Combine 3 Queries into 1. Union?

  • Thread starter Thread starter Charles G via AccessMonster.com
  • Start date Start date
C

Charles G via AccessMonster.com

I have 3 queries: qrySingleFactors, qryTeamFactors, qryBothFactors. These
queries contain calculations that differ, but mostly contain similar fields.

qrySingleFactors:
[FullName]
[SumOfNumberOfDepositsProcessed]
[SumOfTotalVolumeProcessed]
[TellerProcessingOptions]
[CPF]
[SumOfHours]
Single DepFactor: [SumOfNumberofDepositsProcessed]/[SumofHours]/39.16*100
Single Vol Factor: [SumOfTotalVolumeProcessed]/[SumOfHours]/1756.32
Single Points: [SumOfNumberofDepositsProcessed]/[SumofHours]/39.16*100+
[SumOfTotalVolumeProcessed]/[SumOfHours]/1756.32

qryTeamFactors:
[FullName]
[SumOfNumberOfDepositsProcessed]
[SumOfTotalVolumeProcessed]
[TellerProcessingOptions]
[CPF]
[SumOfHours]
Team Dep Factor: [SumOfNumberofDepositsProcessed]/[SumofHours]/50.27*100
Team Vol Factor: [SumOfTotalVolumeProcessed]/[SumOfHours]/2854.73
Total Points: [SumOfNumberofDepositsProcessed]/[SumofHours]/50.27*100+
[SumOfTotalVolumeProcessed]/[SumOfHours]/2854.73

qryBothFactors:
[FullName]
[SumOfNumberOfDepositsProcessed]
[SumOfTotalVolumeProcessed]
[TellerProcessingOptions]
[CPF]
[SumOfHours]
Both Dep Factor: [SumOfNumberofDepositsProcessed]/[SumofHours]/44.71*100
Both Vol Factor: [SumOfTotalVolumeProcessed]/[SumOfHours]/2305.52
Total Points: [SumOfNumberofDepositsProcessed]/[SumofHours]/44.71*100+
[SumOfTotalVolumeProcessed]/[SumOfHours]/2305.52


I would like to be able to combine all 3 queries into one so that I can run a
single report. In the report I want:
[FullName] [CPF] Sum: Total Points

For example,

CPF TELLER CPF POINTS
Charles Good Richmond 99.85
John Doe Falls Church 95.55
Jane Doe Norfolk 87.40

-Charles-
 
Dear Charles:

At what point are you having difficulty?

Can you write the 3 separate queries? Keep in mind that the 3 separate
queries must display the exact same columns in the same order.

You can, as I typically do, identiry the 3 sources from which the
information comes, by adding another column. This column, which I commonly
call Source, would say: "Single Factors", "Team Factors", or "Both
Factors". This may or may not be useful later on, but is really good to
have if problems arise.

Simply place " UNION ALL " between the 3 queries (replace any of the spaces
in UNION ALL with newline/carriage returns, but there must be white space.
The finished product will appear.

Is there any other difficulty in doing this? If any of the 3 queries making
up this has a column you want to see but is missing from the other sets,
just put a NULL in the other sets at that point and that column's
information will be included.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison


Charles G via AccessMonster.com said:
I have 3 queries: qrySingleFactors, qryTeamFactors, qryBothFactors. These
queries contain calculations that differ, but mostly contain similar
fields.

qrySingleFactors:
[FullName]
[SumOfNumberOfDepositsProcessed]
[SumOfTotalVolumeProcessed]
[TellerProcessingOptions]
[CPF]
[SumOfHours]
Single DepFactor: [SumOfNumberofDepositsProcessed]/[SumofHours]/39.16*100
Single Vol Factor: [SumOfTotalVolumeProcessed]/[SumOfHours]/1756.32
Single Points: [SumOfNumberofDepositsProcessed]/[SumofHours]/39.16*100+
[SumOfTotalVolumeProcessed]/[SumOfHours]/1756.32

qryTeamFactors:
[FullName]
[SumOfNumberOfDepositsProcessed]
[SumOfTotalVolumeProcessed]
[TellerProcessingOptions]
[CPF]
[SumOfHours]
Team Dep Factor: [SumOfNumberofDepositsProcessed]/[SumofHours]/50.27*100
Team Vol Factor: [SumOfTotalVolumeProcessed]/[SumOfHours]/2854.73
Total Points: [SumOfNumberofDepositsProcessed]/[SumofHours]/50.27*100+
[SumOfTotalVolumeProcessed]/[SumOfHours]/2854.73

qryBothFactors:
[FullName]
[SumOfNumberOfDepositsProcessed]
[SumOfTotalVolumeProcessed]
[TellerProcessingOptions]
[CPF]
[SumOfHours]
Both Dep Factor: [SumOfNumberofDepositsProcessed]/[SumofHours]/44.71*100
Both Vol Factor: [SumOfTotalVolumeProcessed]/[SumOfHours]/2305.52
Total Points: [SumOfNumberofDepositsProcessed]/[SumofHours]/44.71*100+
[SumOfTotalVolumeProcessed]/[SumOfHours]/2305.52


I would like to be able to combine all 3 queries into one so that I can
run a
single report. In the report I want:
[FullName] [CPF] Sum: Total Points

For example,

CPF TELLER CPF POINTS
Charles Good Richmond 99.85
John Doe Falls Church 95.55
Jane Doe Norfolk 87.40

-Charles-
Click to expand...
 
Is there any other difficulty in doing this? If any of the 3 queries making
up this has a column you want to see but is missing from the other sets,
just put a NULL in the other sets at that point and that column's
information will be included.
Click to expand...

This is first time I've used a Union Query and I wanted to make sure it was
appropriate and make sure I was on the right track. I used this SQL for my
query:

SELECT
qrySingleFactors.*
FROM qrySingleFactors

UNION ALL

SELECT
qryTeamFactors.*
FROM qryTeamFactors

UNION ALL SELECT
qryBothFactors.*
FROM qryBothFactors;

After changing some field names and formats to make sure all matched, it
worked perfectly. Thanks.

-Charles-
 
Back
Top