Update query syntax

[Jack Sheet]

Hi all, Access 97

Tables "T_Clients" and "T_Tasks" share a common field ClientRef with a 1
(T_Clients) to many (T_Tasks) relationship.
T_Clients has a field MyDate
T_Tasks has a field YourDate

I want to fill the MyDate field with the maximum value of YourDate where
records match ClientRef.

For instance, in T_Tasks you might find
Record ClientRef YourDate
1 AA100 01/01/2001
2 BB200 02/02/2002
3 AA100 03/03/2003
4 BB200 04/04/2004

Now, if those were the only records in T_Tasks, then on running the update
query the result in T_Clients should be
Record ClientRef MyDate
1 AA100 03/03/2003
2 BB200 04/04/2004

You may assume that there are no null ie blank entries.
I don't mind using intermediate queries if necessary.
Any help with the syntax of the query would be appreciated.
Thanks

 

[tina]

the only suggestion i have is to use a DMax() function in the Update query,
to look up the maximum (most recent) date in table T_Tasks for each
ClientRef in tableT_Clients. see the DMax() function topic in Access Help
for details on how it works.

btw, general normalization rule is that you don't hard code calculated
values in a table, and there is rarely a good business or technical reason
to break that rule. suggest that instead of hard-coding the data - which
presumably changes over time - instead you use a DMax() function or a Totals
query to retrieve the most recent task date for each client at runtime,
wherever needed in a query, form, or report.

hth

 

[Jack Sheet]

Hi tina

Thanks for the tip. Not tried it yet but will look into it.

You are of course right about hard-coding data. I only intend to run the
update query once. Background is that I have imported a fairly large
database from Excel to Access, redesigning the structure of the database as
I go but importing the fundamental data unchanged. I only need to run this
update query once, to "initialise" the date in T_Clients, and thereafter
will delete the query which will not be required again. But initialising
the field manually would be a huge task.