Combine queries for chart?

[topwaya]

I have 8 queries that say how many people participated in a specific program between parameter dates.

Here are the queries:

qfltOpsPGASP

SELECT tblLoc.LocID AS [Work Location], tblGASP.PID AS [Total GASP]
FROM (tblLoc INNER JOIN tblPersonal ON tblLoc.LocID = tblPersonal.LocW) INNER JOIN tblGASP ON tblPersonal.PID = tblGASP.PID
GROUP BY tblLoc.LocID, tblGASP.PID, tblGASP.GaspD
HAVING (((tblGASP.GaspD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]));

qfltOpsPLNL

SELECT tblLoc.LocID, tblLNL.LnLTitl, Count(tblLNL.PID) AS [Total LNL Participants]
FROM tblLoc INNER JOIN tblLNL ON tblLoc.LocID = tblLNL.LnLLoc
GROUP BY tblLoc.LocID, tblLNL.LnLTitl, tblLNL.LnLD
HAVING (((tblLNL.LnLD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]));

qfltOpsPMDG

SELECT tblLoc.LocID AS [MDG Location], Count(tblMDG.PID) AS [Total MDG Participants]
FROM tblLoc INNER JOIN tblMDG ON tblLoc.LocID = tblMDG.MDGLoc
WHERE (((tblMDG.MDGSD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd])) OR (((tblMDG.MDGED) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))
GROUP BY tblLoc.LocID;

qfltOpsPNL

SELECT tblLoc.LocID AS [Work Location], Count(tblNL.PID) AS [Total NL Participants]
FROM (tblLoc INNER JOIN tblPersonal ON tblLoc.LocID = tblPersonal.LocW) INNER JOIN tblNL ON tblPersonal.PID = tblNL.PID
GROUP BY tblLoc.LocID, tblNL.NLD
HAVING (((tblNL.NLD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]));

qfltOpsPPED

SELECT tblLoc.LocID AS [PED Location], Count(tblPed.PID) AS [Total PED Participants]
FROM tblLoc INNER JOIN tblPed ON tblLoc.LocID = tblPed.PedLoc
WHERE (((tblPed.PedSD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd])) OR (((tblPed.PedED) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))
GROUP BY tblLoc.LocID;

qfltOpsPSE

SELECT tblLoc.LocID AS [Special Event Location], tblSE.SETitl, Count(tblSE.PID) AS [Total Special Event Participants]
FROM tblSE INNER JOIN tblLoc ON tblSE.SELoc = tblLoc.LocName
WHERE (((tblSE.SED) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))
GROUP BY tblLoc.LocID, tblSE.SETitl;

qfltOpsPTobCes

SELECT tblLoc.LocID AS [Work Location], Count(tblTobCes.PID) AS [Total Tobacco Cessation Participants]
FROM (tblLoc INNER JOIN tblPersonal ON tblLoc.LocID = tblPersonal.LocW) INNER JOIN tblTobCes ON tblPersonal.PID = tblTobCes.PID
WHERE (((tblTobCes.TobCesD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))
GROUP BY tblLoc.LocID;

qfltOpsP

SELECT tblLoc.LocID AS [Work Location], tblWB.WBTitl, Count(tblWB.PID) AS [Total WB]
FROM tblLoc INNER JOIN tblWB ON tblLoc.LocID = tblWB.WBLoc
WHERE (((tblWB.WBD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))
GROUP BY tblLoc.LocID, tblWB.WBTitl;

I would like to have just 1 query to base a 3-D Column Chart off of.

The chart would have the Locations (LocID) across the bottom (X-axis).

The Number of Participants on the left (Z-axis I think)

The type of program (GASP, LNL, MDG, NL, PED, SE, TobCes, WB) displayed in different bars for each location (Y-Axis I think)

Is there a way to write one query to get this?

Thank you for any help!!! It is much appreciated!!

 

[Crowley16]

a Union query?

--------------------
Procrastinate Now!

 

[DotNetGnat]

may be you can use a UNION ALL query...

Select * from yourquery1
UNION ALL
Select * from yourquery2
...and so on...

-DNG

 

[topwaya]

What would I be unioning?
for the chart I want
-a separate location for each program.
-Each program has a separate total.
-Each location has separate programs displayed.

The chart reminds me of a crosstab query, but I am at a loss as if that is really what I need or how to go about combining so many queries?

Thanks for your suggestions! If a union query is required, what would I union?

 

[DotNetGnat]

yes do a union all query and it will solve your problem...
try it out and post back if you get any errors...like this..

-DNG

 

[DotNetGnat]

oops...hit submit button too fast..

as i can see in your each query you are selecting two things...1. Location and 2. Count...

so if you do...

select * FROM qfltOpsPGASP
UNION ALL
select * FROM qfltOpsPLNL
and so on....

you will get a big table with all the results of all your queries combined and you can then create your chart...

-DNG

 

[topwaya]

Thank you very much! I am getting an error message:

Invalid SQL Statement, Expected 'DELETE', 'INSERT', 'PROCEDURE', SELECT', or 'UPDATE'.


I have:

SELECT * FROM qfltOpsPGASP
UNION ALL
SELECT * FROM qfltOpsPLNL
UNION ALL
SELECT * FROM qfltOpsPMDG
UNION ALL
SELECT * FROM qfltOpsPNL
UNION ALL
SELECT * FROM qfltOpsPPED
UNION ALL
SELECT * FROM qfltOpsPSE
UNION ALL
SELECT * FROM qfltOpsPTobCes
UNION ALL
SELECT * FROM qfltOpsPWB
UNION ALL;

Thanks again for all your help!

 

[DotNetGnat]

you dont need UNION ALL in the end...should be

SELECT * FROM qfltOpsPGASP
UNION ALL
SELECT * FROM qfltOpsPLNL
UNION ALL
SELECT * FROM qfltOpsPMDG
UNION ALL
SELECT * FROM qfltOpsPNL
UNION ALL
SELECT * FROM qfltOpsPPED
UNION ALL
SELECT * FROM qfltOpsPSE
UNION ALL
SELECT * FROM qfltOpsPTobCes
UNION ALL
SELECT * FROM qfltOpsPWB;

-DNG

 

[topwaya]

Oh! Thank you!!

The other problem with the queries, is that 3 of them also included a "title" column, but I removed that so they could union.

I have a result that looks like this:

Work Location Total GASP
BTW 5
BTW 2
RVW 3
SFM 2
BB 1
BTW 1
FC 1
NW 1
RVW 2
SFM 2
FC 4
BTW 5
RVW 1
SFM 1
BTW 7

How can I get the result to incllude the different programs? For instance, BTW shows up 5 times, I assume for 5 different programs, but I need to know which programs the participant number is referring to.

Any suggestions?
Thank you so much for all your help!

 

[DotNetGnat]

then i think you need to pull one more field in all of your queries that can tell you what program refers to a particular number...

-DNG

 

[topwaya]

Each query is based on a different table - the table (and its respective query) *are* the different programs.

There is no "field" that states the program
There is a separate table/query for each program

Thanks for all your help, sorry I'm still confused!!

 

[DotNetGnat]

oh ok...you can do this then...

SELECT tblLoc.LocID AS [Work Location], tblGASP.PID AS [Total GASP], [red]"1" AS programnumber[/red]
FROM (tblLoc INNER JOIN tblPersonal ON tblLoc.LocID = tblPersonal.LocW) INNER JOIN tblGASP ON tblPersonal.PID = tblGASP.PID
GROUP BY tblLoc.LocID, tblGASP.PID, tblGASP.GaspD
HAVING (((tblGASP.GaspD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]));

now do that for all your queries but change the number for each differnt program...and then when you do your final UNION query you will have three columns and the third column will tell you which program it is...

-DNG