Compare dates in two tables to find most recent

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...

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]
 
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
 
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?
Click to expand...

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]
 
Pocket said:
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.
Click to expand...

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
 
Thanks John
I'll give it a go and see how I get on.

John Vinson said:
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?
Click to expand...

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]
Click to expand...
 
Back
Top