Query To Get ID Of Oldest (By Date) Record, MS Access
I have a table (tblFees) full of fees. the fields I am interested in are:
ID - which is an autonumber
Account - could be many entries in this file with the same account
FeeDate - could be many entries associated with an account
I would like a query that returns one record for each Account.
The ID of the oldest record (based on feedate) in the account
and the Account Number.
I can't figure out a way to get it that way.
ID - which is an autonumber
Account - could be many entries in this file with the same account
FeeDate - could be many entries associated with an account
I would like a query that returns one record for each Account.
The ID of the oldest record (based on feedate) in the account
and the Account Number.
I can't figure out a way to get it that way.
Now, if you want all of the data associated with that earliest date, you could do:
Select * FROM yourTable
INNER JOIN (
SELECT Account, Min(FeeDate) as OldestRec
FROM yourTable
GROUP BY Account
) as AcctOld
ON yourTable.Account = AcctOld.Account
AND yourTable.FeeDate = AcctOld.OldestRec
Select * FROM yourTable
INNER JOIN (
SELECT Account, Min(FeeDate) as OldestRec
FROM yourTable
GROUP BY Account
) as AcctOld
ON yourTable.Account = AcctOld.Account
AND yourTable.FeeDate = AcctOld.OldestRec
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For my simple mind I used the query designer and broke it into two queries. The first I called 'qryTaxRecs_Fees_getOldest
SELECT tblTaxRecs_Fees.BRT, Min(tblTaxRecs_Fees.FeeDat
FROM tblTaxRecs_Fees
GROUP BY tblTaxRecs_Fees.BRT
HAVING (((tblTaxRecs_Fees.BRT)>0)
The second I called 'qryTaxRecs_Fees_GetOldest
SELECT tblTaxRecs_Fees.ID, tblTaxRecs_Fees.TaxHdrID, tblTaxRecs_Fees.BRT, tblTaxRecs_Fees.FeePeriod,
FROM tblTaxRecs_Fees INNER JOIN qryTaxRecs_Fees_getOldestF
WHERE (((tblTaxRecs_Fees.BRT)>0)
ORDER BY tblTaxRecs_Fees.ID;
so far so good but as PatHartman suggested there may be multiple fees with the same date on an account/BRT. So at this point I had almost what I wanted, excpept multiple lines for any that had the same date on multiple oldest fees.
I tried the Top 1 as suggested but ended up with only a single line in my result, not a single line for each BRT. I tried revising query 2 usning the Top 1, here is what I tried:
SELECT TOP 1 tblTaxRecs_Fees.ID, tblTaxRecs_Fees.TaxHdrID, tblTaxRecs_Fees.BRT, tblTaxRecs_Fees.FeePeriod,
FROM tblTaxRecs_Fees INNER JOIN qryTaxRecs_Fees_getOldestF
WHERE (((tblTaxRecs_Fees.BRT)>0)
ORDER BY tblTaxRecs_Fees.ID;
Where did I go wrong
SELECT tblTaxRecs_Fees.BRT, Min(tblTaxRecs_Fees.FeeDat
FROM tblTaxRecs_Fees
GROUP BY tblTaxRecs_Fees.BRT
HAVING (((tblTaxRecs_Fees.BRT)>0)
The second I called 'qryTaxRecs_Fees_GetOldest
SELECT tblTaxRecs_Fees.ID, tblTaxRecs_Fees.TaxHdrID, tblTaxRecs_Fees.BRT, tblTaxRecs_Fees.FeePeriod,
FROM tblTaxRecs_Fees INNER JOIN qryTaxRecs_Fees_getOldestF
WHERE (((tblTaxRecs_Fees.BRT)>0)
ORDER BY tblTaxRecs_Fees.ID;
so far so good but as PatHartman suggested there may be multiple fees with the same date on an account/BRT. So at this point I had almost what I wanted, excpept multiple lines for any that had the same date on multiple oldest fees.
I tried the Top 1 as suggested but ended up with only a single line in my result, not a single line for each BRT. I tried revising query 2 usning the Top 1, here is what I tried:
SELECT TOP 1 tblTaxRecs_Fees.ID, tblTaxRecs_Fees.TaxHdrID, tblTaxRecs_Fees.BRT, tblTaxRecs_Fees.FeePeriod,
FROM tblTaxRecs_Fees INNER JOIN qryTaxRecs_Fees_getOldestF
WHERE (((tblTaxRecs_Fees.BRT)>0)
ORDER BY tblTaxRecs_Fees.ID;
Where did I go wrong
Can you post some sample data with expected result?
ASKER
Sorry, haven't had time to pull that together yet.
FROM yourTable
GROUP BY Account