Summary Report

[Guest]

I have a tblCandidates. Calls are made to candidates. The date the call is
made is stored. The result of the call is stored, i.e. "message" left. The
Result of the call is stored, i.e. "CO". I would like to cfreate a report
that will look like this:

Called ON Calls Msg CO
9/19/2005 120 50 10
9/18/2005 15 12 1
9/16/2005 90 60 4

1) Called On is stored in Called_On.
2) Calls should be the sum of all of the calls on the date, e.g. the
9/19/2005.
3) Msg should be the sum of all calls on the date which have a
"Call_Result" = "Message".
4) CO should be the sum of all calls on the date which have a "Call_Result"
= "CO".

There was some help provided in a thread with the subject: Date Summary
Report, but the help dropped off. Peprhaps you could check out this thread
and resolve the problem.

I'm in a pinch and could use a solution. Any help is much appreciated.

 

[John Spencer (MVP)]

SQL Statement should look something like:

SELECT Called_ON, Count(Called-On) as Calls,
Abs(Sum(Call_Result="Message")) as Msg,
Abs(Sum(Call_Result="CO")) as CO
FROM TblCandidates
GROUP BY Called_On

If you only use the grid to make your queries, then make a select query with
your table
SELECT Totals from the View Menu
Add called_on to the grid twice
Select GROUP BY under the first occurence, Count under the second occurence
Enter MSG: Abs(Sum(Call_Result="Message")) as a new field
Enter CO: Abs(Sum(Call_Result="CO")) as another new field
Select expression under both these fields.

 

[Guest]

Bingo.

Thanks.

SQL Statement should look something like:

SELECT Called_ON, Count(Called-On) as Calls,
Abs(Sum(Call_Result="Message")) as Msg,
Abs(Sum(Call_Result="CO")) as CO
FROM TblCandidates
GROUP BY Called_On

If you only use the grid to make your queries, then make a select query with
your table
SELECT Totals from the View Menu
Add called_on to the grid twice
Select GROUP BY under the first occurence, Count under the second occurence
Enter MSG: Abs(Sum(Call_Result="Message")) as a new field
Enter CO: Abs(Sum(Call_Result="CO")) as another new field
Select expression under both these fields.