Update Data from one table with another?

[June7]

Did you follow my suggestion in posts 9 and 14?

[zipaway]

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.

I think I am getting somewhere. I have this now.




UPDATE ShipDeliveryDates INNER JOIN Change_Orders_Dates ON (ShipDeliveryDates.ComponentNumber = Change_Orders_Dates.ShipmentNumber) AND (ShipDeliveryDates.Project_Number = Change_Orders_Dates.Project_Number) SET ShipDeliveryDates.Sched_Ship_Date = [Change_Orders_Dates].[New_Sched_Ship_Date], ShipDeliveryDates.Sched_Ship_Date = [Change_Orders_Dates].[New_Sched_Delivery_Date];

When I run it though I get an error "Duplicate output destination 'ShipDeliveryDates.Sched_Ship_Date'." and it does nothing.

[June7]

Why do you have Sched_Ship_Date field in the grid twice? Should one of those be Sched_Delivery_Date?

[zipaway]

Why do you have Sched_Ship_Date field in the grid twice? Should one of those be Sched_Delivery_Date?

Wow I can't believe I didn't spot that! Thanks so much. It is kinda working now but if I have two change orders for the same project it updates using the first set (Should pick the last entered change order). How would I go about only updating with the maximum change_order number for a project?

[June7]

Now that gets nasty tricky.

This approach abandons the table join.

UPDATE ShipDeliveryDates SET Sched_Ship_Date = DMax("New_Sched_Ship_Date", "[Change_Orders_Dates]", "Project_Number=" & [Project_Number] & " AND "ShipmentNumber=" & [ShipmentNumber]),
Sched_Delivery_Date = DMax("New_Sched_Delivery_Date", "[Change_Orders_Dates]", "Project_Number=" & [Project_Number] & " AND "ShipmentNumber=" & [ShipmentNumber]);

[zipaway]

Now that gets nasty tricky.

This approach abandons the table join.

UPDATE ShipDeliveryDates SET Sched_Ship_Date = DMax("New_Sched_Ship_Date", "[Change_Orders_Dates]", "Project_Number=" & [Project_Number] & " AND "ShipmentNumber=" & [ShipmentNumber]),
Sched_Delivery_Date = DMax("New_Sched_Delivery_Date", "[Change_Orders_Dates]", "Project_Number=" & [Project_Number] & " AND "ShipmentNumber=" & [ShipmentNumber]);

Hmm I get the following error running the above update query.

[June7]

Sorry, extra " mark before ShipmentNumber. Fix both DMax expressions to:

" AND ShipmentNumber=" &

[zipaway]

UPDATE ShipDeliveryDates SET Sched_Ship_Date = DMax("New_Sched_Ship_Date", "[Change_Orders_Dates]", "Project_Number=" & [Project_Number] & " AND ShipmentNumber=" & [ShipmentNumber]),
Sched_Delivery_Date = DMax("New_Sched_Delivery_Date", "[Change_Orders_Dates]", "Project_Number=" & [Project_Number] & " AND ShipmentNumber=" & [ShipmentNumber]);

Hmm it doesn't seem to do anything. For some reason it's saying it will update all records in the database, I click yes and then nothing happens. I am trying to follow this SQL update query and I understand it for the most part but when you are doing DMax what is it finding the maximum of? It should be finding the max change_number.

[June7]

Yes, it will update all records because there is no filter criteria for the UPDATE. Should not be an issue. Don't know why didn't work.

These DMax expressions should find the maximum (most recent) date values for each ShipmentNumber/Project_Number pair as specified in the criteria. Don't you want the most recent dates? Oh wait, the latest change order could be to a date earlier than dates of previous changes. Rats!%*%$^. If that is the case, this gets worse because need to use the maximum change_number as criteria to select record from Change_Orders_Dates.

The following query should return the latest Change_Orders_Dates record for each ShipmentNumber/Project_Number.

SELECT * FROM Change_Orders_Dates WHERE Change_Number IN (SELECT Max(Change_Number) AS CN FROM Change_Orders_Dates GROUP BY ShipmentNumber, Project_Number);

Can try using that query as the source for the date updates instead of the Change_Orders_Dates table. However, if I remember correct, will generate a 'non-updatable dataset' error because of the aggregate query.

So, if that doesn't work, try:
SELECT * FROM Change_Orders_Dates WHERE Change_Number = DMax("Change_Number", "Change_Orders_Dates", "Project_Number=" & [Project_Number] & " AND ShipmentNumber=" & [ShipmentNumber]);

I said this would be nasty tricky. But really, instead of updating table, should just use that query in a report.

[zipaway]

Yes, it will update all records because there is no filter criteria for the UPDATE. Should not be an issue. Don't know why didn't work.

These DMax expressions should find the maximum (most recent) date values for each ShipmentNumber/Project_Number pair as specified in the criteria. Don't you want the most recent dates? Oh wait, the latest change order could be to a date earlier than dates of previous changes. Rats!%*%$^. If that is the case, this gets worse because need to use the maximum change_number as criteria to select record from Change_Orders_Dates.

The following query should return the latest Change_Orders_Dates record for each ShipmentNumber/Project_Number.

SELECT * FROM Change_Orders_Dates WHERE Change_Number IN (SELECT Max(Change_Number) AS CN FROM Change_Orders_Dates GROUP BY ShipmentNumber, Project_Number);

Can try using that query as the source for the date updates instead of the Change_Orders_Dates table. However, if I remember correct, will generate a 'non-updatable dataset' error because of the aggregate query.

So, if that doesn't work, try:
SELECT * FROM Change_Orders_Dates WHERE Change_Number = DMax("Change_Number", "Change_Orders_Dates", "Project_Number=" & [Project_Number] & " AND ShipmentNumber=" & [ShipmentNumber]);

I said this would be nasty tricky. But really, instead of updating table, should just use that query in a report.

That first query worked!!! I did like you said and used the earlier update query with the new selection query and now it's working as it should be without even updating all the rows.

You are an access genius! I sincerely thank you for all your help. I am just putting the final wraps on this access project hopefully I don't have to pester you anymore .