Update Fields From Latest Entry of Another Table

[PMal]

I have 2 tables, the first is a list of items (table1), the second is a list of maintenance for said items (table2). so an item has a maintenance task either every month or year. The tables are linked by the serial number of the items.

So table1 will have item 1, item1 has 4 entries in table2, (2018, 2019, 2020 & 2021).

I have a field in table1 which is basically 'date last maintained'.

I want table1 to automatically update with the latest maintenance date for all items when an entry is made to table2. I know of update queries and Dlookups (not sure either are relevant and would help me) if someone could point me in the right direction for completing such a task?



Thank s very much.

[Welshgasman]

You do not do it.
You just look up the max date from table 2 for relevant item any time you want to know what it is. That way it always remains current. If you store a calculated/lookup value, it can easily get out of sync/date.

[PMal]

You do not do it.
You just look up the max date from table 2 for relevant item any time you want to know what it is. That way it always remains current. If you store a calculated/lookup value, it can easily get out of sync/date.

Thanks for your response.

If that's the case, I have a report to show 'Expired items', when the last maintenance point + the set interval has passed. (Last event: 01/01/22+ 6 month interval = Due date of 01/07/22). Any pointers on how I could work my report if I don't have an expired date?

An option is to enter both fields manually but I want to avoid duplicate inputs if I can as we know this leaves massive room errors/forgetfulness.

[Welshgasman]

You bring it into the query that supplies the data for the report.

[Micron]

What is 'expired date'? If it's the same as the due date and you're calculating that, then you also have the expired date? Then put an unbound textbox in the report with that calculation as the control source.