UNION Query

[Wildster]

Hi,

I'm having trouble with a union query, I've got two queries which I want to combine into one and produce the output

Time_Slot AWOL Paid_Holidays

however the code below just produces: -

Time_Slot AWOL

Not showing the results from the second SELECT statement after the UNION, any suggestions why?

Thanks in advance

SELECT qry2CountOfAllBookedSlots.Time_Slot, qry2CountOfAllBookedSlots.Total_Bookings AS AWOL
FROM qry2CountOfAllBookedSlots
WHERE (((qry2CountOfAllBookedSlots.Booking_Date)=[Forms]![frmBookingTypeCountAllCombined]![EnterDate]) AND ((qry2CountOfAllBookedSlots.Booking_Type)="AWOL"))
UNION ALL SELECT qry2CountOfAllBookedSlots.Time_Slot, qry2CountOfAllBookedSlots.Total_Bookings AS Paid_Holidays
FROM qry2CountOfAllBookedSlots
WHERE (((qry2CountOfAllBookedSlots.Booking_Date)=[Forms]![frmBookingTypeCountAllCombined]![EnterDate]) AND ((qry2CountOfAllBookedSlots.Booking_Type)="Paid Holiday"))
GROUP BY qry2CountOfAllBookedSlots.Time_Slot, qry2CountOfAllBookedSlots.Total_Bookings;

 

[Dennisk]

Why not combine them

as all you appear to be doing is getting booking types AWOL and Paid Holiday
((qry2CountOfAllBookedSlots.Booking_Type)=IN("AWOL",Paid Holiday") )

 

[Atomic Shrimp]

Does all this data ultimately come from the same table? If so, can't you do it with a single new query with broader/combined criteria, rather than trying to unite two queries with different criteria?

 

[Wildster]

Why not combine them

as all you appear to be doing is getting booking types AWOL and Paid Holiday
((qry2CountOfAllBookedSlots.Booking_Type)=IN("AWOL",Paid Holiday) )

I hear what you say about combining them, but I thought that's what a UNION was? How would the code you provided above fit into a statement to bring the data required back?

Thanks

 

[Wildster]

Does all this data ultimately come from the same table? If so, can't you do it with a single new query with broader/combined criteria, rather than trying to unite two queries with different criteria?

The answer is yes it does, however I'm not sure on how to do it in one query. That's the reason that I've done it in two smaller queries and are trying to combine them together.

The main query I have used to return all the different types of Booking_Type and a count of all the Booking_Type (s) is: -

SELECT Booking_Date, Booking_Type, Time_Slot, Count(*) AS Total_Bookings
FROM qryBookedSlots
GROUP BY Booking_Date, Booking_Type, Time_Slot;

I basically need to know how to tweak this so that it returns two seperate counts (one for AWOL & one for Paid Holiday)

Any help would be much appreciated.

 

[Atomic Shrimp]

I think it should just be a case of adding appropriate criteria, so something like:

SELECT Booking_Date, Booking_Type, Time_Slot, Count(*) AS Total_Bookings
FROM qryBookedSlots
GROUP BY Booking_Date, Booking_Type, Time_Slot
HAVING ((Booking_Type Like "AWOL") OR (Booking_Type Like "Paid Holiday"));

('HAVING' is the grouping query equivalent of 'WHERE')

 

[Wildster]

I think it should just be a case of adding appropriate criteria, so something like:

SELECT Booking_Date, Booking_Type, Time_Slot, Count(*) AS Total_Bookings
FROM qryBookedSlots
GROUP BY Booking_Date, Booking_Type, Time_Slot
HAVING ((Booking_Type Like "AWOL") OR (Booking_Type Like "Paid Holiday"));

('HAVING' is the grouping query equivalent of 'WHERE')

That only returns the AWOL and Paid Holidays, but in the following format where the Bookign_Type is either AWOL or Paid Holidays: -

Booking_Date     Booking_Type    Time_Slot      Total_Bookings

What I'm after is producing the same result but where at present the Total_Bookings is a count of all the individual Time_Slot occurances, I want two counts, one count for the AWOL and one for the Paid Holidays.

So the output headings would be: -

Booking_Date      Booking_Type     Time_Slot        AWOL_Count Paid_Hol_Count

I'm guessing that this would have to be done by adding another count field to the SELECT statement, whith one counting for the Booking_Type AWOL and one counting for the Booking_Type "Paid Holidays".

Any suggestions?

Thanks

 

[Atomic Shrimp]

No problem - you can get a total of the two different types like this:

SELECT Sum(IIf([Booking_Type] Like "AWOL",1,0)) AS AWOL_count, Sum(IIf([Booking_Type] Like "Paid Holiday",1,0)) AS Paid_Hol_Count
FROM qryBookedSlots;

 

[Wildster]

No problem - you can get a total of the two different types like this:

SELECT Sum(IIf([Booking_Type] Like "AWOL",1,0)) AS AWOL_count, Sum(IIf([Booking_Type] Like "Paid Holiday",1,0)) AS Paid_Hol_Count
FROM qryBookedSlots;

Brilliant, thanks so much. I was trying to something similar using count to no avail, makes sense now to use Sum instead.

Thanks again

 

[Atomic Shrimp]

There are probably other ways to do it, without the IIF - that's just the one that sprang to mind.

If you just want to grab the numbers into some VBA code in your application, you should be able to do it with the DCount function - something like:
Me.TextBoxName = DCount("*", "qryBookedSlots", "[Booking_Type] Like 'AWOL'")

 

[Wildster]

No problem - you can get a total of the two different types like this:

SELECT Sum(IIf([Booking_Type] Like "AWOL",1,0)) AS AWOL_count, Sum(IIf([Booking_Type] Like "Paid Holiday",1,0)) AS Paid_Hol_Count
FROM qryBookedSlots;

Hi,

Having checked it doesn't quite produce the output I require. It's displaying the same time slots multiple times i.e. if there is a count for the Time_Slot 08:00 for "AWOL" and also for "Paid Holiday", then it displays the following: -

Time_Slot       AWOL     Paid_Holiday
08:00               1               0
08:00               0               1

Rather than how I want it, showing only the time slot once: -

Time_Slot       AWOL     Paid_Holiday
08:00               1               1

Any ideas on how to tweak the code to do this?

Thanks

 

[Atomic Shrimp]

Could you post the SQL for the query that's giving you trouble? It sounds like maybe you just haven't told it to group the time slot field.

Either that, or the 08:00 slots are not in fact the same (maybe one is 08:00:03 and the other is 08:00:04) - in which case, you may need to explicitly format it (or use datepart) before grouping.

 

[Wildster]

Could you post the SQL for the query that's giving you trouble? It sounds like maybe you just haven't told it to group the time slot field.

Either that, or the 08:00 slots are not in fact the same (maybe one is 08:00:03 and the other is 08:00:04) - in which case, you may need to explicitly format it (or use datepart) before grouping.

Good shout, I hadn't grouped the other query!

Thanks for your help