By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,835 Members | 964 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,835 IT Pros & Developers. It's quick & easy.

How can I join 2 queries together

P: 31
Hi everyone, I have two seperate queries that work great individually, but I need to join them together to get a combined output. Can someone look at these and help me with the code? Here are the 2 queries below.
Thank you in advance,
Nick

SELECT
Sum([Environmental Rounds].[Fire Extinguishers]) AS [SumOfFire Extinguishers],
Sum([Environmental Rounds].[Fire Exits]) AS [SumOfFire Exits],
Sum([Environmental Rounds].[Fire alarms]) AS [SumOfFire alarms],
Sum([Environmental Rounds].[Evacuation Map]) AS [SumOfEvacuation Map],
Sum([Environmental Rounds].[Doors propped]) AS [SumOfDoors propped],
Sum([Environmental Rounds].[Hallways and stairwells]) AS [SumOfHallways and stairwells],
Sum([Environmental Rounds].[Oxygen valves]) AS [SumOfOxygen valves],
Sum([Environmental Rounds].[Oxygen shutoff]) AS [SumOfOxygen shutoff],
Sum([Environmental Rounds].[Electrical Hazards]) AS [SumOfElectrical Hazards],
Sum([Environmental Rounds].[Electrical Panels]) AS [SumOfElectrical Panels],
Sum([Environmental Rounds].Ceiling) AS SumOfCeiling,
Sum([Environmental Rounds].[Lighting in hall]) AS [SumOfLighting in hall],
Sum([Environmental Rounds].[No items stored on floors]) AS [SumOfNo items stored on floors],
Sum([Environmental Rounds].MSDS) AS SumOfMSDS,
Sum([Environmental Rounds].[Fire Manual Posted]) AS [SumOfFire Manual Posted],
Sum([Environmental Rounds].[Employee fire alarm]) AS [SumOfEmployee fire alarm],
Sum([Environmental Rounds].[Disaster manual posted, and current]) AS [SumOfDisaster manual posted, and current],
Sum([Environmental Rounds].[No Evidence of Smoking]) AS [SumOfNo Evidence of Smoking],
Sum([Environmental Rounds].[Participation in emergency training drills]) AS [SumOfParticipation in emergency training drills],

NZ([SumOfFire Extinguishers],0)+NZ([SumOfFire Exits],0)+NZ([SumOfFire alarms],0)+NZ([SumOfEvacuation Map],0)+NZ([SumOfDoors propped],0)+NZ([SumOfHallways and stairwells],0)+NZ([SumOfOxygen valves],0)+NZ([SumOfOxygen shutoff],0)+NZ([SumOfElectrical Hazards],0)+NZ([SumOfElectrical Panels],0)+NZ([SumOfCeiling],0)+NZ([SumOfLighting in hall],0)+NZ([SumOfNo items stored on floors],0)+NZ([SumOfMSDS],0)+NZ([SumOfFire Manual Posted],0)+NZ([SumOfEmployee fire alarm],0)+NZ([SumOfDisaster manual posted, and current],0)+NZ([SumOfNo Evidence of Smoking],0)+NZ([SumOfParticipation in emergency training drills],0) AS Total_A, [Environmental Rounds].Month

FROM [Environmental Rounds]
WHERE ((([Environmental Rounds].Date) Between [Enter Start Date] And [Enter End Date]))
GROUP BY [Environmental Rounds].Month, [Environmental Rounds].Date
ORDER BY [Environmental Rounds].Date,

------------------------------------------------------------------------------------------------------------

SELECT
Sum([Environmental Rounds Medical Equipment].[Staff wearing ID badge]) AS [SumOfStaff wearing ID badge],
Sum([Environmental Rounds Medical Equipment].[Doors secure]) AS [SumOfDoors secure],
Sum([Environmental Rounds Medical Equipment].[Staff know Dr Noble policy]) AS [SumOfStaff know Dr Noble policy],

NZ([SumOfStaff wearing ID badge],0)+NZ([SumOfDoors secure],0)+NZ([SumOfStaff know Dr Noble policy],0) AS Total_B, [Environmental Rounds Medical Equipment].Month

FROM [Environmental Rounds Medical Equipment]
WHERE ((([Environmental Rounds Medical Equipment].Date) Between [Enter Start Date] And [Enter End Date]))
GROUP BY [Environmental Rounds Medical Equipment].Month, [Environmental Rounds Medical Equipment].Date
ORDER BY [Environmental Rounds Medical Equipment].Date;
May 22 '07 #1
Share this Question
Share on Google+
1 Reply

LacrosseB0ss
100+
P: 113
The easiest way is with a Union statement. What a union does is takes all returned values and pops them in the same table.

Example:
Expand|Select|Wrap|Line Numbers
  1. SELECT a,b,c,d
  2. FROM MyTable
  3.  
  4. UNION
  5.  
  6. SELECT a,b,c,d
  7. FROM MyOtherTable
will return one recordset with all matching criteria.

One thing to be careful of when doing this (and I didn't look too in depth at the query examples, they were a bit lengthy, sorry) is that all selected columns need to be of the same data type. I noticed a lot of SUMs so you should be ok.

Hope this helps
-LB


Hi everyone, I have two seperate queries that work great individually, but I need to join them together to get a combined output. Can someone look at these and help me with the code? Here are the 2 queries below.
Thank you in advance,
Nick
May 22 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.