?
Solved

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

Posted on 2014-01-15
6
Medium Priority
?
886 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 38

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Technology Partners: 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!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

800 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