Is Cross Tab Query The Right Choice

C

cna48

I'm wondering if I'm using the right query type. I'm trying to get multiple
totals for several columns for a given date range.

I would like to see.
[Start Of Week] [Start Of Week] [Start Of Week]
[Status].Cur 45 50 65
[Status].Sol 30 27
12
[Status].May 15 12 0
Totals 90 89
77

Here is the sql view.

SELECT [Brochsteins Schedule].Status, [Brochsteins Schedule].Job,
[Brochsteins Schedule].PM, [Brochsteins Schedule].Detailer, [Brochsteins
Schedule].[Drawing No], [Brochsteins Schedule].Description, [Brochsteins
Schedule].[Detail Hrs], [Brochsteins Schedule].TNM, [Brochsteins
Schedule].[Shop Hrs], [Brochsteins Schedule].[Inst Hrs], [Brochsteins
Schedule].[No Shop Req], [Brochsteins Schedule].[PM/Detail Notes],
[Brochsteins Schedule].[Cust/Design Hold], [Brochsteins Schedule].[1st Sub],
[Brochsteins Schedule].[1st Sub A], [Brochsteins Schedule].[1st Sub C],
[Brochsteins Schedule].[1st Ret], [Brochsteins Schedule].[1st Ret A],
[Brochsteins Schedule].[1st Ret C], [Brochsteins Schedule].[2nd Sub],
[Brochsteins Schedule].[2nd Sub A], [Brochsteins Schedule].[2nd Sub C],
[Brochsteins Schedule].[2nd Ret], [Brochsteins Schedule].[2nd Ret A],
[Brochsteins Schedule].[2nd Ret C], [Brochsteins Schedule].[Field Dim],
[Brochsteins Schedule].[Field Dim A], [Brochsteins Schedule].[Field Dim C],
[Brochsteins Schedule].[Shop Description], [Brochsteins Schedule].[To S/B
Date], [Brochsteins Schedule].[To S/B], [Brochsteins Schedule].[To Shop
Date], [Brochsteins Schedule].[To Shop], [Brochsteins Schedule].[Production
Notes], [Brochsteins Schedule].Core, [Brochsteins Schedule].[Core C],
[Brochsteins Schedule].Ven, [Brochsteins Schedule].[Ven C], [Brochsteins
Schedule].VHrs, [Brochsteins Schedule].Mach, [Brochsteins Schedule].[Mach C],
[Brochsteins Schedule].MHrs, [Brochsteins Schedule].Cabinet, [Brochsteins
Schedule].[Cab C], [Brochsteins Schedule].CHRS, [Brochsteins
Schedule].Finish, [Brochsteins Schedule].[Finish C], [Brochsteins
Schedule].FHrs, [Brochsteins Schedule].Upfit, [Brochsteins Schedule].[Upfit
C], [Brochsteins Schedule].Metal, [Brochsteins Schedule].[Metal C],
[Brochsteins Schedule].MeHrs, [Brochsteins Schedule].Shipped, [Brochsteins
Schedule].[Prod Date], [Brochsteins Schedule].[Thru-Put], [Brochsteins
Schedule].[Ship Date], ([Prod Date]-[To Shop Date])/7 AS [Cycle Time],
DateAdd("d",-Weekday([Prod Date])+1,[Prod Date]) AS [Start of Week]
FROM [Brochsteins Schedule]
WHERE ((([Brochsteins Schedule].Status)="CUR" Or ([Brochsteins
Schedule].Status)="SOL" Or ([Brochsteins Schedule].Status)="MAY") AND
(([Brochsteins Schedule].[Prod Date]) Between [Enter The Start Date] And
[Enter The End Date]))
ORDER BY [Brochsteins Schedule].[Drawing No], [Brochsteins Schedule].[Prod
Date];
 
K

KARL DEWEY

A crosstab will do all but the bottom row. A crosstab feeding a report will
do the bottom row by summing in a footer.
--
KARL DEWEY
Build a little - Test a little


cna48 said:
I'm wondering if I'm using the right query type. I'm trying to get multiple
totals for several columns for a given date range.

I would like to see.
[Start Of Week] [Start Of Week] [Start Of Week]
[Status].Cur 45 50 65
[Status].Sol 30 27
12
[Status].May 15 12 0
Totals 90 89
77

Here is the sql view.

SELECT [Brochsteins Schedule].Status, [Brochsteins Schedule].Job,
[Brochsteins Schedule].PM, [Brochsteins Schedule].Detailer, [Brochsteins
Schedule].[Drawing No], [Brochsteins Schedule].Description, [Brochsteins
Schedule].[Detail Hrs], [Brochsteins Schedule].TNM, [Brochsteins
Schedule].[Shop Hrs], [Brochsteins Schedule].[Inst Hrs], [Brochsteins
Schedule].[No Shop Req], [Brochsteins Schedule].[PM/Detail Notes],
[Brochsteins Schedule].[Cust/Design Hold], [Brochsteins Schedule].[1st Sub],
[Brochsteins Schedule].[1st Sub A], [Brochsteins Schedule].[1st Sub C],
[Brochsteins Schedule].[1st Ret], [Brochsteins Schedule].[1st Ret A],
[Brochsteins Schedule].[1st Ret C], [Brochsteins Schedule].[2nd Sub],
[Brochsteins Schedule].[2nd Sub A], [Brochsteins Schedule].[2nd Sub C],
[Brochsteins Schedule].[2nd Ret], [Brochsteins Schedule].[2nd Ret A],
[Brochsteins Schedule].[2nd Ret C], [Brochsteins Schedule].[Field Dim],
[Brochsteins Schedule].[Field Dim A], [Brochsteins Schedule].[Field Dim C],
[Brochsteins Schedule].[Shop Description], [Brochsteins Schedule].[To S/B
Date], [Brochsteins Schedule].[To S/B], [Brochsteins Schedule].[To Shop
Date], [Brochsteins Schedule].[To Shop], [Brochsteins Schedule].[Production
Notes], [Brochsteins Schedule].Core, [Brochsteins Schedule].[Core C],
[Brochsteins Schedule].Ven, [Brochsteins Schedule].[Ven C], [Brochsteins
Schedule].VHrs, [Brochsteins Schedule].Mach, [Brochsteins Schedule].[Mach C],
[Brochsteins Schedule].MHrs, [Brochsteins Schedule].Cabinet, [Brochsteins
Schedule].[Cab C], [Brochsteins Schedule].CHRS, [Brochsteins
Schedule].Finish, [Brochsteins Schedule].[Finish C], [Brochsteins
Schedule].FHrs, [Brochsteins Schedule].Upfit, [Brochsteins Schedule].[Upfit
C], [Brochsteins Schedule].Metal, [Brochsteins Schedule].[Metal C],
[Brochsteins Schedule].MeHrs, [Brochsteins Schedule].Shipped, [Brochsteins
Schedule].[Prod Date], [Brochsteins Schedule].[Thru-Put], [Brochsteins
Schedule].[Ship Date], ([Prod Date]-[To Shop Date])/7 AS [Cycle Time],
DateAdd("d",-Weekday([Prod Date])+1,[Prod Date]) AS [Start of Week]
FROM [Brochsteins Schedule]
WHERE ((([Brochsteins Schedule].Status)="CUR" Or ([Brochsteins
Schedule].Status)="SOL" Or ([Brochsteins Schedule].Status)="MAY") AND
(([Brochsteins Schedule].[Prod Date]) Between [Enter The Start Date] And
[Enter The End Date]))
ORDER BY [Brochsteins Schedule].[Drawing No], [Brochsteins Schedule].[Prod
Date];
Click to expand...
 

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

How do I calculate totals in a query 2
Import Access records to excel (parameter is a called funct) v.20 18

Top