Criteria Count Reports

[Andy]

I have a report based on a table with multiple fields. Within this report I
need to construct some counts based on data within the table. I can do this
for one field, but applying the filter criteria 'x' for counts in Field A,
then obviously affects the counts for Field B as it is then running Field B's
counts where x is in Field A.

How can I get purely independent counts with critierias for each field? The
only way it seems possible is to create a duff primary key on multiple output
tables and then link them all back together in relationships...

HELP!

 

[Allen Browne]

If you want a summary at the end of the report (not in group footers
scattered throughout the report), it might be easiest to use a subreport.

If the values you want do count are few and fixed, it may be possible to use
a series of text boxes, each with a Control Source like this:
=-Sum([Field1]="A")
or:
=Sum(IIf([Field1]="B", 1, 0)

 

[Andy]

Thanks. Using the Sum function, I get a negative count, but can fix that.
When using the subreport and putting the function in the detail, I get a long
list of my counts repeated, presumably I should put this 'Sum' box in the
header of the subreport?

If you want a summary at the end of the report (not in group footers
scattered throughout the report), it might be easiest to use a subreport.

If the values you want do count are few and fixed, it may be possible to use
a series of text boxes, each with a Control Source like this:
=-Sum([Field1]="A")
or:
=Sum(IIf([Field1]="B", 1, 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report based on a table with multiple fields. Within this report I
need to construct some counts based on data within the table. I can do
this
for one field, but applying the filter criteria 'x' for counts in Field A,
then obviously affects the counts for Field B as it is then running Field
B's
counts where x is in Field A.

How can I get purely independent counts with critierias for each field?
The
only way it seems possible is to create a duff primary key on multiple
output
tables and then link them all back together in relationships...

HELP!

 

[Allen Browne]

Either that, or base the subreport on a Totals query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. Using the Sum function, I get a negative count, but can fix that.
When using the subreport and putting the function in the detail, I get a
long
list of my counts repeated, presumably I should put this 'Sum' box in the
header of the subreport?

If you want a summary at the end of the report (not in group footers
scattered throughout the report), it might be easiest to use a subreport.

If the values you want do count are few and fixed, it may be possible to
use
a series of text boxes, each with a Control Source like this:
=-Sum([Field1]="A")
or:
=Sum(IIf([Field1]="B", 1, 0)

I have a report based on a table with multiple fields. Within this
report I
need to construct some counts based on data within the table. I can do
this
for one field, but applying the filter criteria 'x' for counts in Field
A,
then obviously affects the counts for Field B as it is then running
Field
B's
counts where x is in Field A.

How can I get purely independent counts with critierias for each field?
The
only way it seems possible is to create a duff primary key on multiple
output
tables and then link them all back together in relationships...

 

[Andy]

Thanks, am sure I can get this to work now. Could you recommend any good
web-sites for report, sub-report. counts/totals so I can review?

Cheers

Either that, or base the subreport on a Totals query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. Using the Sum function, I get a negative count, but can fix that.
When using the subreport and putting the function in the detail, I get a
long
list of my counts repeated, presumably I should put this 'Sum' box in the
header of the subreport?

If you want a summary at the end of the report (not in group footers
scattered throughout the report), it might be easiest to use a subreport.

If the values you want do count are few and fixed, it may be possible to
use
a series of text boxes, each with a Control Source like this:
=-Sum([Field1]="A")
or:
=Sum(IIf([Field1]="B", 1, 0)

I have a report based on a table with multiple fields. Within this
report I
need to construct some counts based on data within the table. I can do
this
for one field, but applying the filter criteria 'x' for counts in Field
A,
then obviously affects the counts for Field B as it is then running
Field
B's
counts where x is in Field A.

How can I get purely independent counts with critierias for each field?
The
only way it seems possible is to create a duff primary key on multiple
output
tables and then link them all back together in relationships...

 

[Allen Browne]

Just depress the Totals button on the toolbar in query design view.
(It's an upper sigma icon.)

Group by the fields you wish, and Count or Sum the others.

 

[Eric D. Braden]

Just depress the Totals button on the toolbar in query design view.
(It's an upper sigma icon.)

Group by the fields you wish, andCountor Sum the others.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.






- Show quoted text -

Is it possible to get multiple counts in one query?

What I am trying to do:

I've got 3 queries-
a) 3days or less
b) 4 days
c) 5 days or more

If I put one of those queries in a new one, then put Count in Total,
it's accurate. If I put all three queries into a new one and use
Count on each Total, I get the exact same value for all three. So
far, when I need counts, I make a query to filter down my records,
then a seperate query that does nothing but count the number of
results in the previous query. I have a seperate "count" query for
every single query that needs to be counted.

Is it possible to consolidate them all into one master count query?
(I currently have a "master count" query, but it's just a collection
of all the previous count queries for later reference)

 

[Allen Browne]

If you want mulitples like that in one query, use a subquery to get the
other 2 counts.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html