update table with query expression results

[AccessDevJunior]

I have 8 tables:
Tbl_Main
Tbl_a
Tbl_b
Tbl_c
And so on,
Each table contains a field named ‘Reference Number’ and it is this field that relates all the tables together, in the ‘tbl_Main’ the ‘Reference Number’ is the Primary Key.

For each of the sub tables (tbl_a, tbl_b etc) I have created a query and each query contains an expression.

I would like to get the result of the expressions in the sub queries into ‘tbl_Main’.

I have attempted to do this by using an append query but an error occurs when I run the query. I then changed the ‘Reference Number’ in the ‘tbl_main’ so it was no longer a primary key then run the same append query, it worked but it created duplicate values.

Can anyone please help for a solution for this

 

[eAlchemist]

You should be using an update query. First, open tbl_main in the design view and add the columns that you want to put data into. Next, create an update query linking each of the tables to tbl_main via the "reference number" field. Lastly, put each of the columns in tbl_main to be updated into the query. In the "update to" row in the query type:
[tbl_a]![field name]

Run the query.

Hope this helps

 

[eAlchemist]

Rather than linking the tables, link the queries. Make sure that each query contains the reference number.