Putting multiple query results into one table.

Konai · Dec 28, 2009

Using Access 2007

I have constructed fifteen queries that are modeled like this:

Code:

Query Name::PS_One
SELECT CName, COUNT(CName) AS PC
FROM Contact
WHERE CType=1 And EDate Between Start_Date And End_Date
GROUP BY Contact.[CName];

Query Name::PS_One_Total
SELECT Count(PS_One.[CName]) AS ["PS"]
FROM PS_One;

Query Name::PS_One_Percent
SELECT Round(([PS_One_Total]!["PS"])/([CCount]!["CCount"])*100,2) AS test
FROM CCount, PS_One_Total;

The second query would differ in that CType would = 2 instead of 1, and it continues on for 3, 4, and 5.
thus producing 5 queries titled
PS_One_Percent, PS_Two_Percent, PS_Three_Percent, PS_Four_Percent, and
PS_Five_Percent
(for simplification the actual query names were changed)

I am trying to find a way of storing each one of these individual query results in ONE table.
If that can be done then I can generate a report with all 5 calculated percents on one report (without using sub reports).

If anyone could help me get that done I would be very grateful.
If someone has another solution to this that would also be wonderful.
Thanks in advance.

EDIT

I tried using a UNION on each of the 5 queries above, but this produces another problem.
I have no way of labeling each individual percentage when I make the report from that UNION query.
The desired report should look something like this...
Percentage_ONE : <label for perecentage one>
Percentage_TWO : <label for perecentage two>
etc.

wiklendt · Dec 29, 2009

have you tried grouping in your report based off your union query?

Konai · Dec 29, 2009

Perhaps I am not doing the UNION correctly.
I do not see any option for grouping when I try and make a report
from the UNION query.

here is my UNION query

Code:

SELECT test1
FROM PS_One
UNION ALL
SELECT test2
FROM PS_Two
UNION ALL
SELECT test3
FROM PS_Three
UNION ALL
SELECT test4
FROM PS_Four
UNION ALL 
SELECT test5
FROM PS_Five;

When I run this query I see all 5 numbers (some of which are 0) listed under
a field name of test1 ?!?
I was trying to get them grouped together in separate fields (not separate records)
can that be done inside of a query, or do I need to use something else?

Here is the output I am seeing. (attached)

boblarson · Dec 29, 2009

It should be:

Code:

SELECT test1, Null As test2, Null As test3, Null As test4, Null As test5
FROM PS_One
UNION ALL
SELECT Null As test1, test2, Null As test3, Null As test4, Null As test5
FROM PS_Two
UNION ALL
SELECT Null As test1, Null As test2, test3, Null As test4, Null As test5
FROM PS_Three
UNION ALL
SELECT Null As test1, Null As test2, Null As test3, test4, Null As test5
FROM PS_Four
UNION ALL 
SELECT Null As test1, Null As test2, Null As test3, Null As test4, test5
FROM PS_Five;

Konai · Dec 29, 2009

Thank you!! that looks exactly like what i was trying to do in the first place

I will try this immediately!

Konai · Dec 29, 2009

OK I it's very close now I think, but that query did not quite put them all on the same row. I am thinking that a max value query on each individual field,
could clear this up. I am just not certain how to do that right now.

I'll be looking for that, but in the meantime
This is what i was trying to generate
________Field1_______Field2_______Field3_______Field4_______Field5
row1: ValOFTest1 | ValOFTest2 | ValOFTest3 | ValOFTest4 | ValOFTest5

so basically im trying to go from
1 a, 0, 0, 0, 0
2 0, b, 0, 0, 0
3 0, 0, c, 0, 0
4 0, 0, 0, d, 0
5 0, 0, 0, 0, e

to

1 a, b, c, d, e

This is still way better than what I had before though!.. tyvm

EDIT

Ok I figured out how to get the max values to produce one row containing all the data I was looking for.
Thanks again for your help Wiklendt and Boblarson!

jpmourar · May 3, 2011

Can you tell me how you combined the MAX with the UNION ALL? I can't get it to work. I have the exact same problem that you did, combining a bunch of COUNTS into one row. Thanks.

Putting multiple query results into one table.

Konai

Registered User.

wiklendt

i recommend chocolate

Konai

Registered User.

Attachments

boblarson

Smeghead

Konai

Registered User.

Konai

Registered User.

jpmourar

New member

Similar threads

Users who are viewing this thread