Adding up row values

I am trying to add up all "fertilizer" costs by customer name. However, I am
getting 3 results per customer due to there being 3 types of fertilizer. How
can I combine into one customer, one fertilizer total sales? Code below.

SELECT [SL Customers].[Customer Name], [SL Inventory].[Item Description],
Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name], [SL Inventory].[Item Description]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;

How can I combine into one customer, one fertilizer total sales?
Drop [SL Inventory].[Item Description] from the query.

SELECT [SL Customers].[Customer Name], Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;

--
Build a little, test a little.


"kthomas" wrote:

I am trying to add up all "fertilizer" costs by customer name. However, I am
getting 3 results per customer due to there being 3 types of fertilizer. How
can I combine into one customer, one fertilizer total sales? Code below.

SELECT [SL Customers].[Customer Name], [SL Inventory].[Item Description],
Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name], [SL Inventory].[Item Description]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;

But that brings in all items purchased by customer when I only need
fertilizer items by customer?

"KARL DEWEY" wrote:

How can I combine into one customer, one fertilizer total sales?
Drop [SL Inventory].[Item Description] from the query.

SELECT [SL Customers].[Customer Name], Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;

--
Build a little, test a little.


"kthomas" wrote:

I am trying to add up all "fertilizer" costs by customer name. However, I am
getting 3 results per customer due to there being 3 types of fertilizer. How
can I combine into one customer, one fertilizer total sales? Code below.

SELECT [SL Customers].[Customer Name], [SL Inventory].[Item Description],
Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name], [SL Inventory].[Item Description]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;

kthomas wrote:

I am trying to add up all "fertilizer" costs by customer name. However, I am
getting 3 results per customer due to there being 3 types of fertilizer. How
can I combine into one customer, one fertilizer total sales? Code below.

SELECT [SL Customers].[Customer Name], [SL Inventory].[Item Description],
Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name], [SL Inventory].[Item Description]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;


You need to remove the item description field from the
Select and Group By clauses.

You also should change the Having clause to a Where clause.

--
Marsh
MVP [MS Access]

On Mon, 15 Mar 2010 15:47:01 -0700, kthomas
wrote:

I am trying to add up all "fertilizer" costs by customer name. However, I am
getting 3 results per customer due to there being 3 types of fertilizer. How
can I combine into one customer, one fertilizer total sales? Code below.

SELECT [SL Customers].[Customer Name], [SL Inventory].[Item Description],
Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name], [SL Inventory].[Item Description]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;

Change the HAVING to WHERE (which applies before the grouping is done) and
remove Item Description from the GROUP BY clause.

To do this in the query grid change the Totals row under Item Description from
the default Group BY to Where (Access will uncheck the Show checkbox).
--

John W. Vinson [MVP]