Top N from Group Queries

[Slap]

I've done some Googling and search the forums but am drawing a blank or not understanding the proposed solutions correctly.

Problem Statement:

Find the last 6 records for each supplier/item combination from a list of 100's of records.

Data:
ORG
VENDOR_NAME
ITEM
PO_NUM
LINE_NUM
LAST_UPDATE
SUM_OF_STEPS
1
2
etc to 10

1-10 are simply a count of separate steps in a process and come from crosstab queries against the original data.

Ultimately the last 6 unique values will be compared to a scorecard to determine if other criteria can be relaxed.

I can attach a sample db if needed, the data is not so confidential or I can replace a few names.

Thanks for any help!

 

[KenHigg]

Can you simply sort down and do a query property for max of 6 records

 

[Slap]

Ken,

Thanks, I have everything sorted but I am not sure what you mean by doing a max 6.

What I need at the end is:

Supplier 1, Item A etc
Supplier 1, Item A etc
Supplier 1, Item A etc
Supplier 1, Item A etc
Supplier 1, Item A etc
Supplier 1, Item A etc

Supplier 2, Item B etc
Supplier 2, Item B etc
Supplier 2, Item B etc
Supplier 2, Item B etc
Supplier 2, Item B etc
Supplier 2, Item B etc


Where those 6 lines for each supplier (or less if there are not 6 or more) at the most recent by LAST_UPDATE (date and time).

Thanks again for your support.

 

[KenHigg]

I see. Hum... I have some ideas but lets see if somebody else comes up with a creative solution. Mine is rather clunky.

 

[Brianwarnock]

I didn't understand your sample data, I had expected multi items per supplier and then they would want the last 6 per combination based on last_update, if so you need to do a ranking query then select as required, it's a while since I did this but a quick search returned this thread.

http://www.access-programmers.co.uk/forums/showthread.php?p=967646&highlight=Rank#post967646

Brian

 

[Slap]

I've uploaded a sample and converted the query into a table for ease.

A supplier can have multiple items but each Supplier + Item is the grouping, for which I need to return the last 6 records in chronological order descending (or less than 6).

 

[Brianwarnock]

That's pretty much what my code on that thread illustrates.

Brian

 

[Brianwarnock]

Found time to look at this today.
You have not responded back so you have either solved it and not bothered or given up on the thread.
For anybody else who has found their way here, here is the SQL based on the table in the attachment. To extract further data for the records join this query back to the table.

Brian

SELECT x.VENDOR_NAME,x.ITEM, x.LAST_UPDATE, (select count(*) + 1
           from tbldata
          where VENDOR_NAME = x.VENDOR_NAME and x.ITEM=ITEM
            and LAST_UPDATE > x.LAST_UPDATE ) AS rank
FROM tbldata AS x
WHERE ((((select count(*) + 1
           from tbldata
          where VENDOR_NAME = x.VENDOR_NAME and x.ITEM=ITEM
            and LAST_UPDATE > x.LAST_UPDATE))<=6))
ORDER BY x.VENDOR_NAME,x.ITEM,x.LAST_UPDATE desc;

 

[Slap]

Brian,

Thank you, I had left work in a rush and wasn't near a PC all weekend.

You solution works perfectly, many thanks for your help!

Regards
Tony