Automatically update a Table based on values in another Table

[stacynh]

Hello Everyone,




I am hoping for some help automating a task in Access. I have scoured forums and YouTube, but so far, no luck...


In the same database, I have a "Item Distribution" table and a "Item Serial Number" table.


The Item Distribution table includes (among other things):
-A Serial Number field: Pulls values from the other table;
-A Item Returned Date field;
-A Date Updated field


The Item Serial Number table includes (among other things):
-A Serial Number field;
-A Current Location field;
-A Date Updated field.


When a new record is added to the Item Distribution Table, I would like the Item Serial Number Current Location field to change to "Assigned." When a returned date is added to an existing record on the Item Distribution table, I would like the Item Serial Number Current Location to change to "Stock." In both cases, the Item Serial Number Date Updated field should also update.


Is there any way to automate this?


LIMITATIONS:
1. The same Item Serial Number may be listed multiple times on the Item Distribution table, should the same item have been signed out and returned multiple times. I am interested in the most recent change to a record with that serial number.


2. There are additional location possibilities on the Item Serial Number table that would never appear on the Item Distribution table, (such as "Repair Shop" or "Destroyed").


Thank you very much for your time!

[ranman256]

To get the most recent one make a select query on the table : select [serialNum],MAX(UpdDate)
now make an update query , this will set the new date based on the select query above, but you cannot join the 2 queries. (you cant sum/Max and update in the same query)
You must update using the IN clause.

as an example, use the query wizard to create a FIND DUPLACATES query. Then look at the resulting sql. Do this to update your table.
If you cannot, write the Select query to a table, THEN update your data from this 'temp' table.

[RuralGuy]

It sure looks like there is a Normalization issue as the actual "Serial Number" should only be in one table and every other table just has the PK field of that record. Of course, I could be wrong here.

[stacynh]

Thank you! I think this may be a little beyond my current abilities, but I will work on it! Thanks for the help!

To get the most recent one make a select query on the table : select [serialNum],MAX(UpdDate)
now make an update query , this will set the new date based on the select query above, but you cannot join the 2 queries. (you cant sum/Max and update in the same query)
You must update using the IN clause.

as an example, use the query wizard to create a FIND DUPLACATES query. Then look at the resulting sql. Do this to update your table.
If you cannot, write the Select query to a table, THEN update your data from this 'temp' table.