Need to display the record with the oldest date from a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello: I am familiar with Min and Max. What I want to do is to display the
most recent records from a table. For example. I have 3 accounts (A, B, C)
Each accounts has 4 records each.. I would like a query that would only
display the most recent record for A, B, & C. So instead of a query showing
me 12 records, it would only show me the 3 most recent.

Any help would be appreciated.

Thanks,
 
One Approch can be, to create a query

SELECT M1.*
FROM TableName AS M1
WHERE M1.[FieldName] In (SELECT Top 1 M2.[FieldName]
FROM TableName as M2
WHERE M2..[Account Num Field Name] =M1..[Account Num Field Name]
ORDER BY M2.[FieldName] Desc)

FieldName = The name of the field that indicate which record is the most
recent one, such as a counter or a date
 
I put two dots by mistake

SELECT M1.*
FROM TableName AS M1
WHERE M1.[FieldName] In (SELECT Top 1 M2.[FieldName]
FROM TableName as M2
WHERE M2.[Account Num Field Name] =M1.[Account Num Field Name]
ORDER BY M2.[FieldName] Desc)
=============================
Another option will be, to create two queries
1. Group by query to return the account number and the Max of the field that
you use to indicate which record is the most recent one

2. A query that include the above query join with the table by the Account
number, and the above field, and retun all the fields from the table

--
I hope that helped
Good luck


Ofer said:
One Approch can be, to create a query

SELECT M1.*
FROM TableName AS M1
WHERE M1.[FieldName] In (SELECT Top 1 M2.[FieldName]
FROM TableName as M2
WHERE M2..[Account Num Field Name] =M1..[Account Num Field Name]
ORDER BY M2.[FieldName] Desc)

FieldName = The name of the field that indicate which record is the most
recent one, such as a counter or a date
--
I hope that helped
Good luck


Craig Thompson said:
Hello: I am familiar with Min and Max. What I want to do is to display the
most recent records from a table. For example. I have 3 accounts (A, B, C)
Each accounts has 4 records each.. I would like a query that would only
display the most recent record for A, B, & C. So instead of a query showing
me 12 records, it would only show me the 3 most recent.

Any help would be appreciated.

Thanks,
Click to expand...
Click to expand...
 
Back
Top