SUM of COUNT?

  • Thread starter Thread starter PeterW
  • Start date Start date
P

PeterW

I have a report based on a crosstab query that counts the number of enquiries
from an advertising source for a year and sorts them into months
So columns are by date and rows give the source and the value is the count
of enquries in the source.
I want to total the number of enquiries in the month on the bottom of the
report. I have tried putting a text box in the report footer that uses the
value field and set it's running sum property to over all but it just gives
the sum for the source and not all the sources added together.
Is it possible to do this?
 
Peter

One of the folks here more experience with crosstabs may have a way to do
that within Access.

Here's an alternate approach, if you only need an answer, not an elegant
solution...<g>

Export the crosstab query to Excel, and use what Excel does well, summing up
columns.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Thank You
I thought that might be a way to do it - I will try and set up a spreadsheet
using msquery to see if I can automate it
 
You can use a totals query in a union to feed the crosstab.

If you need more explaination then post your crosstab SQL.
 
Not completely clear to me what you want.
Do you want a grand total of the count for each month. Then Add a control in
the report footer and set its source to whatever the field name is

= Sum([MonthofMay])

If you want a total for all months (the year) then I would modify the crosstab
to give you a row count that you could sum.

TRANSFORM Count(Enquiry)
SELECT Source, Count(Enquiry) as RowCount
FROM SomeTable
GROUP BY Source
PIVOT Format(SomeDate,"mmm") In ("Jan","Feb",...,"Dec")

Then your grand Total would be
Sum(Rowcount)



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
The sql for my query is as follows

TRANSFORM Count(QryRequestsInDate.Source) AS CountOfSource
SELECT tblSource.Source
FROM tblSource LEFT JOIN QryRequestsInDate ON tblSource.Source =
QryRequestsInDate.Source
GROUP BY tblSource.Source
ORDER BY tblSource.Source
PIVOT Format(QryRequestsInDate.DateRequested,"mmm yyyy");

This gives me a crosstab that gets the summaries by each source by month and
then I was trying to get the total of all enquries by month - I was trying to
do this in the report footer by adding a text box that had =Sum(Jan 2009) in
it but this just gives me a syntax error message

John Spencer said:
Not completely clear to me what you want.
Do you want a grand total of the count for each month. Then Add a control in
the report footer and set its source to whatever the field name is

= Sum([MonthofMay])

If you want a total for all months (the year) then I would modify the crosstab
to give you a row count that you could sum.

TRANSFORM Count(Enquiry)
SELECT Source, Count(Enquiry) as RowCount
FROM SomeTable
GROUP BY Source
PIVOT Format(SomeDate,"mmm") In ("Jan","Feb",...,"Dec")

Then your grand Total would be
Sum(Rowcount)



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a report based on a crosstab query that counts the number of enquiries
from an advertising source for a year and sorts them into months
So columns are by date and rows give the source and the value is the count
of enquries in the source.
I want to total the number of enquiries in the month on the bottom of the
report. I have tried putting a text box in the report footer that uses the
value field and set it's running sum property to over all but it just gives
the sum for the source and not all the sources added together.
Is it possible to do this?
Click to expand...
.
Click to expand...
 
Your requirement need the union like this --
qrySourceTotal --
SELECT tblSource.Source, QryRequestsInDate.DateRequested
FROM tblSource LEFT JOIN QryRequestsInDate ON tblSource.Source =
QryRequestsInDate.Source
UNION ALL "Total" AS Source, QryRequestsInDate.DateRequested
FROM tblSource LEFT JOIN QryRequestsInDate ON tblSource.Source =
QryRequestsInDate.Source;

TRANSFORM Count(qrySourceTotal.Source) AS CountOfSource
SELECT tblSource.Source
FROM qrySourceTotal
GROUP BY qrySourceTotal.Source
ORDER BY qrySourceTotal.Source
PIVOT Format(qrySourceTotal.DateRequested,"mmm yyyy");

You may need someting other than "Total" to make it fallout as the last item
as Source.
Alternative --
qrySourceTotal --
SELECT tblSource.Source, 0 AS [Sort], QryRequestsInDate.DateRequested
FROM tblSource LEFT JOIN QryRequestsInDate ON tblSource.Source =
QryRequestsInDate.Source
UNION ALL "Total" AS Source, 1 AS [Sort], QryRequestsInDate.DateRequested
FROM tblSource LEFT JOIN QryRequestsInDate ON tblSource.Source =
QryRequestsInDate.Source;

TRANSFORM Count(qrySourceTotal.Source) AS CountOfSource
SELECT tblSource.Source
FROM qrySourceTotal
GROUP BY [Sort], qrySourceTotal.Source
ORDER BY [Sort], qrySourceTotal.Source
PIVOT Format(qrySourceTotal.DateRequested,"mmm yyyy");

--
Build a little, test a little.


PeterW said:
The sql for my query is as follows

TRANSFORM Count(QryRequestsInDate.Source) AS CountOfSource
SELECT tblSource.Source
FROM tblSource LEFT JOIN QryRequestsInDate ON tblSource.Source =
QryRequestsInDate.Source
GROUP BY tblSource.Source
ORDER BY tblSource.Source
PIVOT Format(QryRequestsInDate.DateRequested,"mmm yyyy");

This gives me a crosstab that gets the summaries by each source by month and
then I was trying to get the total of all enquries by month - I was trying to
do this in the report footer by adding a text box that had =Sum(Jan 2009) in
it but this just gives me a syntax error message

John Spencer said:
Not completely clear to me what you want.
Do you want a grand total of the count for each month. Then Add a control in
the report footer and set its source to whatever the field name is

= Sum([MonthofMay])

If you want a total for all months (the year) then I would modify the crosstab
to give you a row count that you could sum.

TRANSFORM Count(Enquiry)
SELECT Source, Count(Enquiry) as RowCount
FROM SomeTable
GROUP BY Source
PIVOT Format(SomeDate,"mmm") In ("Jan","Feb",...,"Dec")

Then your grand Total would be
Sum(Rowcount)



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a report based on a crosstab query that counts the number of enquiries
from an advertising source for a year and sorts them into months
So columns are by date and rows give the source and the value is the count
of enquries in the source.
I want to total the number of enquiries in the month on the bottom of the
report. I have tried putting a text box in the report footer that uses the
value field and set it's running sum property to over all but it just gives
the sum for the source and not all the sources added together.
Is it possible to do this?
Click to expand...
.
Click to expand...
Click to expand...
 
You would need to enclose the field name with []s since there is a space in
Jan 2009.

Personnally, I would use the dynamic solution I suggested earlier so you
don't have to ever modify the report for new months and years. It can always
display a range of months with the proper column labels.

--
Duane Hookom
Microsoft Access MVP


PeterW said:
The sql for my query is as follows

TRANSFORM Count(QryRequestsInDate.Source) AS CountOfSource
SELECT tblSource.Source
FROM tblSource LEFT JOIN QryRequestsInDate ON tblSource.Source =
QryRequestsInDate.Source
GROUP BY tblSource.Source
ORDER BY tblSource.Source
PIVOT Format(QryRequestsInDate.DateRequested,"mmm yyyy");

This gives me a crosstab that gets the summaries by each source by month and
then I was trying to get the total of all enquries by month - I was trying to
do this in the report footer by adding a text box that had =Sum(Jan 2009) in
it but this just gives me a syntax error message

John Spencer said:
Not completely clear to me what you want.
Do you want a grand total of the count for each month. Then Add a control in
the report footer and set its source to whatever the field name is

= Sum([MonthofMay])

If you want a total for all months (the year) then I would modify the crosstab
to give you a row count that you could sum.

TRANSFORM Count(Enquiry)
SELECT Source, Count(Enquiry) as RowCount
FROM SomeTable
GROUP BY Source
PIVOT Format(SomeDate,"mmm") In ("Jan","Feb",...,"Dec")

Then your grand Total would be
Sum(Rowcount)



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a report based on a crosstab query that counts the number of enquiries
from an advertising source for a year and sorts them into months
So columns are by date and rows give the source and the value is the count
of enquries in the source.
I want to total the number of enquiries in the month on the bottom of the
report. I have tried putting a text box in the report footer that uses the
value field and set it's running sum property to over all but it just gives
the sum for the source and not all the sources added together.
Is it possible to do this?
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top