Counting in a Crosstab Query

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

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

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

 

[Guest]

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

--
Duane Hookom
Microsoft Access MVP

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.

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

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