Combining three queries into one

W

wanglede

Hi, I am trying to create a Monthly Summary Report. The only way I ca
get the information correctly is to create three different queries.
would like to create a report based on 1 query. I have tried to make
query with the relationship between the 3 queries linked through ARE
(as this is common with all 3 queries); but I am not getting the sam
results. I need to count how many days a dismantle, erection, or mo
has been completed per month per area. I also need to get the sum o
hours. How would I create a report based on the three queries. Th
wizard does not allow me to use the 3 queries to generate a report;
am in the process of trying to build a report from scratch, but woul
prefer to use the wizard.

Any suggestions?

Thanks

QUERY 1:

SELECT DISTINCTROW [WCS SCAFFOLD TABLE].AREA, [WCS SCAFFOLD TABLE].[SC
DISMANTLE DATE], Count([WCS SCAFFOLD TABLE].[SCH DISMANTLE DATE]) A
[CountOfSCH DISMANTLE DATE], Sum([WCS SCAFFOLD TABLE].[PLAN DIS
HOURS]) AS [Sum Of PLAN DISM HOURS], Sum([WCS SCAFFOLD TABLE].[ACT DIS
HOURS]) AS [Sum Of ACT DISM HOURS]
FROM [WCS SCAFFOLD TABLE]
GROUP BY [WCS SCAFFOLD TABLE].AREA, [WCS SCAFFOLD TABLE].[SCH DISMANTL
DATE]
HAVING ((([WCS SCAFFOLD TABLE].[SCH DISMANTLE DATE]) Between [Typ
Beginning Date:] And [Type End Date:]))
ORDER BY [WCS SCAFFOLD TABLE].AREA;

QUERY 2:

SELECT DISTINCTROW [WCS SCAFFOLD TABLE].AREA, [WCS SCAFFOLD TABLE].[SC
ERECTION DATE], Count([WCS SCAFFOLD TABLE].[SCH ERECTION DATE]) A
[CountOfSCH ERECTION DATE], [WCS SCAFFOLD TABLE].[UNSCH ERECTION
DATE], Count([WCS SCAFFOLD TABLE].[UNSCH ERECTION DATE]) A
[CountOfUNSCH ERECTION DATE], Sum([WCS SCAFFOLD TABLE].[PLAN EREC
HOURS]) AS [Sum Of PLAN ERECT HOURS], Sum([WCS SCAFFOLD TABLE].[AC
ERECT HOURS]) AS [Sum Of ACT ERECT HOURS]
FROM [WCS SCAFFOLD TABLE]
GROUP BY [WCS SCAFFOLD TABLE].AREA, [WCS SCAFFOLD TABLE].[SCH ERECTIO
DATE], [WCS SCAFFOLD TABLE].[UNSCH ERECTION DATE]
HAVING ((([WCS SCAFFOLD TABLE].[SCH ERECTION DATE]) Between [Typ
Beginning Date:] And [Type End Date:]))
ORDER BY [WCS SCAFFOLD TABLE].AREA;

QUERY 3:

SELECT DISTINCTROW [WCS SCAFFOLD TABLE].AREA, [WCS SCAFFOLD TABLE].[SC
MODS DATE], Count([WCS SCAFFOLD TABLE].[SCH MODS DATE]) AS [CountOfSC
MODS DATE], [WCS SCAFFOLD TABLE].[UNSCH MODS DATE], Count([WCS SCAFFOL
TABLE].[UNSCH MODS DATE]) AS [CountOfUNSCH MODS DATE], Sum([WCS SCAFFOL
TABLE].[ACT MOD HOURS]) AS [Sum Of ACT MOD HOURS]
FROM [WCS SCAFFOLD TABLE]
GROUP BY [WCS SCAFFOLD TABLE].AREA, [WCS SCAFFOLD TABLE].[SCH MOD
DATE], [WCS SCAFFOLD TABLE].[UNSCH MODS DATE]
HAVING ((([WCS SCAFFOLD TABLE].[SCH MODS DATE]) Between [Type Beginnin
Date:] And [Type End Date:]))
ORDER BY [WCS SCAFFOLD TABLE].AREA
 
Top