Date Summary Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

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".

Any help is much appreciated.
 
Hi, Rod.
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
Click to expand...

In your query's SQL View pane, try:

SELECT Called_On, COUNT(Called_On) AS [#Calls],
(SELECT COUNT(Call_Result)
FROM tblCandidates AS T
WHERE ((Call_Result = 'message') AND (T.Called_On = C.Called_On))) AS
[#Msg],
(SELECT COUNT(Call_Result)
FROM tblCandidates AS T
WHERE ((Call_Result = 'CO') AND (T.Called_On = C.Called_On))) AS [#CO]
FROM tblCandidates AS C
GROUP BY Called_On
ORDER BY Called_On DESC;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
The adapted SQL looks like this:
SELECT tblCandidates.CALLED_ON, Count(tblCandidates.CALLED_ON) AS [#Calls],
(SELECT COUNT(Call_Results)
FROM tblCandidates AS T
WHERE ((Call_Result = 'message') AND (T.Called_On = C.Called_On))) AS
[#Msg], (SELECT COUNT(Call_Results)
FROM tblCandidates AS T
WHERE ((Call_Result = 'CO') AND (T.Called_On = C.Called_On))) AS [#CO]
FROM tblCandidates AS C, tblCandidates
GROUP BY tblCandidates.CALLED_ON
ORDER BY tblCandidates.CALLED_ON DESC;

and produces the error:
"You tried to execute a query that does not include the specified expression
'CALLED_ON' as part of the aggregate function."
then terminates the query.

'69 Camaro said:
Hi, Rod.
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
Click to expand...

In your query's SQL View pane, try:

SELECT Called_On, COUNT(Called_On) AS [#Calls],
(SELECT COUNT(Call_Result)
FROM tblCandidates AS T
WHERE ((Call_Result = 'message') AND (T.Called_On = C.Called_On))) AS
[#Msg],
(SELECT COUNT(Call_Result)
FROM tblCandidates AS T
WHERE ((Call_Result = 'CO') AND (T.Called_On = C.Called_On))) AS [#CO]
FROM tblCandidates AS C
GROUP BY Called_On
ORDER BY Called_On DESC;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Rod said:
Hello,

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".

Any help is much appreciated.
Click to expand...
Click to expand...
 
Hi, Rod.
The adapted SQL looks like this: . . .
and produces the error:
Click to expand...

Well, once that error is fixed, you'll see the other errors that your
adapted SQL produces, too. It appears that you are attempting to create a
Cartesian join with the query I suggested and the tblCandidates table, along
with an additional field named "Call_Results" (not "Call_Result," which you
named in your question). Is this your intent? We need to make some changes
if this is the case.

The query I suggested will produce the results you requested as long as the
table is normalized. Is the table not normalized? Is there something else
you would like to do with your query to produce different results than
originally requested? Please explain in further detail what results you'd
like to see, and I'd be happy to help you if I can.

As an aside, I will always recommend using only alphanumeric characters and
the underscore character when naming any table, field, query, object,
variable, or procedure in order to avoid chasing bugs. I suggest that you
not use the # sign as part of the name of the column, but to use this in the
control's Caption Property when it is displayed on a form or report. For
example:

Me!lblNumMsgs.Caption = "#Msg"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Rod said:
The adapted SQL looks like this:
SELECT tblCandidates.CALLED_ON, Count(tblCandidates.CALLED_ON) AS [#Calls],
(SELECT COUNT(Call_Results)
FROM tblCandidates AS T
WHERE ((Call_Result = 'message') AND (T.Called_On = C.Called_On))) AS
[#Msg], (SELECT COUNT(Call_Results)
FROM tblCandidates AS T
WHERE ((Call_Result = 'CO') AND (T.Called_On = C.Called_On))) AS [#CO]
FROM tblCandidates AS C, tblCandidates
GROUP BY tblCandidates.CALLED_ON
ORDER BY tblCandidates.CALLED_ON DESC;

and produces the error:
"You tried to execute a query that does not include the specified expression
'CALLED_ON' as part of the aggregate function."
then terminates the query.

'69 Camaro said:
Hi, Rod.
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
Click to expand...

In your query's SQL View pane, try:

SELECT Called_On, COUNT(Called_On) AS [#Calls],
(SELECT COUNT(Call_Result)
FROM tblCandidates AS T
WHERE ((Call_Result = 'message') AND (T.Called_On = C.Called_On))) AS
[#Msg],
(SELECT COUNT(Call_Result)
FROM tblCandidates AS T
WHERE ((Call_Result = 'CO') AND (T.Called_On = C.Called_On))) AS [#CO]
FROM tblCandidates AS C
GROUP BY Called_On
ORDER BY Called_On DESC;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Rod said:
Hello,

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".

Any help is much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
I am not aware of a Cartesian join (I'll have to research that). The actual
names are tblCandidates and Call_Results (I inadvertantly left out the "s").
Call_Results is a combo box which has "Message", "Corp Overview" and others
as choices. Does this create a problem? The col titled # Calls (or, if you
like, Number of Calls) should simply tell me how many calls were made on the
corresponding date under Called_On, e.g., answer the question how many calls
were made on 9/19/2005. So a report line could give the following
information:

On 9/19/2005, 120 calls were made, 50 messages were left, and 10 candidates
were scheduled for the Corp Overview.

Thanks.

'69 Camaro said:
Hi, Rod.
The adapted SQL looks like this: . . .
and produces the error:
Click to expand...

Well, once that error is fixed, you'll see the other errors that your
adapted SQL produces, too. It appears that you are attempting to create a
Cartesian join with the query I suggested and the tblCandidates table, along
with an additional field named "Call_Results" (not "Call_Result," which you
named in your question). Is this your intent? We need to make some changes
if this is the case.

The query I suggested will produce the results you requested as long as the
table is normalized. Is the table not normalized? Is there something else
you would like to do with your query to produce different results than
originally requested? Please explain in further detail what results you'd
like to see, and I'd be happy to help you if I can.

As an aside, I will always recommend using only alphanumeric characters and
the underscore character when naming any table, field, query, object,
variable, or procedure in order to avoid chasing bugs. I suggest that you
not use the # sign as part of the name of the column, but to use this in the
control's Caption Property when it is displayed on a form or report. For
example:

Me!lblNumMsgs.Caption = "#Msg"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Rod said:
The adapted SQL looks like this:
SELECT tblCandidates.CALLED_ON, Count(tblCandidates.CALLED_ON) AS [#Calls],
(SELECT COUNT(Call_Results)
FROM tblCandidates AS T
WHERE ((Call_Result = 'message') AND (T.Called_On = C.Called_On))) AS
[#Msg], (SELECT COUNT(Call_Results)
FROM tblCandidates AS T
WHERE ((Call_Result = 'CO') AND (T.Called_On = C.Called_On))) AS [#CO]
FROM tblCandidates AS C, tblCandidates
GROUP BY tblCandidates.CALLED_ON
ORDER BY tblCandidates.CALLED_ON DESC;

and produces the error:
"You tried to execute a query that does not include the specified expression
'CALLED_ON' as part of the aggregate function."
then terminates the query.

'69 Camaro said:
Hi, Rod.

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

In your query's SQL View pane, try:

SELECT Called_On, COUNT(Called_On) AS [#Calls],
(SELECT COUNT(Call_Result)
FROM tblCandidates AS T
WHERE ((Call_Result = 'message') AND (T.Called_On = C.Called_On))) AS
[#Msg],
(SELECT COUNT(Call_Result)
FROM tblCandidates AS T
WHERE ((Call_Result = 'CO') AND (T.Called_On = C.Called_On))) AS [#CO]
FROM tblCandidates AS C
GROUP BY Called_On
ORDER BY Called_On DESC;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hello,

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".

Any help is much appreciated.
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Summary Report 2
Can You Figure Out What's Wrong W/ This Query 4
Simple Update Q Problem 1
AND Problem 7
Query ask two questions 1
add a value to a date 8
Drop Dead: Dual Strike Edition for the Oculus Quest 0
Creating summary records 4

Back
Top