Query to consolidate data from 2 table? (1 Viewer)

[Babycat]

Hi everyone.

I would like to consolidate date from FieldData1 and FieldData2 from 2 separated tables into Table Main.

What is the most efficent solution (short execution time) to do that?

I do write VBA code to copy one by one consecutively with recordset method, but I think query might be better...
What could the query look like?

Anyone can help me please?

 

[isladogs]

Create an append query with both tables using a left join on fields ID to get all values from Sub1.
Add the other 2 fields and run it. Job done.

 

[arnelgp]

ID on TBL_MAIN must not be Autonumber:

Insert Into tbl_main (id, fieldData1, fieldData2)
Select A.id, A.fieldData1, B.fieldData2
From tbl_sub1 As A Left Join
tbl_sub2 As B On A.ID = B.ID

 

[isladogs]

Alternatively, you can use an autonumber field for tblMain providing you don't import the ID field from tblSub1

INSERT INTO tbl_main (fieldData1, fieldData2)
SELECT A.fieldData1, B.fieldData2
FROM tbl_sub1 As A Left Join tbl_sub2 As B On A.ID = B.ID

 

[Pat Hartman]

For everyone's edification,

You CAN append an autonumber value in queries. It is the ONLY place that allows you to actually "update" the autonumber column. However, the autonumber must not already exist.

How do you think Access could do compact & repair if this was not possible?

I don't know if you want to use the old autonumber. I'm just saying that you could. Whenever you start with two tables like this, you run the risk of rows being in tbl1 but not in tbl2 or vice versa. Do some analysis to determine whether you have a complete set of matching rows. If you do, then an inner join works fine. If there are rows in a but not in b or rows in b but not in a, handle them with separate append queries. Left join and right join. and just append the data you have.

 

[Babycat]

For everyone's edification,

You CAN append an autonumber value in queries. It is the ONLY place that allows you to actually "update" the autonumber column. However, the autonumber must not already exist.

How do you think Access could do compact & repair if this was not possible?

I don't know if you want to use the old autonumber. I'm just saying that you could. Whenever you start with two tables like this, you run the risk of rows being in tbl1 but not in tbl2 or vice versa. Do some analysis to determine whether you have a complete set of matching rows. If you do, then an inner join works fine. If there are rows in a but not in b or rows in b but not in a, handle them with separate append queries. Left join and right join. and just append the data you have.

Thank Hartman

In general it shoud be left join and right join as you suggested. Luckily, it is most likely set of ID (A) = set of ID (B) and they are both subset of table_main.

 

[Babycat]

ID on TBL_MAIN must not be Autonumber:

Insert Into tbl_main (id, fieldData1, fieldData2)
Select A.id, A.fieldData1, B.fieldData2
From tbl_sub1 As A Left Join
tbl_sub2 As B On A.ID = B.ID

Thank Agnelgp, my main_table ID is an autonumber , so I will not include it in the query