How do I calculate totals in a query

C

cna48

I have the following query. I have formated [Prod Date] as "ww" to give me a
week number. I then want to get the sum of [Shop Hrs] for every particulare
"ww". Attached is the sql view. Any help is appreciated.


SELECT [Zee's Schedule + Capacity Known QUERY Query The Real Deal].[Line #],
[Zee's Schedule + Capacity Known QUERY Query The Real Deal].Job, [Zee's
Schedule + Capacity Known QUERY Query The Real Deal].PM, [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].Detailer, [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].[Drawing No], [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].Description, [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].[Detail Hrs], [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].TNM, [Zee's Schedule + Capacity
Known QUERY Query The Real Deal].[Shop Hrs], [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[Inst Hrs], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].[No Shop Req], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].[PM/Detail Notes], [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[Cust/Design Hold], [Zee's Schedule + Capacity
Known QUERY Query The Real Deal].[1st Sub], [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[1st Sub A], [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[1st Sub C], [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[1st Ret], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].[1st Ret A], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].[1st Ret C], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].[2nd Sub], [Zee's Schedule + Capacity Known QUERY Query
The Real Deal].[2nd Sub A], [Zee's Schedule + Capacity Known QUERY Query The
Real Deal].[2nd Sub C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[2nd Ret], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[2nd Ret A], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[2nd Ret C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Field Dim], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Field Dim A], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Field Dim C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Shop Description], [Zee's Schedule + Capacity Known QUERY Query The
Real Deal].[To S/B Date], [Zee's Schedule + Capacity Known QUERY Query The
Real Deal].[To S/B], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[To Shop Date], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[To Shop], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Production Notes], [Zee's Schedule + Capacity Known QUERY Query The
Real Deal].Core, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Core C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].Ven, [Zee's Schedule + Capacity Known QUERY Query The Real Deal].[Ven
C], [Zee's Schedule + Capacity Known QUERY Query The Real Deal].VHrs, [Zee's
Schedule + Capacity Known QUERY Query The Real Deal].Mach, [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].[Mach C], [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].MHrs, [Zee's Schedule + Capacity
Known QUERY Query The Real Deal].Cabinet, [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[Cab C], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].CHRS, [Zee's Schedule + Capacity Known QUERY Query The
Real Deal].Finish, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Finish C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].FHrs, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].Upfit, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Upfit C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].Metal, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Metal C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].MeHrs, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].Shipped, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Prod Date], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Thru-Put], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Ship Date], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Cycle Time]
FROM [Zee's Schedule + Capacity Known QUERY Query The Real Deal]
GROUP BY [Zee's Schedule + Capacity Known QUERY Query The Real Deal].[Prod
Date]
ORDER BY [Zee's Schedule + Capacity Known QUERY Query The Real Deal].[Shop
Hrs];
 
G

Golfinray

As a rule, the easiest way to sum in queries is to build a separate little
query just to sum and then add it back in to the main query. You could build
a query in design view, pull in your query, put ww in a query field, and do
totals and sum.

cna48 said:
I have the following query. I have formated [Prod Date] as "ww" to give me a
week number. I then want to get the sum of [Shop Hrs] for every particulare
"ww". Attached is the sql view. Any help is appreciated.


SELECT [Zee's Schedule + Capacity Known QUERY Query The Real Deal].[Line #],
[Zee's Schedule + Capacity Known QUERY Query The Real Deal].Job, [Zee's
Schedule + Capacity Known QUERY Query The Real Deal].PM, [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].Detailer, [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].[Drawing No], [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].Description, [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].[Detail Hrs], [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].TNM, [Zee's Schedule + Capacity
Known QUERY Query The Real Deal].[Shop Hrs], [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[Inst Hrs], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].[No Shop Req], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].[PM/Detail Notes], [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[Cust/Design Hold], [Zee's Schedule + Capacity
Known QUERY Query The Real Deal].[1st Sub], [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[1st Sub A], [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[1st Sub C], [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[1st Ret], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].[1st Ret A], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].[1st Ret C], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].[2nd Sub], [Zee's Schedule + Capacity Known QUERY Query
The Real Deal].[2nd Sub A], [Zee's Schedule + Capacity Known QUERY Query The
Real Deal].[2nd Sub C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[2nd Ret], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[2nd Ret A], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[2nd Ret C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Field Dim], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Field Dim A], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Field Dim C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Shop Description], [Zee's Schedule + Capacity Known QUERY Query The
Real Deal].[To S/B Date], [Zee's Schedule + Capacity Known QUERY Query The
Real Deal].[To S/B], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[To Shop Date], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[To Shop], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Production Notes], [Zee's Schedule + Capacity Known QUERY Query The
Real Deal].Core, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Core C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].Ven, [Zee's Schedule + Capacity Known QUERY Query The Real Deal].[Ven
C], [Zee's Schedule + Capacity Known QUERY Query The Real Deal].VHrs, [Zee's
Schedule + Capacity Known QUERY Query The Real Deal].Mach, [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].[Mach C], [Zee's Schedule +
Capacity Known QUERY Query The Real Deal].MHrs, [Zee's Schedule + Capacity
Known QUERY Query The Real Deal].Cabinet, [Zee's Schedule + Capacity Known
QUERY Query The Real Deal].[Cab C], [Zee's Schedule + Capacity Known QUERY
Query The Real Deal].CHRS, [Zee's Schedule + Capacity Known QUERY Query The
Real Deal].Finish, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Finish C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].FHrs, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].Upfit, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Upfit C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].Metal, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Metal C], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].MeHrs, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].Shipped, [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Prod Date], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Thru-Put], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Ship Date], [Zee's Schedule + Capacity Known QUERY Query The Real
Deal].[Cycle Time]
FROM [Zee's Schedule + Capacity Known QUERY Query The Real Deal]
GROUP BY [Zee's Schedule + Capacity Known QUERY Query The Real Deal].[Prod
Date]
ORDER BY [Zee's Schedule + Capacity Known QUERY Query The Real Deal].[Shop
Hrs];
Click to expand...
 
J

John W. Vinson

I have the following query. I have formated [Prod Date] as "ww" to give me a
week number. I then want to get the sum of [Shop Hrs] for every particulare
"ww". Attached is the sql view. Any help is appreciated.
Click to expand...

The format applies only to the *DISPLAY* - not to what's used for sorting or
grouping.

Create a calculated field in the query by typing

TheWeek: DatePart("ww", [Prod Date])

and group by it.

I must say that's about the biggest scariest ugliest query name I've ever
seen...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is Cross Tab Query The Right Choice 1
How can I display data/calculate ranges in a query prompt? 4
How do I use checkboxes in an Access form to get query results? 1
How do I limit criteria to a combo box in an Access Query? 1
How do I show all characters before the letter A in a Query? 6
how do I calculate the sum of hours if marked x in a schedule? 2
how do I refer to fields on a form in a query in an adp project? 6
How do I: Query AD from a list of usernames in Excel 1

Top