Solved

Query To Get  ID Of Oldest (By Date) Record, MS Access

Posted on 2014-01-15
6
826 Views
Last Modified: 2014-11-05
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
Comment
Question by:mlcktmguy
6 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39783628
SELECT Account, Min(FeeDate) as OldestRec
FROM yourTable
GROUP BY Account
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39783636
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
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39783680
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:mlcktmguy
ID: 39784313
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
 
LVL 40

Expert Comment

by:Sharath
ID: 39788728
Can you post some sample data with expected result?
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39802131
Sorry, haven't had time to pull that together yet.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now