combing three queries to create a report

[wanglede]

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

I have read about unions but I do not know if it would work for this
application.

Any suggestions?

Thanks

QUERY 1:

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

QUERY 2:

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

QUERY 3:

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

 

[Allen Browne]

You could probably solve this problem with a UNION query.

1. Create a new query.
Cancel the Add Table dialog.
Switch to SQL View (View menu.)
Delete the text:
SELECT;

2. Paste in the statement from query1.
Remove last line of the query (from ORDER BY to the semicolon.)
Instead, type:
UNION ALL

3. Paste in the statement from query2.
Again remove the ORDER BY clause, and type:
UNION ALL.

4. Paste in the statement from query3.
Leave the ORDER BY clause and closing semicolon in the query this time.

You may find that you have to modify the queries to get the results you
want, but that's the basic approach.

A better solution would be to redesign the table so it has fields like this:
Area
OperationTypeID
SchDate
PlanHours
ActHours
The OperationTypeID field would contain "Dismantle", "Erect", or "Mod" -
values you would store in an OperationType table. In this way, you select
the kind of operation when you create the record, and your query can just:
GROUP BY Area, OperationTypeID
to get the results you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

I have read about unions but I do not know if it would work for this
application.

Any suggestions?

Thanks

QUERY 1:

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

QUERY 2:

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

QUERY 3:

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

 

[wanglede via AccessMonster.com]

Hi, pardon my ignorance - I am a newbie at Access and basically I am learning
as I create databases. After reading more about union and how all the
columns must be identical, and after entering "null" columns, I can see this
application will not work for the results I require. You mentioned creating
a new table with a Combo Box for the Operation Type ID. I actually already
have a combo box with dismantle, erect, mod but it is being used for a
different purpose. If I create a new table as you suggested would I have to
create a new form based on the new table and as well as create new reports
that I am already using? I do not mind creating a new table for this one
report but my concern is will I have to manually input all the information
again into the new form or is there an easier method for transfering the data
from one table to another? Or perhaps using the existing form for both
tables? Does that make sense? I would like to attach a copy of the tables I
am using for your reference, but I am not sure how to create a hyperlink for
you to view it.

Thank you for your patience.

Delia

You could probably solve this problem with a UNION query.

1. Create a new query.
Cancel the Add Table dialog.
Switch to SQL View (View menu.)
Delete the text:
SELECT;

2. Paste in the statement from query1.
Remove last line of the query (from ORDER BY to the semicolon.)
Instead, type:
UNION ALL

3. Paste in the statement from query2.
Again remove the ORDER BY clause, and type:
UNION ALL.

4. Paste in the statement from query3.
Leave the ORDER BY clause and closing semicolon in the query this time.

You may find that you have to modify the queries to get the results you
want, but that's the basic approach.

A better solution would be to redesign the table so it has fields like this:
Area
OperationTypeID
SchDate
PlanHours
ActHours
The OperationTypeID field would contain "Dismantle", "Erect", or "Mod" -
values you would store in an OperationType table. In this way, you select
the kind of operation when you create the record, and your query can just:
GROUP BY Area, OperationTypeID
to get the results you need.

Hi, I am trying to create a Monthly Summary Report. The only way I can get
the information correctly is to create three different queries. I would

[quoted text clipped - 70 lines]

And [Type End Date:]))
ORDER BY [WCS SCAFFOLD TABLE].AREA;

 

[Allen Browne]

Oaky, firstly the data is always stored in tables, not forms. Forms are only
the interface to the data stored in tables. That means that if you change
the tables, you have to change the forms as well.

You can change the tables without losing data. And you can use an Append
query to copy the data from the existing table to the new one, so you don't
have to enter all the data again.

If I understand correctly, your existing table has *fields* like this:
Area
Sch Dismantle Date Date/Time
Unsch Dismantle Date Date/Time
Plan Dism Hours Number
Act Dism Hours Number
Sch Erection Date Date/Time
Unsch Erection Date Date/Time
Plan Erection Hours Number
Act Erection Hours Number
Sch Mods Date Date/Time
Unsch Mods Date Date/Time
Plan Mods Hours Number
Act Mods Hours Number
In other words, you have the same 4 fields repeating for 3 types of
operation.

The suggestion is that you create a new table with field:
OperationTypeID Text
Enter 3 *records* into this table: "Dismantle", "Erect", and "Mod".

Now create a new table with fields:
Area whatever this currently is
OperationTypeID one of the values from the new lookup table.
OperationDate when this happened.
PlannedHours Number
ActualHours Number

Use an 3 append queries to populate this table from your existing one.

Now you can easily get a sum of Dismantle hours: just query the table, with
criteria on the OperationTypeID. Or you can get the total hours: just query
the table without the criteria.

The core concept is that whenever you see repeating fields like you have, it
*always* means you need a related table where those repeating fields become
records instead of fields.

If this whole design area is new and you want do to some more reading, Jeff
Conrad has lots of links here:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, pardon my ignorance - I am a newbie at Access and basically I am
learning
as I create databases. After reading more about union and how all the
columns must be identical, and after entering "null" columns, I can see
this
application will not work for the results I require. You mentioned
creating
a new table with a Combo Box for the Operation Type ID. I actually
already
have a combo box with dismantle, erect, mod but it is being used for a
different purpose. If I create a new table as you suggested would I have
to
create a new form based on the new table and as well as create new reports
that I am already using? I do not mind creating a new table for this one
report but my concern is will I have to manually input all the information
again into the new form or is there an easier method for transfering the
data
from one table to another? Or perhaps using the existing form for both
tables? Does that make sense? I would like to attach a copy of the
tables I
am using for your reference, but I am not sure how to create a hyperlink
for
you to view it.

Thank you for your patience.

Delia

You could probably solve this problem with a UNION query.

1. Create a new query.
Cancel the Add Table dialog.
Switch to SQL View (View menu.)
Delete the text:
SELECT;

2. Paste in the statement from query1.
Remove last line of the query (from ORDER BY to the semicolon.)
Instead, type:
UNION ALL

3. Paste in the statement from query2.
Again remove the ORDER BY clause, and type:
UNION ALL.

4. Paste in the statement from query3.
Leave the ORDER BY clause and closing semicolon in the query this time.

You may find that you have to modify the queries to get the results you
want, but that's the basic approach.

A better solution would be to redesign the table so it has fields like
this:
Area
OperationTypeID
SchDate
PlanHours
ActHours
The OperationTypeID field would contain "Dismantle", "Erect", or "Mod" -
values you would store in an OperationType table. In this way, you select
the kind of operation when you create the record, and your query can just:
GROUP BY Area, OperationTypeID
to get the results you need.

Hi, I am trying to create a Monthly Summary Report. The only way I can
get
the information correctly is to create three different queries. I would

[quoted text clipped - 70 lines]

And [Type End Date:]))
ORDER BY [WCS SCAFFOLD TABLE].AREA;