Sum of total customer accounts

[Guest]

Hello,

My list breaks out customer sales by state (ie, Customer A Idaho Sales;
Customer A Oregon Sales, etc) I want to create a query that sums the
customer's total sales accross states. How do I design a query to do this?
Thanks

 

[Guest]

Omit the state from the query grouping or use a footer to sum.

 

[Guest]

if you are using the wizard you will come to a screen that asks you if you
want detail or summary. Choose summary on the State and Customer.

 

[John W. Vinson]

Hello,

My list breaks out customer sales by state (ie, Customer A Idaho Sales;
Customer A Oregon Sales, etc) I want to create a query that sums the
customer's total sales accross states. How do I design a query to do this?
Thanks

Clarification please: do you mean that you have a (correct) table with fields
CustomerID, State and Sales? Or do you have an (incorrect, spreadsheetish)
table with 50 (maybe fewer) fields Idaho Sales, Oregon Sales, Utah Sales?

You can get the sum either way... but the first is easier (a totals query
grouped by Customer, summing Sales, and omitting the state) and more flexible.

John W. Vinson [MVP]

 

[Guest]

If you want to show the total sales both by state and across states then to
do it in a single query you'd need to use a subquery correlated with the
outer query on the Customer column, e.g. to sum by state per customer and for
all states per customer:

SELECT Customer, State, SUM(Sales) AS TotalSalesByState,
(SELECT SUM(Sales)
FROM YourTable AS T2
WHERE T2.Customer = T1.Customer) AS TotalSalesAllStates
FROM YourTable AS T2
GROUP BY Customer, State;

Alternatively you can create one query grouped by customer then state and
sum the sales, another query grouped just by Customer and sum the sales, then
join the two queries on the Customer columns in a third query to give the
sums by state and across states.

But it would be better to do it in a report in which you can group first by
customer, then by state and include a text box in each group footer with a
ControlSource of:

=Sum([Sales])

Ken Sheridan
Stafford, England