asked on

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));

D B

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

aikimark

Do you need the (max price) line item per customer or per customer-order?

pcalabria

ASKER

@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)

pcalabria

ASKER

@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.

D B

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

D B

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.

pcalabria

ASKER

@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 Bernstein

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);

aikimark

@pcalabria

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

pcalabria

ASKER

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!

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.