Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 945
  • Last Modified:

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.
0
mlcktmguy
Asked:
mlcktmguy
1 Solution
 
Dale FyeCommented:
SELECT Account, Min(FeeDate) as OldestRec
FROM yourTable
GROUP BY Account
0
 
Dale FyeCommented:
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
0
 
PatHartmanCommented:
If feedate is not unique, you will get multiple records.  I would include the autonumber in the query and sort descending on it and include a Top 1 to limit the number of records to 1.

Select Top 1 yourtable.* 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
order By ID desc;
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mlcktmguyAuthor Commented:
For my simple mind I used the query designer and broke it into two queries.  The first I called 'qryTaxRecs_Fees_getOldestFee_ForBRT_1':

SELECT tblTaxRecs_Fees.BRT, Min(tblTaxRecs_Fees.FeeDate) AS MinOfFeeDate
FROM tblTaxRecs_Fees
GROUP BY tblTaxRecs_Fees.BRT
HAVING (((tblTaxRecs_Fees.BRT)>0));

The second I called 'qryTaxRecs_Fees_GetOldestFeeForBRT_2'

SELECT  tblTaxRecs_Fees.ID, tblTaxRecs_Fees.TaxHdrID, tblTaxRecs_Fees.BRT, tblTaxRecs_Fees.FeePeriod, tblTaxRecs_Fees.FeeID, tblTaxRecs_Fees.OriginalAmt, tblTaxRecs_Fees.CurrBalanceAmt, tblTaxRecs_Fees.FeeDate, tblTaxRecs_Fees.PaymentStatusID, tblTaxRecs_Fees.Active_YN
FROM tblTaxRecs_Fees INNER JOIN qryTaxRecs_Fees_getOldestFee_ForBRT_1 ON (tblTaxRecs_Fees.BRT = qryTaxRecs_Fees_getOldestFee_ForBRT_1.BRT) AND (tblTaxRecs_Fees.FeeDate = qryTaxRecs_Fees_getOldestFee_ForBRT_1.MinOfFeeDate)
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, tblTaxRecs_Fees.FeeID, tblTaxRecs_Fees.OriginalAmt, tblTaxRecs_Fees.CurrBalanceAmt, tblTaxRecs_Fees.FeeDate, tblTaxRecs_Fees.PaymentStatusID, tblTaxRecs_Fees.Active_YN
FROM tblTaxRecs_Fees INNER JOIN qryTaxRecs_Fees_getOldestFee_ForBRT_1 ON (tblTaxRecs_Fees.BRT = qryTaxRecs_Fees_getOldestFee_ForBRT_1.BRT) AND (tblTaxRecs_Fees.FeeDate = qryTaxRecs_Fees_getOldestFee_ForBRT_1.MinOfFeeDate)
WHERE (((tblTaxRecs_Fees.BRT)>0))
ORDER BY tblTaxRecs_Fees.ID;


Where did I go wrong
0
 
SharathData EngineerCommented:
Can you post some sample data with expected result?
0
 
mlcktmguyAuthor Commented:
Sorry, haven't had time to pull that together yet.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now