Showing last 3 dates

[Bay0519]

Hi,

I'm working on a query using MS Access 2003. Right now on my query it showed all receipt dates for items from the beginning up to now. On the report, I just want to show the last 3 receipt dates. How can I do this? Could someone help? I know for the last date I can just use max, but what about the last 2?

Thank you in advance.

[NeoPa]

What you need to do is sort the results in your query (by date descending) then set (Properties of the QueryDef) Top Values to 3.

[Bay0519]

Thank you Neopa for your quick response.

But now the result only showed my top 3 value on the query. But my query have all items that we receipt from beginning FY up to now. I need for all that items to show on the report, but for the date, I just need the last 3 receipts date for each items. If there's an item only have 2 dates, then the 3rd date will be blank. Can you help me please? I'm sorry for not givinng you a clear picture earlier.
here is my query

SELECT FY09_LastReceiptDate_1.ID, FY09_LastReceiptDate_1.TRANSACTION_DATE AS LastReceiptDate
FROM FY09_LastReceiptDate_1
GROUP BY FY09_LastReceiptDate_1.ID, FY09_LastReceiptDate_1.TRANSACTION_DATE
ORDER BY FY09_LastReceiptDate_1.ID, FY09_LastReceiptDate_1.TRANSACTION_DATE DESC;
 

Thank you.

[Bay0519]

Nevermind I found the answer. Basically I just need to create a function that showed 1, 2, 3 as a new field. then from that I just create another query that showed data with number < 4

Thank you for all your help.

[NeoPa]

I think this can be done in pure SQL Bay (so in a QueryDef). Unfortunately I'm still not clear exactly what you're after here, so I knocked up something I hope will illustrate the concept :

SELECT   tTBG.GroupField,
         tTBG.SortField
 
FROM     tblTopByGroup AS tTBG 
 
WHERE    tTBG.ID In(
    SELECT TOP 3 inTBG.ID
 
    FROM         tblTopByGroup AS inTBG
 
    WHERE        (inTBG.GroupField=tTBG.GroupField)
 
    ORDER BY     inTBG.SortField
    )
 
ORDER BY tTBG.GroupField,
         tTBG.SortField