如何将新字段插入与另一个表相关的表中? [英] How to insert a new field into a table which related to another table?

问题描述

我在 MS Access 2013 中有两个表.

有一些条件:
1. 零件只能在维修后取出.
2.一段时间后，部分将再次返回服务.
3. 部分可回收.

tblService:

(ID为主键)<前>ID PART_ID SERV_DATE1 A0001 11/1/20132 A0001 11/13/20133 A0001 11/25/20134 B0001 11/26/20135 C0001 12/1/20136 C0001 12/10/20137 C0001 12/20/20138 A0001 12/21/2013

tblWithdraw:

(ID为主键)<前>ID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO1 A0001 11/6/2013 下午 6012 A0001 11/20/2013 120 下午 6033 A0001 11/30/2013 更换 6054 C0001 12/2/2013 下午 30 点 7015 C0001 12/15/2013 180 下午 7026 B0001 12/18/2013 下午 8017 A0001 12/25/2013 60 下午 502
我想在 tblWithdraw 中插入一个新字段 SERVICE_ID，如下所示:<前>ID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO SERVICE_ID1 A0001 11/6/2013 下午 601 12 A0001 11/20/2013 120 下午 603 23 A0001 11/30/2013 更换 605 34 C0001 12/2/2013 下午 30 点 701 55 C0001 12/15/2013 180 下午 702 66 B0001 12/18/2013 下午 801 47 A0001 12/25/2013 60 下午 502 8

SERVICE_ID 是 tblService 中的 ID.

但是，当我在查询中尝试以下代码时:

INSERT INTO tblWithdraw (SERVICE_ID) SELECT ID

来自 tblService;

它给了我:<前>ID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO SERVICE_ID1 A0001 11/6/2013 下午 6012 A0001 11/20/2013 120 下午 6033 A0001 11/30/2013 更换 6054 C0001 12/2/2013 下午 30 点 7015 C0001 12/15/2013 180 下午 7026 B0001 12/18/2013 下午 8017 A0001 12/25/2013 60 下午 5028 19 210 311 412 513 614 715 8或以下代码:

INSERT INTO tblWithdraw (SERVICE_ID) SELECT ID FROM tblService WHERE tblService.PART_ID =tblWithdraw.PART_ID;

系统无法识别tblWithdraw.PART_ID.

谁能更正我的代码或给我答案或指导我解决问题?谢谢！

解决方案

正如您所发现的，INSERT 语句总是向表中添加新行.您想使用以下内容更新现有行:

更新 tblWithdrawSET SERVICE_ID = DLookup("ID","tblService","PART_ID='" & PART_ID & "' AND SERV_DATE=#" & Format(DMax("SERV_DATE","tblService","PART_ID='" &; PART_ID & "' AND SERV_DATE<=#" & Format(DRAWN_DATE,"yyyy-mm-dd") & "#"),"yyyy-mm-dd") & "#")

它使用 DMax() 来查找给定 DRAWN_DATE 之前的最新 SERV_DATE，然后执行 DLookup() 来查找相应的 [tblService].[ID]，然后将其插入到 [tblWithdraw] 中的 SERVICE_ID 列中.

I have two tables in MS Access 2013.

There are some conditions:
1. Part can only be withdrawn after serviced.
2. After certain time, part will be returned for service again.
3. Part can be recycled.

tblService:

(ID is the primary key)

ID  PART_ID SERV_DATE
1    A0001  11/1/2013
2    A0001  11/13/2013
3    A0001  11/25/2013
4    B0001  11/26/2013
5    C0001  12/1/2013
6    C0001  12/10/2013
7    C0001  12/20/2013
8    A0001  12/21/2013

tblWithdraw:

(ID is the primary key)

ID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO
1   A0001  11/6/2013       PM         601
2   A0001  11/20/2013    120 PM       603
3   A0001  11/30/2013  REPLACEMENT    605
4   C0001  12/2/2013      30 PM       701
5   C0001  12/15/2013    180 PM       702
6   B0001  12/18/2013      PM         801
7   A0001  12/25/2013     60 PM       502

I'd like to insert a new field, SERVICE_ID, into tblWithdraw, look like this:

ID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO SERVICE_ID
1   A0001  11/6/2013       PM         601       1
2   A0001  11/20/2013    120 PM       603       2
3   A0001  11/30/2013  REPLACEMENT    605       3
4   C0001  12/2/2013      30 PM       701       5
5   C0001  12/15/2013    180 PM       702       6
6   B0001  12/18/2013      PM         801       4
7   A0001  12/25/2013     60 PM       502       8

which SERVICE_ID is the ID in tblService.

However, when I tried codes below in the query:

INSERT INTO tblWithdraw ( SERVICE_ID ) SELECT ID  

FROM tblService;

It gave me:

ID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO SERVICE_ID
1   A0001  11/6/2013       PM         601
2   A0001  11/20/2013    120 PM       603
3   A0001  11/30/2013  REPLACEMENT    605
4   C0001  12/2/2013      30 PM       701
5   C0001  12/15/2013    180 PM       702
6   B0001  12/18/2013      PM         801
7   A0001  12/25/2013     60 PM       502
8                                               1
9                                               2
10                                              3
11                                              4
12                                              5
13                                              6
14                                              7
15                                              8

Or codes below:

INSERT INTO tblWithdraw (SERVICE_ID) SELECT ID FROM tblService WHERE tblService.PART_ID =tblWithdraw.PART_ID;  

The system can't recognize tblWithdraw.PART_ID.

Can anyone correct my codes or give me answer or direct me to the solution? Thanks!

解决方案

As you have discovered, INSERT statements always add new rows to a table. You want to UPDATE existing rows using something like this:

UPDATE tblWithdraw
SET SERVICE_ID = DLookup("ID","tblService","PART_ID='" & PART_ID & "' AND SERV_DATE=#" & Format(DMax("SERV_DATE","tblService","PART_ID='" & PART_ID & "' AND SERV_DATE<=#" & Format(DRAWN_DATE,"yyyy-mm-dd") & "#"),"yyyy-mm-dd") & "#")

It uses DMax() to find the most recent SERV_DATE that precedes a given DRAWN_DATE, then does a DLookup() to find the corresponding [tblService].[ID], and then plugs it into the SERVICE_ID column in [tblWithdraw].

这篇关于如何将新字段插入与另一个表相关的表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！