Update Data from one table with another?

[zipaway]

Hello I have two tables, when a module is run I would like the Ship_Dates Table to be updated with the 'New' dates in the Change_Orders table. I have tried to come up with a solution but can't think of anything. Any help would greatly be appreciated.

Example

Ship_Dates Table:

Project_Number	ShipmentNumber	Sched_Ship_Date	Actual_Ship_Date	Notes
1020	1	28/07/2014		Test
1020	2	29/07/2014		Test

Change_Order_Dates Table:

Change_Number	Project_Number	ShipmentNumber	Current_Ship_Date	New_Ship_Date
10	1020	1	28/07/2014	3/08/2014
10	1020	2	29/07/2014	4/08/2014

Ship_Dates Table after module is run:

Project_Number	ShipmentNumber	Sched_Ship_Date	Actual_Ship_Date	Notes
1020	1	3/08/2014		Test
1020	2	4/08/2014		Test

[burrina]

One way would be to simply run an update query using the afterupdateevent of your ship delivery table.

[zipaway]

One way would be to simply run an update query using the afterupdateevent of your ship delivery table.

I tried making an update query but it didn't make much sense to me, what would I put for the UpdateTo field?

[June7]

Why do you need the Ship_Delivery_Dates table? Why not just query the Change_Order_Dates table? Or why the Change_Order_Dates table instead of data entry/edit directly in Ship_Delivery_Dates?

[burrina]

It would be an append query using a Destination table. Here is a screenshot example.
Oops, it should be an update qry! But you get the idea.


HTH

[June7]

Burrina, I think OP wants to edit existing record. That is an UPDATE sql action.

Start with a query that joins the tables on the Project_Number then click UPDATE on the ribbon. The Update To would be reference to the field from Change_Order_Dates.

[zipaway]

Why do you need the Ship_Delivery_Dates table? Why not just query the Change_Order_Dates table? Or why the Change_Order_Dates table instead of data entry/edit directly in Ship_Delivery_Dates?

I would like to keep a history of the changes made. Good idea about re-querying the Change_Order_Dates table but it wouldn't work very well in my database.

[zipaway]

This doesn't make too much sense to me, I have this so far. How would I create criteria so that it only updates when the ProjectNumber and the ShipmentNumber are the same?

[June7]

Do a compound join. Link the tables in the query on the two fields in both tables.

[burrina]

Here is an example. Of course make the changes to reflect your table names and field names. IF what you want to do is keep a history!

Dim strSQL As String 'Log Changed ShipDate to tblOrderDates.

strSQL = "INSERT INTO tblOrderDates (OrderDate,ProjectNo ) "
strSQL = strSQL & "VALUES (#" & Me.ShipDate & "#, '" & Me.ProjectNo & "')"
CurrentDb.Execute strSQL, dbFailOnError

HTH

[zipaway]

Here is an example. Of course make the changes to reflect your table names and field names. IF what you want to do is keep a history!

HTH

I don't think that would work in my case. I am trying to edit data that is already existing. An update would probably work but I don't know how to set up the joins.

[zipaway]

Do a compound join. Link the tables in the query on the two fields in both tables.

I'm sorry I tried looking up how to create a compound join but it still doesn't make too much sense to me.

[burrina]

Take a look at this video tutorial. http://office.microsoft.com/en-us/ac...104092866.aspx

[June7]

Link the two tables in query on the two common fields.
Click one field and hold then drag to the corresponding field in other table. Then click the link line and set the join type.
Repeat for the other pair.

[zipaway]

Take a look at this video tutorial. http://office.microsoft.com/en-us/ac...104092866.aspx

This didn't help very much, I don't need to change data in multiple tables. I need to change data in one table using data from another table.