Solved

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

Posted on 2014-01-15
6
844 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 35

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

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)

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

773 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