Sums in a query

[saraann422]

I'm having problems with my query. I want to sum each field for clerk. But I either get each clerk once with a sum I don't recognize, each clerk more than once with separeted sums (but they are correct), or an error saying that two of my fields (CC and Cases Promoted to QC) are not part of an aggregate function.

Can anyone help me with this?

Thank you!!
Sarah


SELECT [Clerk - Closed & Promoted to QC].Clerk, ([Clerk - Closed & Promoted to QC]![CC]) AS CC, [Clerk - Closed & Promoted to QC].[Promoted to QC], Sum([Clerk - Closed & Promoted to QC]!EM+[Clerk - Closed & Promoted to QC]!EQ+[Clerk - Closed & Promoted to QC]!RR+[Clerk - Closed & Promoted to QC]!QQ+[Clerk - Closed & Promoted to QC]!T1+[Clerk - Closed & Promoted to QC]!T2+[Clerk - Closed & Promoted to QC]!T3+[Clerk - Closed & Promoted to QC]!T5+[Clerk - Closed & Promoted to QC]!T7+[Clerk - Closed & Promoted to QC]!WR) AS [Priority Mail], Sum([Clerk - Closed & Promoted to QC]!CI+[Clerk - Closed & Promoted to QC]!DD+[Clerk - Closed & Promoted to QC]!FS+[Clerk - Closed & Promoted to QC]!MD+[Clerk - Closed & Promoted to QC]!MM+[Clerk - Closed & Promoted to QC]!PA+[Clerk - Closed & Promoted to QC]!RV+[Clerk - Closed & Promoted to QC]!SS+[Clerk - Closed & Promoted to QC]!TA+[Clerk - Closed & Promoted to QC]!WW) AS [General Mail]
FROM [Clerk - Closed & Promoted to QC], [QC Information]
WHERE ((([Clerk - Closed & Promoted to QC].Date)>=[StartDate] And ([Clerk - Closed & Promoted to QC].Date)<=[EndDate]))
GROUP BY [Clerk - Closed & Promoted to QC].Clerk, ([Clerk - Closed & Promoted to QC]![CC]), [Clerk - Closed & Promoted to QC].[Promoted to QC];

 

[lespaul]

Ok, why do you have QC Information in the FROM clause when none of the fields in the query use that table? I'm also confused because you have [Clerk - Closed & Promoted to QC] and use that using both a query/table format (the dot .) and the forms format (the bang !)... Do you have two objects named [Clerk - Closed & Promoted to QC]? One a form and one a query or table?

The error indicating that the fields are not part of an aggregate function refers to having fields in the SELECT portion of the query and not in the GROUP BY portion of the query.

Just as a quick example, if you have to sum sales for a company and you have a table like this:
[tt]
Orders
OrderID
SalesmanID
CustomerID
{other fields}
OrderTotal[/tt]

and you want to get the total by salesman:

SELECT SalesmanID, SUM(OrderTotal) As Totals FROM Orders
GROUP BY SalesmanID

you would get these results:
[tt]
SalesmanID Totals
1 1520.54
2 1625.53[/tt]

if you left off the GROUP BY clause in the query above you would get the error: "You tried to execute a query that does not have SalesmanID as part of an aggregate" (or whatever the exact verbiage is).

If you want to know what state the sales went to (assume there's a CUSTOMER table that contains the field STATE):

SELECT STATE, Sum(OrderTotal) As Total FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
GROUP BY STATE

[tt]
State Total
CA 100.52
NM 200.67
AZ 500.23[/tt]

If you want by salesman, by state:

SELECT SalesmanID, STATE, SUM(OrderTotal) As Total
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
GROUP BY SalesmanID, STATE

[tt]
SalesmanID State Total
1 NM 100.58
1 CA 50.26
1 AZ 25.23[/tt]

So, hopefully that helps you understand how the aggregate functions (SUM, COUNT, AVG, MIN, MAX) work and use the GROUP BY portion of the query...you want to GROUP BY every field in your SELECT that is NOT an aggregate.

HTH



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com