UPDATE QUERY WHERE tbl1.FIELD1 = tbl2.FIELD1

[AMJADJ]

Hi

I am not sure if I need a separate query but will explain my problem. I have a linked table which contains all Sales Orders, the fields I am interested in are order_no, del_address, address3 and address5.

I have a form where the transport team enter the info regarding the loads they put on wagons i.e. order_no, no_of_plts etc. This forms can have multiple lines and populates a table called tbl_loads. tbl_loads also contains fields called del_address,address3 and address5.

What I would like to happen is either when the form has been fully filled in, del_address,address3 and address5 in the tbl_loads are populated based on the individual order numbers on the form i.e. if the form has three lines order x y z, I would want it to go to the table containing all sales orders, look up orders x y and z and populate del_address,address3 and address5 based on their value in the sales orders table.

Alternatively, I have created a update query, and said update tbl_loads.customer with customer from sales orders table, but it prompts me to enter a customer value.
In short, equivalent sql but in access for Update table1 set customer = A.customer from (select order_no,customer from tbl2)A where table1.order_no = A.order_no




Hope this makes sense, thank you for your time.

[June7]

Why duplicate the address data in multiple tables? Save the address ID in tbl_loads, build query that joins tables, use that query as report RecordSource.

[AMJADJ]

Hi, Thanks for your time.

The transport team enter the info regarding wagon loads, once the wagon has reached the offsite warehouse, they then enter the trailer number and date, which then opens a form displaying everything loaded on that wagon. The goods are then booked in. When they load the form showing wagon load details, I want the transport info to contain address details.

At the beginning, the sales order table is populated with addresses and order number, but we don't have anything to join as transport wont know what they are going to load. We do not want the offsite warehouse guys to have full access to the sales order table, hence why we want to populate tbl_loads with the address info.

Hope this makes sense.

[June7]

Is Transport accessing the databases in any way? Unless you go to extreme measures to secure the database, they already have 'full access'.

Users should not work directly with tables and queries, only forms and reports. Design reports to control data users can see and/or modify.

The UPDATE example you posted is not valid syntax. If you want to do "lookup", will look like:

UPDATE tbl_Loads SET del_address=DLookup("address", "tbl_Orders", "customer=" & [customer]), address3=DLookup("address3", "tbl_Orders", "customer=" & [customer]), address5=DLookup("address5", "tbl_Orders", "customer=" & [customer])

Alternatively, VBA code opens a recordset of the necessary customer record to pull the address info and run UPDATE action referencing recordset fields instead DLookup expressions.