Counting in a Crosstab Query

R

Rod

I have:
TRANSFORM Count(tblCandidates.CALL_RESULTS) AS CountOfCALL_RESULTS
SELECT tblCandidates.CALLED_ON
FROM tblCandidates, [tblDaily Blast]
WHERE (((tblCandidates.CALLED_ON)>=Date()-35 And
(tblCandidates.CALLED_ON)<=Date()))
GROUP BY tblCandidates.CALLED_ON, tblCandidates.RETURNED_CALL
ORDER BY tblCandidates.CALLED_ON DESC
PIVOT tblCandidates.CALL_RESULTS;

I am looking to count the total calls made on the particular day. The total
calls is the sum of all "Corp Overview", "Message", "NI, At This Time", Not
Interested", "Thinking It Over", and "Will Call back" - basically anything
other than "-" and "Add to DNC".

How can I get this daily number so I can display it in a "Call" column in a
report? The idea is:

CALLED ON CALLS
Thu, Sep 13 07 151
Wed, Sep 12 07 137

I will adapt the same solution to populate other colums I need to display.

Thanks
 
D

Duane Hookom

Do you have a table of unique values of CALL_RESULTS? Add a field to this
table that identifies IsCall. Add this table to the crosstab so that you can
count the records where the IsCall field is true (or whatever). This create a
new Row Heading column.
 
R

Rod

Record has a field named CALLED_ON. This field stores what date the call was
made. The idea is to count the number of calls made on a particular day so i
can display that in a report:

CALLED ON CALLS
Thu, Sep 13 07 151
Wed, Sep 12 07 137

As a beginner to Access how can I do this?

Thanks for your patience and help.

Duane Hookom said:
Do you have a table of unique values of CALL_RESULTS? Add a field to this
table that identifies IsCall. Add this table to the crosstab so that you can
count the records where the IsCall field is true (or whatever). This create a
new Row Heading column.

--
Duane Hookom
Microsoft Access MVP


Rod said:
I have:
TRANSFORM Count(tblCandidates.CALL_RESULTS) AS CountOfCALL_RESULTS
SELECT tblCandidates.CALLED_ON
FROM tblCandidates, [tblDaily Blast]
WHERE (((tblCandidates.CALLED_ON)>=Date()-35 And
(tblCandidates.CALLED_ON)<=Date()))
GROUP BY tblCandidates.CALLED_ON, tblCandidates.RETURNED_CALL
ORDER BY tblCandidates.CALLED_ON DESC
PIVOT tblCandidates.CALL_RESULTS;

I am looking to count the total calls made on the particular day. The total
calls is the sum of all "Corp Overview", "Message", "NI, At This Time", Not
Interested", "Thinking It Over", and "Will Call back" - basically anything
other than "-" and "Add to DNC".

How can I get this daily number so I can display it in a "Call" column in a
report? The idea is:

CALLED ON CALLS
Thu, Sep 13 07 151
Wed, Sep 12 07 137

I will adapt the same solution to populate other colums I need to display.

Thanks
 
D

Duane Hookom

SELECT CALLED_ON, Count(*) as NumCalls
FROM tblCandidates
GROUP BY CALLED_ON;

--
Duane Hookom
Microsoft Access MVP


Rod said:
Record has a field named CALLED_ON. This field stores what date the call was
made. The idea is to count the number of calls made on a particular day so i
can display that in a report:

CALLED ON CALLS
Thu, Sep 13 07 151
Wed, Sep 12 07 137

As a beginner to Access how can I do this?

Thanks for your patience and help.

Duane Hookom said:
Do you have a table of unique values of CALL_RESULTS? Add a field to this
table that identifies IsCall. Add this table to the crosstab so that you can
count the records where the IsCall field is true (or whatever). This create a
new Row Heading column.

--
Duane Hookom
Microsoft Access MVP


Rod said:
I have:
TRANSFORM Count(tblCandidates.CALL_RESULTS) AS CountOfCALL_RESULTS
SELECT tblCandidates.CALLED_ON
FROM tblCandidates, [tblDaily Blast]
WHERE (((tblCandidates.CALLED_ON)>=Date()-35 And
(tblCandidates.CALLED_ON)<=Date()))
GROUP BY tblCandidates.CALLED_ON, tblCandidates.RETURNED_CALL
ORDER BY tblCandidates.CALLED_ON DESC
PIVOT tblCandidates.CALL_RESULTS;

I am looking to count the total calls made on the particular day. The total
calls is the sum of all "Corp Overview", "Message", "NI, At This Time", Not
Interested", "Thinking It Over", and "Will Call back" - basically anything
other than "-" and "Add to DNC".

How can I get this daily number so I can display it in a "Call" column in a
report? The idea is:

CALLED ON CALLS
Thu, Sep 13 07 151
Wed, Sep 12 07 137

I will adapt the same solution to populate other colums I need to display.

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top