Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-15
6
Medium Priority
?
911 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 48

Expert Comment

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

Expert Comment

by:Dale Fye
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 39

Accepted Solution

by:
PatHartman earned 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 41

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

604 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