Selecting the Most Recent Date From one unique ID

[Nabiyah E via AccessMonster.com]

I'm pretty new to access. I'm using the 97 version and I'm very frustrated as
I cannot figure this out.

I have one field [ProviderID], which houses many different Provider IDs. This
field can also contain the same ID several times, because we have to keep a
record for every date that is applicable to that ProviderID. There is an
autonumber on the table that houses this information, which means that every
one of those records has a different autonumber. I need to be able to run a
select query that pulls the most recent entry (date) for every ProviderID in
the table and it's relative autonumber.

Originally, I ran Grpby on the ProviderID field (ascending sort) and entered
Last (instead of a Groupby on the Term_Date fields (descending sort) but it
doesn't pull the correct information. When I bring in the AutoNumber field in
it brings in all records in the table because of the GroupBy.

Here is the scenario:

[AutoNumber] [ProviderID] [Term_Date]
1 1515536 10/29/2003
2 1515536 10/21/2001
3 1515536 09/11/1999
4 1515536 07/26/2004
5 1515536 04/21/1997
6 1515536 07/21/2001


Please help. Thanks
Ny

 

[Michel Walsh]

Hi,


Take a look at http://www.mvps.org/access/queries/qry0020.htm which present
four methods to achieve that result.


Hoping it may help,
Vanderghast, Access MVP

 

[Van T. Dinh]

Try a Query with an SQL String like:

****Untested****
SELECT Main.*
FROM [YourTable] As Main
WHERE Main.[Term_Date] =
(
SELECT Max(Sub.[TERM_Date])
FROM [YourTable] As Sub
WHERE Sub.ProviderID = Main.ProviderID
)
****End****

 

[John Spencer]

Single query solution.

SELECT *
FROM YourTable
WHERE YourTable.[Term_Date] =
(SELECT Max([Term_Date]
FROM YourTable as Temp
WHERE Temp.[Provider_ID] = YourTable.[Provider_ID])

Two query solution:
Run a Group by query on just the provider ID and Term Date
Group on Provider Id and get the MAX (not the last) of the Term Date
Save that query.

Open a new query window and add your table and the above query
Join the two on the ProviderID and the TermDate fields
Run this new query.

Why use Max not Last? Max gets the largest value (latest date); Last gets
the last record in retrieval order which as far as the human observer is
concerned is more or less random.

 

[Chris M]

I'm pretty new to access. I'm using the 97 version and I'm very frustrated
as
I cannot figure this out.

I have one field [ProviderID], which houses many different Provider IDs.
This
field can also contain the same ID several times, because we have to keep
a
record for every date that is applicable to that ProviderID. There is an
autonumber on the table that houses this information, which means that
every
one of those records has a different autonumber. I need to be able to run
a
select query that pulls the most recent entry (date) for every ProviderID
in
the table and it's relative autonumber.

Originally, I ran Grpby on the ProviderID field (ascending sort) and
entered
Last (instead of a Groupby on the Term_Date fields (descending sort) but
it
doesn't pull the correct information. When I bring in the AutoNumber field
in
it brings in all records in the table because of the GroupBy.

Here is the scenario:

[AutoNumber] [ProviderID] [Term_Date]
1 1515536 10/29/2003
2 1515536 10/21/2001
3 1515536 09/11/1999
4 1515536 07/26/2004
5 1515536 04/21/1997
6 1515536 07/21/2001

SELECT AutoNumber,ProviderId,Date
FROM MyTable AS MainTable
WHERE NOT EXISTS
(
SELECT 1 FROM MyTable AS SubQuery
WHERE SubQuery.ProviderId = MainTable.ProviderId
AND SubQuery.Date > MainTable.Date
)