Ask Your Question
0

Counting Confirms and Attendees in Query to Display in Report

asked 2016-04-03 07:17:27 +0200

Alb58 gravatar image

I want to make a report for event attendance based on how many confirms and attendees were obtained by a caller. I made a query where the first column is Name of Caller, the second is Event Name, the third is Attendee Name and fourth and fifth are boolean variables for Confirmed to Attend and Attended. I can run a query using the count function and that gives me only a total number of either confirms or attendees for each caller but not both totals for each caller.

I could solve this if there was a function in the report builder counting the number of TRUEs but I haven't found one that does this. I could also solve this by replacing the TRUE/FALSE value to a 1 or 0 integer value in the query I made by running another query on this first one as a subquery.

Does anyone know if there is a way to do either of the above?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2016-04-03 15:37:43 +0200

Ratslinger gravatar image

updated 2016-04-03 15:38:41 +0200

Boolean values are TRUE/FALSE and 1/0. If Confirmed is like saying the Confirmed is TRUE or 1. Your answer is all in the SQL statement. You can enter your statement similar to:

SELECT "Caller","Event", SUM(CASE WHEN "Confirmed" Then 1 Else 0 End) AS "TotalConfirmed", SUM(CASE WHEN "Attended" Then 1 Else 0 End) AS "TotalAttended" FROM "Table" AS "Table" GROUP BY "Caller", "Event" ORDER BY "Caller" ASC

Enter your query statement directly in SQL view and turn on 'Run SQL command directly' using toolbar button 'SQL'.

edit flag offensive delete link more

Comments

Thank you. I used the CASE statement and then added the number of confirms and attendees in the report. One needs to write "Confirmed" and "Attended" between CASE and WHEN, correct?

Alb58 gravatar imageAlb58 ( 2016-04-28 14:32:12 +0200 )edit

Nothing goes between CASE & WHEN. CASE is checking (using WHEN) if field "Confirmed/Attended" is ON & if so, adds 1 to the count of "TotalConfirmed/TotalAttended" which is what is to be used in your report. The structure of the statement is good as is & should only require you to use your actual field/table names.

Ratslinger gravatar imageRatslinger ( 2016-04-28 18:06:12 +0200 )edit

I might have used a slightly different version of the statement. I wrote CASE "Confirmed" WHEN 'TRUE' THEN 1 ELSE 0 END "ConfirmedInt". I then did a SUM of all ConfirmedInt in the report itself. In any case the use of the CASE statement did it. thanks!

Alb58 gravatar imageAlb58 ( 2016-06-02 17:36:01 +0200 )edit

Thank you for the feedback. Whatever way works best for you is what you should use. It is always a pleasure to hear back that the problem is resolved (regardless of who solved it) instead of no response or even that the answer is accepted.

Ratslinger gravatar imageRatslinger ( 2016-06-02 20:34:51 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2016-04-03 07:17:27 +0200

Seen: 115 times

Last updated: Apr 03 '16