If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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; |
#2
|
|||
|
|||
Adding up row values
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; |
#3
|
|||
|
|||
Adding up row values
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; |
#4
|
|||
|
|||
Adding up row values
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] |
#5
|
|||
|
|||
Adding up row values
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] |
Thread Tools | |
Display Modes | |
|
|