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?

 

[GHolden]

Hi,

You could do something like the following.

Sort the table descending (so newest dates are at the top) and just select the top 2.

e.g.

SELECT TOP 2 Key, Date, Comment
FROM Table
ORDER BY Date DESC;

note: you should rename your fields if there actual names are Key, Date, Comment (these are all reseved Property names / function names in VBA)


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

 

[mndrlion]

You will actually need two queries to do this.

The first query called qryLastCommentDate will return the last comment date for each Key.

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

The second query called qryNextToLastCommentDate will return the last and next to last comment dates:

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;

 

[ETID]

Thanks for that...

But all that is returned is the top 2 key numbers
I need top 2 comments by date for each key number...
what am I missing?


''''''''''''


SELECT top 2 NPETPROD_TCOMNT.KEYNBR,NPETPROD_TCOMNT.DTCOMENT, NPETPROD_TCOMNT.SEQ
FROM NPETPROD_TCOMNT
WHERE ((NPETPROD_TCOMNT.SEQ)=1)
ORDER BY NPETPROD_TCOMNT.DTCOMENT DESC
;

 

[GHolden]

Hi,

I can't see any thing wrong with the SQL. You should be getting three columns (one for each in the SELECT). How are you viewing the results?





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

 

[GHolden]

If you only want the Comments field try this...

SELECT TOP 2 Comment
FROM Table
ORDER BY Date DESC;

Substitute your field & table names.



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

 

[ETID]

Datasheet view...in an access query

the "date" format is YYYYMMDD ... but I can't see that being the problem.

 

[ETID]

Let me rephrase...

What is returned is the 3 fields for the top 2 key numbers

I need the 3 fields for each key number, only showing the top 2 dates and corresponding comments...

does that help?

 

[GHolden]

1. Is your 'Date' field definately the one in the Order By Clause?

2. Is your 'Date' field of the DateTime type?

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

 

[ETID]

the date field is just a number...

I.E.

20040407 to represent 4/6/04

so, an order can be established based on this column.


and yes....NPETPROD_TCOMNT.DTCOMENT is the date field

 

[ETID]

Ooops
20040407 to represent 4/7/04

 

[GHolden]

Okay,

If you just do...

SELECT *
FROM NPETPROD_TCOMNT
WHERE ((NPETPROD_TCOMNT.SEQ)=1)
ORDER BY NPETPROD_TCOMNT.DTCOMENT DESC

Do you get the all records in the right order?



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

 

[ETID]

Yes...

 

[GHolden]

Cool,

If you now add Top 2

SELECT TOP 2 *
FROM NPETPROD_TCOMNT
WHERE ((NPETPROD_TCOMNT.SEQ)=1)
ORDER BY NPETPROD_TCOMNT.DTCOMENT DESC




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

 

[ETID]

It's back to grabing all fields for only the top 2 key numbers :-(

 

[GHolden]

I really don't get this. I do this all the time, just check @ MS and they confirm...


If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.


So adding TOP 2 should not change the order.


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

 

[lespaul]

There's a lack of communication here. I think what ETID is trying to do is this:


ID# Date Comments
1 4/4/04 SOmething
2 4/1/04 candy
1 3/30/04 something else
3 3/28/04 chips
1 2/16/04 nothing
2 1/15/04 dips
3 12/12/03 christmas
2 10/16/03 halloween
3 9/1/03 last one

id#1 has three records: 4/4, 3/30, 2/16
id#2 has three records: 4/1, 1/15, 10/16
id#3 has three records: 3/28, 12/12, 9/1

He wants to return:

ID Date Comments
1 4/4
1 3/30
2 4/1
2 1/15
3 3/28
3 12/12

Is that correct?

Leslie

 

[GHolden]

Is that correct ???

That's not the impression I got...


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

 

[Golom]

How 'bout
[blue][tt]
Select [Key],[Date], [Comment]

From tbl T

Where tbl.[Date] IN

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

 

[lespaul]

I need top 2 comments by date for each key number

for each key number
1
2
3

i need top 2 commments by date

key 1 1st date comment
key 1 2nd date comment
key 2 1st date comment
key 2 2nd date comment
key 3 1st date comment
key 3 2nd date comment

right??



Leslie