Need help writing an MS Access query

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
HAVING (((Max([Price]*[quantity]))>0));
pcalabriaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Doug BishopDatabase DeveloperCommented:
This is more like pseudo code as I am not certain where all the objects actually reside, but if you pattern your query after this it should work:
SELECT	ih.InvoiceNumber,
	ih.CustomerName,
	mm.ManufacturerName,
	lic.LineItemCost
FROM	InvoiceHistory ih
INNER JOIN ManufacturerMaster mm
ON	ih.LineID = mm.LineID
INNER JOIN	(
		SELECT	CustomerNumber,
			InvoiceNumber,
			MAX(Price * Quantity) AS LineItemCost
		FROM	InvoiceHistory
		GROUP BY CustomerNumber,
			InvoiceNumber
		) lic
ON	ih.InvoiceNumber = lic.InvoiceNumber
AND	ih.CustomerNumber = lic.CustomerNumber

Open in new window

aikimarkCommented:
Do you need the (max price) line item per customer or per customer-order?
pcalabriaAuthor Commented:
@LVL48  Per customer.  I want to know the largest order each customer has placed with us,
and also know what they bought (manufacturer and part number)
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

pcalabriaAuthor Commented:
@LVL15 Thanks for the code.  Unfortunately I receive an sysntax error when posting to the query builder in ms access and I don't understand the code well enough to troubleshoot.
Doug BishopDatabase DeveloperCommented:
It would ne nice if Access had window functions. Given it doesn't try this. The only caveat is, if a customer has two or more invoices (or two line items in the same invoice) where the total cost is the same, you'll return all matching rows.
SELECT	ih.InvoiceNumber,
		ih.CustomerName,
		mm.ManufacturerName,
		maxCost.LineItemCost
FROM	InvoiceHistory ih
INNER JOIN ManufacturerMaster mm
ON	ih.LineID = mm.LineID
INNER JOIN	(
		SELECT	CustomerNumber,
			InvoiceNumber,
			MAX(Price * Quantity) AS LineItemCost
		FROM	InvoiceHistory
		GROUP BY CustomerNumber,
			InvoiceNumber
		) lic
ON	ih.InvoiceNumber = lic.InvoiceNumber
AND	ih.CustomerNumber = lic.CustomerNumber
INNER JOIN	(
		SELECT	CustomerNumber,
			MAX(Price * Quantity) AS LineItemCost
		FROM	InvoiceHistory
		GROUP BY CustomerNumber
		) maxCost
ON	maxCost.CustomerNumber = ih.CustomerNumber
AND	maxCost.LineItemCost = lic.LineItemCost

Open in new window

Doug BishopDatabase DeveloperCommented:
As I said, this is mostly pseudo code. I'm not certain where you get certain column names, etc. For example, I used ManufactererName, byut it may be mm.ShortName per your example. mm. is just an alias for ManufacturerMaster, so that once you define the alias, instead of using ManufacturerMaster.ShortName, you can use mm.ShortName. Makes code much more readable.

I assume the first column in your desired output is an invoice number (e.g. 01234, 01235). I didn't see anything in your sample query that identified an invoice number, so I used InvoiceHistory.InvloiceNumber (alias=ih). This may or may not be the correct column name. Without the actual database schema (all table names and column names) we have to 'guess' at some stuff and it will be up to you to 'clean up'  code.
pcalabriaAuthor Commented:
@Doug  ugh!  I've never used an alias before.. I tried to write the code out in long had but it didn't work. What am I doing wrong?

Gues I don't know what to do with lic???

SELECT invoicehistory.InvoiceNumber,
invoicehistory.CustomerName,
ManufacturerMaster.shortname,
lic.LineItemCost
FROM InvoiceHistory
INNER JOIN ManufacturerMaster
ON invoicehistory.LineID = ManufacturerMaster.LineID
INNER JOIN (
SELECT CustomerNumber,
InvoiceNumber,
MAX(Price * Quantity) AS LineItemCost
FROM InvoiceHistory
GROUP BY CustomerNumber,
InvoiceNumber
) lic
ON invoicehistory.InvoiceNumber = lic.InvoiceNumber
AND invoicehistory.CustomerNumber = lic.CustomerNumber
David BernsteinChief Developer/ Microsoft AccessCommented:
This should accomplish the goal

Cust      Prod      quantity      price
a                    p1           100      $0.10
a                      p2          50      $0.01
b                      p3          25      $0.10
b                      p4         10      $0.03

query4
SELECT Table1.Cust, Table1.Prod, Sum(([quantity]*[price])) AS Amount
FROM Table1
GROUP BY Table1.Cust, Table1.Prod;

query5
SELECT Query4.Cust, Max(Query4.Amount) AS MaxOfAmount
FROM Query4
GROUP BY Query4.Cust;

query6
SELECT Query4.Cust, Query4.Prod, Query4.Amount, Query5.MaxOfAmount
FROM Query4 INNER JOIN Query5 ON (Query4.Amount = Query5.MaxOfAmount) AND (Query4.Cust = Query5.Cust);
aikimarkCommented:
@pcalabria

Where do you stand with this question?  Was David's comment the solution?  If not, what is missing?
pcalabriaAuthor Commented:
Hello..

I was trying to get this finished before my xmas vacation but we ran out of time... I get back to work Monday so I should be looking at it then!

I tried all the solutions that came in before the break but they did not work... this email came I afterwards!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.