Ranking Records with multiple criteria.

[Rachel]

I have honestly looked through this forum and have tried multiple codes from
multiple threads of questions similar but I just can not get this to work for
me - and am in kind of a bind to get it working. Please help!

I want to show the top 20 parts purchased by customer. Top 20 by customer
number is based on qty shipped - tie breaker needs to be cost of a part -
highest cost taking the lead. simple enough! Can't get it to work.

CUST_ID CUST NAME ITEM QTY SHP PART COST RANK
123 ABC 111 5 3.00
1
123 ABC 222 5 2.00
2
123 ABC 333 4 25.00
3
456 DEF 111 10 3.00
1
456 DEF 888 1 100.00
2

 

[Allen Browne]

1. Create a query using your orders table.

2. Depress the Totals button on the toolbar in query design.
Access adds a Total row to the query design grid.

3. Add the Cust_ID and Item fields to the grid.
Accept Group By under these fields.

Type into the Field row:
Amount: Sum([Qty Shp] * [Part Cost])
Choose Expression in the Total row under this field.

5. Save the query as (say) qryCustAmount. Close

6. Create another query using your Customers and Items tables, but not the
orders table you showed us. There will be no line joining the 2 tables in
the upper pane of query design.

7. Use a subquery in the Criteria row under the Item field.
Something like this:
IN (SELECT TOP 20 Item
FROM qryCustAmount
WHERE Cust_ID = Table1.CustID
ORDER BY Amount, Item)

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html