I'm using MS Access 2000
When my customers place an order they specify a manufacturer and a manufacturer part number.
Each line item (manufacturer and Manufacturer Part number therefore has a line item price (quantity * unit price)
I need help writing a query that will only show me the most expensive line item purchase from each customer.
For example, if a customer buys 100 pieces Motorola 2N2222 which cost .10 each the line item cost is $10
if that customer also buys 500 pieces of Texas Instruments SN7400N for .20 each the line item cost is $100
My report should generate a row that looks like:
01234 John Doe Texas Instruments $100
01235 Bill Doe AMD $50
and so forth
Here is a query that lists all parts purchased. I need to modify it to only display the largest purchase (one row for each customer)
Can anyone help???
SELECT ManufacturerMaster.ShortName, InvoiceHistory.PartNumber, Max([Price]*[quantity]) AS Amount
FROM InvoiceHistory INNER JOIN ManufacturerMaster ON InvoiceHistory.LineID = ManufacturerMaster.LineID
GROUP BY InvoiceHistory.CustomerNumber, ManufacturerMaster.ShortName, InvoiceHistory.PartNumber