Hi Guys,

Firstly, I used to do a bit of access and SQL years ago at uni, but its been too long now.

I am making a new database to record jobs

I have a table called ITEMS that lists appliances that can be tested. Fields include;
ID - AutoNumber - Primary Key
Last_Tested_Date - Date/Time

I have another table called TESTS. Fields included
ID - Autonumber - Primary Key
Item_ID - Linked to ITEM table
Date_Tested - Date/Time

Appliances are continually added to the ITEMS table as new clients come on board.

When an appliance is tested, a record is added to the TESTS table. Thats when the Date_Tested field in filled in (along with others).

I need the Last_Tested_Date in the ITEMS table to always reflect the most recent date that the appliance was tested.

I can run a query using MAX to show the item_ID and its last tested date.

SELECT TESTS.Item_ID, Max(TESTS.date_tested) AS Last_Test
FROM TESTS
GROUP BY TESTS.Item_ID;

I just don't know how to get that data to update to the ITEMS table. I have tried to use an update query, but I can't figure out how.

Thanks

Glenn

Anything you can calculate easily (which you are doing already) should not be stored anywhere else.

I.e. you are trying to do something you should not be doing!

Edit:
P.s. Welcome to the forum!

Anything you can calculate easily (which you are doing already) should not be stored anywhere else.

I.e. you are trying to do something you should not be doing!

Edit:
P.s. Welcome to the forum!

Ok, that rings a bell (and makes sense).

So if I want to be able to view that date with the other information, should I be trying to do it in a report, rather than just being able to view it in the table?

Yes, you're right. Tables are for storing information, not viewing it. Typically, you create a report based on a query which draws the data from your table(s).