Update query help please

[Jack Sheet]

Hi all

Re: Access 97

I have 2 tables: T_Clients and T_Tasks.
There is a one (T_Clients) to many (T_Tasks) relationship with enforced
referential integrity, based on the key field ID_Client.
T_Tasks has a field "Date"
T_Clients has a field "LatestDate"

I wish to set up an Update Query that populates LatestDate field in
T_Clients with the latest date that occurs (for each specified ID_Client) in
the Date field of T_Tasks table. In the event that no date is found in the
Date field for a particular ID_Client then no action should be taken to
update the LatestDate field for that ID_Client.

I am not getting anywhere and should be grateful for any help. It sounds
like a simple problem.

 

[Nikos Yannacopoulos]

You shouldn't update, and you shouldn't have this field in your clients
table at all. The general rule is if you can calculate it then don't
store it. Just make a totals query on the two tables and use total
function Group By on ID_Client and Max on the date field to retrieve the
latest date.

HTH,
Nikos

 

[Ofer Cohen]

It's not recomanded storing a calculated field from one table in another one,
this values will always change, and you'll have to run tis update query very
time, and most chances that the data wil not be updated sometimes.
It's better to use a query to do that for you, so every time you run this
query the data will be recalculated

Select T_Clients.* , DMax("[DateFieldName]","[T_Tasks]","[ID_Client] = " &
[ID_Client]) As MaxOfDate From T_Clients