find second most recent entry

[elielson]

I need to find the second MOST RECENT entry for each entry in a table

The table fields are:
Unit, Date, Result

a, 24/12/06, pass
b, 10/10/06, fail
a, 20/12/06, fail


What I need is query which picks up:

For unit 'a': a, 20/12/06, fail
For unit 'b': null (there is only one entry).

I am using access.

Many thanks!

 

[Matt]

I need to find the second MOST RECENT entry for each entry in a table

The table fields are:
Unit, Date, Result

a, 24/12/06, pass
b, 10/10/06, fail
a, 20/12/06, fail

What I need is query which picks up:

For unit 'a': a, 20/12/06, fail
For unit 'b': null (there is only one entry).

I am using access.

Many thanks!

Do you want only to return the date when there is more than one record
for a unit?

 

[elielson]

Thank you for your help.

Does not really matter. It can return the data or as null.

I can run a query to find duplicated entries on UNIT.

I need to find the second MOST RECENT entry for each entry in a table

[quoted text clipped - 13 lines]

Many thanks!

Do you want only to return the date when there is more than one record
for a unit?

 

[Matt]

Thank you for your help.

Does not really matter. It can return the data or as null.

I can run a query to find duplicated entries on UNIT.

[quoted text clipped - 13 lines]

Many thanks!

Do you want only to return the date when there is more than one record
for a unit?- Hide quoted text -- Show quoted text -

Are you familiar with sub queries?

You could do something like:

SELECT a.*
FROM Tablename a
WHERE Date = (SELECT Max(Date) FROM Tablename WHERE a.Unit = Unit);


I just threw that together really quick and didnt check it, but that is
the idea.
If this does not work, assuming you have some knowledge of queries, 10
minutes of SubQuery research should get you what you need!

 

[elielson via AccessMonster.com]

Matt... I am a begginner so I may have done something wrong. but the query
you gave was picking up only the most recente date ( I needed the second most
recent).
I did a bit of search and worked out like this:

SELECT a.Date, a.Unit, a.value, COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.Unit=b.Unit AND a.Date <= b.Date
GROUP BY a.Date, a.Unit, a.Value
HAVING COUNT(*) <= 3

Then I wrote another query to pick up only Rank '2' fields.

Thank you for your help.

[quoted text clipped - 10 lines]
Are you familiar with sub queries?

You could do something like:

SELECT a.*
FROM Tablename a
WHERE Date = (SELECT Max(Date) FROM Tablename WHERE a.Unit = Unit);

I just threw that together really quick and didnt check it, but that is
the idea.
If this does not work, assuming you have some knowledge of queries, 10
minutes of SubQuery research should get you what you need!