Counting and joining in one query 2

[emozley]

Hi,

I have a query:

SELECT StartTime, EndTime, MeetingID, Provisional, Food
FROM Meetings
WHERE Date=#2/1/2006# And RoomID=5
ORDER BY StartTime;

And this works fine. I also have a table called Attendees that which basically shows what users are attending the meeting which has it's details stored in table 'Meetings'.

The Attendees table has UserID, MeetingID and Arrived which is a Yes/No field.

Is there anyway I can execute a query which will pull all meetings for and join it with the total records in the Attendees table where the MeetingID is the same and also the total number of records where the MeetingID is the same and Arrived is set to False.

Hope that makes sense!

cheers

 

[r937]

SELECT M.StartTime
     , M.EndTime
     , M.MeetingID
     , M.Provisional
     , M.Food
     , count(A.MeetingID) 
          as count_attendees
     , sum(iif(Arrived),0,1) 
          as count_not_arrived
  FROM Meetings as M
left outer
  join Attendees as A
    on A.MeetingID = M.MeetingID   
 WHERE M.Date = #2/1/2006# 
   And M.RoomID = 5
group
    by M.StartTime
     , M.EndTime
     , M.MeetingID
     , M.Provisional
     , M.Food
ORDER 
    BY M.StartTime

r937.com | rudy.ca

 

[emozley]

Hi,

Thanks for this - I am getting an error 'Wrong number of arguments used with function in query expression 'sum(iif(Arrived),0,1)' when I run the query.

I am executing the query in Access if that makes any difference.

cheers

Ed

 

[r937]

try sum(iif(Arrived=True),0,1)

or whatever it is you need to say for those *$%#&$%# Yes/No fields

Arrived is Yes?

Arrived is True?

Arrived = Yes?

Arrived = -1?

i forgot

r937.com | rudy.ca

 

[PHV]

Sum(IIf(Arrived,0,1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[emozley]

Excellent! Stars to you both. cheers!

 

[r937]

doh!!

hey phv, well spotted

<homer>stupid yes/no fields</homer>



r937.com | rudy.ca

 

[emozley]

Posted this in a new thread but just realised it makes more sense to continue in this one - I have tried to extend it so it also does the same thing but this time it also looks in another table called ExternalAttendees but for some reason the figures returned for exttotal and extnotarrived are always the same as inttotal for some reason. Where am I going wrong? cheers

SELECT M.StartTime, M.EndTime, M.MeetingID, M.Provisional, M.Food, count(A.MeetingID) AS inttotal, Sum(IIf(A.Arrived,0,1)) AS intnotarrived, count(E.MeetingID) AS exttotal, Sum(IIf(E.Arrived,0,1)) AS extnotarrived
FROM (Meetings AS M LEFT JOIN Attendees AS A ON A.MeetingID=M.MeetingID) LEFT JOIN ExternalAttendees AS E ON E.MeetingID=M.MeetingID
WHERE M.Date=#2/1/2006# And M.RoomID=5
GROUP BY M.StartTime, M.EndTime, M.MeetingID, M.Provisional, M.Food
ORDER BY M.StartTime;

 

[r937]

where are you going wrong? by cross joining two one-to-many relationships


let's try a different approach:

SELECT M.StartTime
     , M.EndTime
     , M.MeetingID
     , M.Provisional
     , M.Food
     , ( select count(MeetingID)
           from Attendees 
          where MeetingID = M.MeetingID )   
          as inttotal
     , ( select sum(iif(Arrived),0,1) 
           from Attendees 
          where MeetingID = M.MeetingID )  
          as intnotarrived
     , ( select count(MeetingID)
           from ExternalAttendees  
          where MeetingID = M.MeetingID )   
          as exttotal
     , ( select sum(iif(Arrived),0,1) 
           from ExternalAttendees  
          where MeetingID = M.MeetingID )  
          as extnotarrived
  FROM Meetings as M
 WHERE M.Date = #2/1/2006# 
   And M.RoomID = 5
ORDER 
    BY M.StartTime

phv will probably be along in a second to show you yet another way to do it...

r937.com | rudy.ca

 

[emozley]

Excellent thanks - I had no idea you could even do that in SQL I thought it always had to be done using JOIN statements!

Thanks again.

Ed