Return records where date = max and where date = next to max 5

[ETID]

Hi,

Can this be done in a access query?

I have 3 fields,...key, date, comment

I need to return both the last comment and the next to last comment for each key.

any clues?

 

[ETID]

LesPaul has it nailed as to Identifying the problem...

 

[ETID]

Although ...keep in mind that I'm Looking for the Last and Next to last date & comment for each key...

Thanks to all for the help so far...

So,.. is this doable in a single query?

 

[mndrlion]

You will need to create 3 queries to do it. But you only need to call the last one to get the result that you are looking for:

First Query qryLastCommentDate
SELECT tblComments.Key, Max(tblComments.Date) AS LastCommentDate
FROM tblComments
GROUP BY tblComments.Key;

Second Query qryNextToLastCommentDate
SELECT tblComments.Key, [qryLastCommentDate].LastCommentDate, Max(tblComments.Date) AS NextToLastCommentDate
FROM tblComments INNER JOIN qryLastCommentDate ON tblComments.Key=[qryLastCommentDate].Key
WHERE (((tblComments.Date)<[LastCommentDate]))
GROUP BY tblComments.Key, [qryLastCommentDate].LastCommentDate;


Third Query qryLastAndNextToLastComments
SELECT tblComments.Key, tblComments.Date, tblComments.Comment
FROM tblComments
WHERE (((tblComments.Key) In (SELECT Key from qryLastCommentDate)) AND ((tblComments.Date) In (SELECT LastCommentDate from qryLastCommentDate))) OR (((tblComments.Key) In (SELECT Key from qryNextToLastCommentDate)) AND ((tblComments.Date) In (SELECT NextToLastCommentDate from qryNextToLastCommentDate)));

 

[Golom]

Sorry ... missed a Clause
[blue][tt]
Select T.[Key], T.[Date], T.[Comment]

From tbl T

Where T.[Date] IN

(Select TOP 2 X.[Date] From tbl X
Where X.[Key] = T.[Key]
Order By X.[Date] DESC)
[/tt][/blue]

 

[lespaul]

Golom's query should do the trick for you then!

leslie

 

[GHolden]

ETID,

Sorry for wasting time, should learn to read properly !!



There are two ways to write error-free programs; only the third one works.

 

[ETID]

I appreciate everyones's help & time...another question though...what does the x refer to in the sql by Golom?
...Do I need another data source?


Select T.[Key], T.[Date], T.[Comment]

From tbl T

Where T.[Date] IN

(Select TOP 2 X.[Date] From tbl X
Where X.[Key] = T.[Key]
Order By X.[Date] DESC)

 

[GHolden]

Hi,

X is the table name (same table as outer query table) you will have to Alias it though for the T.Key = X.Key.

i.e.

Select T.[key], T.[date], T.[comment]
From NPETPROD_TCOMNT as T

Where T.[date]IN

(Select TOP 2 X.[date] From NPETPROD_TCOMNT as X
Where X.[key]= T.[key]
Order By X.[date] DESC)



There are two ways to write error-free programs; only the third one works.

 

[lespaul]

Since you are correlating a table to itself, each one needs its own "name". This is the tables alias

Select T.[key], T.[date], T.[comment]
From NPETPROD_TCOMNT as T this is the alias for the first instance of the table

Where T.[date]IN

(Select TOP 2 X.[date] From NPETPROD_TCOMNT as X this is the alias for the second instance of the table
Where X.[key]= T.[key]here is where the two tables are correlated so you get the correct date for each id
Order By X.[date] DESC)


Leslie

 

[ETID]

That's the ticket...Stars for everybody.


Thanks again to All

 

[Golom]

Just a cautionary note ...

The above SQL can conceivably generate more than two records for each key value if there are multiple entries for a date. For example
[tt]
Key Date Comment
1 01/05/04 Jan 5 Comment
1 01/15/04 Jan 15 Comment 1
1 01/15/04 Jan 15 Comment 2
[/tt]
Would return all three records because there are two 01/15/04 records and there's no way to decide which to return.

 

[ETID]

Thanks.....but in this case it's only one date per comment