Return TOP N in 2 table query - REAL ANSWER PLEASE

[TheIMan]

Hi, I've been having trouble getting this query to return the values
I
need. I have two related tables that contain raw material purchase
information. Currently, the query is returning ALL the pruchases for
a
particular item, but I want to limit it to return 10 most recent
purchases for a particular item. I tried using the SELECT TOP
statement, but I keep putting it in the wrong places and the query
returned only 10 records period. I want the LAST 10 for EACH item.
Can
anyone help? Thank you. Below is the query as it stands returning
all
the records: -- Ira

SELECT RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName, RawMaterialsInvtbl.LbsPerDrum,
RawMaterialsInvtbl.Quote1Date, RawMaterialsInvtbl.Quote1Supplier,
RawMaterialsInvtbl.Quote1PriceDrum,
RawMaterialsInvtbl.Quote1PriceTote, RawMaterialsInvtbl.Quote2Date,
RawMaterialsInvtbl.Quote2Supplier,
RawMaterialsInvtbl.Quote2PriceDrum,
RawMaterialsInvtbl.Quote2PriceTote, RawMaterialsInvtbl.Quote3Date,
RawMaterialsInvtbl.Quote3Supplier,
RawMaterialsInvtbl.Quote3PriceDrum,
RawMaterialsInvtbl.Quote3PriceTote,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.Supplier,
RawMaterialPurchaseRecords.Quantity,
RawMaterialPurchaseRecords.UnitOfMeasure,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.PO,
RawMaterialPurchaseRecords.ReceivedDate,
RawMaterialPurchaseRecords.Price,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.TotalPrice


FROM RawMaterialsInvtbl LEFT JOIN RawMaterialPurchaseRecords ON
RawMaterialsInvtbl.RawMaterialID =
RawMaterialPurchaseRecords.RawMaterialID


GROUP BY RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName, RawMaterialsInvtbl.LbsPerDrum,
RawMaterialsInvtbl.Quote1Date, RawMaterialsInvtbl.Quote1Supplier,
RawMaterialsInvtbl.Quote1PriceDrum,
RawMaterialsInvtbl.Quote1PriceTote, RawMaterialsInvtbl.Quote2Date,
RawMaterialsInvtbl.Quote2Supplier,
RawMaterialsInvtbl.Quote2PriceDrum,
RawMaterialsInvtbl.Quote2PriceTote, RawMaterialsInvtbl.Quote3Date,
RawMaterialsInvtbl.Quote3Supplier,
RawMaterialsInvtbl.Quote3PriceDrum,
RawMaterialsInvtbl.Quote3PriceTote,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.Supplier,
RawMaterialPurchaseRecords.Quantity,
RawMaterialPurchaseRecords.UnitOfMeasure,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.PO,
RawMaterialPurchaseRecords.ReceivedDate,
RawMaterialPurchaseRecords.Price,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.TotalPrice


ORDER BY RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName;

 

[Allen Browne]

Answered in m.p.a.queries