I've looked through many of the Max date threads but don't really know SQL too well. I did something that I think works but I'm not sure this is the right way to do this. I have an address table with the fields ID#, Address, and Effective Date. The addresses are updated every month which is the Effective date. I want to see the only the most current address without any duplicates.
A straight query looks like this:
ID# Address Effective Date
1 address 1 5/1/04
1 address 2 6/1/04
1 address 2 7/1/04
A totals query with date set to Max looks like this. It's grouped on SSN and Address so I'm getting multiple addresses:
ID# Address Effective Date
Group Group Max
1 address 1 5/1/04
1 address 2 7/1/04
A query with Address set to Last and Date to Max looks right, the most current entry, but I'm not sure it is:
ID# Address Effective Date
Group Last Max
1 Address 2 7/1/04
This is the SQL:
SELECT dbo_tbl_Address.nbrID, Last(dbo_tbl_Address.txtMailAddr1) AS LastOftxtMailAddr1, Max(dbo_tbl_Address.dtmEffectiveDate) AS MaxOfdtmEffectiveDate
FROM dbo_tbl_Address
GROUP BY dbo_tbl_Address.nbrID;
Thanks in advance.
A straight query looks like this:
ID# Address Effective Date
1 address 1 5/1/04
1 address 2 6/1/04
1 address 2 7/1/04
A totals query with date set to Max looks like this. It's grouped on SSN and Address so I'm getting multiple addresses:
ID# Address Effective Date
Group Group Max
1 address 1 5/1/04
1 address 2 7/1/04
A query with Address set to Last and Date to Max looks right, the most current entry, but I'm not sure it is:
ID# Address Effective Date
Group Last Max
1 Address 2 7/1/04
This is the SQL:
SELECT dbo_tbl_Address.nbrID, Last(dbo_tbl_Address.txtMailAddr1) AS LastOftxtMailAddr1, Max(dbo_tbl_Address.dtmEffectiveDate) AS MaxOfdtmEffectiveDate
FROM dbo_tbl_Address
GROUP BY dbo_tbl_Address.nbrID;
Thanks in advance.