Compare dates in two tables to find most recent

[Guest]

Hi
I have two tables which both have 'Date' fields.
What I want to do is, in a form, compare the two date fields in the two
tables and display on the form whichever of the two dates is the most recent
(and if there is only 1 date populated, just display this).
Please help!! Thanks

 

[John Vinson]

Hi
I have two tables which both have 'Date' fields.
What I want to do is, in a form, compare the two date fields in the two
tables and display on the form whichever of the two dates is the most recent
(and if there is only 1 date populated, just display this).
Please help!! Thanks

How are the tables related? If you have 39994 records in TableA, and
41238 records in TableB, each record with a date field, which dates do
you want to compare?

John W. Vinson[MVP]

 

[Guest]

Hi John
Thanks for your response.
The tables are both linked to a third table called Contacts which has a
ContactID field. The ContactID field is what the two separate tables are
linked to, so the comparison would be comparing the two date fields for the
specific client and selecting the most recent one.
Thinking about it, it is also possible that there would be more than one
record in one or other of the first two tables for that specific client, but
I just want the overall most recent date for that client.
Hope that helps?
Thanks again

 

[John Vinson]

Hi John
Thanks for your response.
The tables are both linked to a third table called Contacts which has a
ContactID field. The ContactID field is what the two separate tables are
linked to, so the comparison would be comparing the two date fields for the
specific client and selecting the most recent one.
Thinking about it, it is also possible that there would be more than one
record in one or other of the first two tables for that specific client, but
I just want the overall most recent date for that client.
Hope that helps?

Sounds like the table design might need a look, if you have "the same"
date data in two tables... but try a Query joining Contacts to the two
tables. I'll call them TableA and TableB:

SELECT Contacts.<whatever you want to see>,
IIf(NZ(Max([TableA].[Datefield], #1/1/100#)) >
NZ(Max([TableB].[Datefield], #1/1/100#)), Max([TableA].[Datefield]),
Max([TableB].[Datefield]) AS MostRecent;


John W. Vinson[MVP]

 

[peregenem]

Thinking about it, it is also possible that there would be more than one
record in one or other of the first two tables for that specific client, but
I just want the overall most recent date for that client.

SELECT MAX(DT1.date_col)
FROM (
SELECT date_col AS date_col FROM Table1
UNION ALL
SELECT date_col AS date_col FROM Table2
) AS DT1

 

[Guest]

Thanks John
I'll give it a go and see how I get on.

Hi John
Thanks for your response.
The tables are both linked to a third table called Contacts which has a
ContactID field. The ContactID field is what the two separate tables are
linked to, so the comparison would be comparing the two date fields for the
specific client and selecting the most recent one.
Thinking about it, it is also possible that there would be more than one
record in one or other of the first two tables for that specific client, but
I just want the overall most recent date for that client.
Hope that helps?

Sounds like the table design might need a look, if you have "the same"
date data in two tables... but try a Query joining Contacts to the two
tables. I'll call them TableA and TableB:

SELECT Contacts.<whatever you want to see>,
IIf(NZ(Max([TableA].[Datefield], #1/1/100#)) >
NZ(Max([TableB].[Datefield], #1/1/100#)), Max([TableA].[Datefield]),
Max([TableB].[Datefield]) AS MostRecent;


John W. Vinson[MVP]