Formulating stats using a query

[hydeyho]

I've developed a course booking system in Access and am now looking at creating a number of reports. When people are invited to a course they are asked to confirm if they will be attending, then an attendance record is kept. I'm trying to produce a report listing number of invites, confirmed, attended, confirmed but didn't attend, and attended but didn't confirm for each course.

I have created a query which lists each course (between 2 dates) and who was invited. How do I create a query which returns an integer figure for the number of invites, confirmed, attended, confirmed but didn't attend, and attended but didn't confirm for each course?

 

[hydeyho]

Right, I've used the 'Group By' and 'Count' functions to work out the values as required, however attended, confirmed, etc are all yes/no and it counts the record for either answer (despite putting True in the criteria). What am I doing wrong?

I've included a pic of the query >

 

[neileg]

Are you aware that Yes is stored as -1 and No as 0? So if you use Sum instead of Count it will produce the total number of Yes answers, albeit as a negative figure.

 

[hydeyho]

Is it possible to invert it to become positive? That would sort out another problem I'm now having.

If any of the search criteria return no records, non of the results from the entire course are displayed. e.g. if everybody who was invited attends the course, the course is omitted!

 

[Rich]

Look up the Abs function

 

[hydeyho]

Great,thanks, but it still omits the entrie record when no records are returned. Is it possible to display 0 so the course is completely ignored?

 

[neileg]

Change the relationship between tblCourse and tblBooking to a left join (ie return all of the records from tblCourse and those records of tblBooking that match).

 

[hydeyho]

I've tried to create the left join but it now throws error 3258. How can I split my SQL into 2 seprate queries?

SELECT tblCourseDetails.intCourseDetails, Sum(tblBooking.attended) AS SumOfattended
FROM tblEmployee INNER JOIN (tblCourseDetails LEFT JOIN tblBooking ON tblCourseDetails.intCourseDetails = tblBooking.intCourseDetails) ON tblEmployee.intEmployee = tblBooking.intEmployee
WHERE (((tblBooking.confirmed)=False) AND ((tblBooking.attended)=True))
GROUP BY tblCourseDetails.intCourseDetails;