Counting different occurances in a field

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

Guest

If I had a field name "store #" (in table this listed as a number for sorting
purposes). Each record would have a non-unique store number assigned to it.

Store # Need
1 Box
20 Triangle
1 Square
456 Square
20 Box
20 Triangle
355 Circle

Need to express in my report how many stores I have (4 stores - #1, 20, 355
and 456). Can someone help me with an expression to show the total # of
stores?
 
Access said:
If I had a field name "store #" (in table this listed as a number for sorting
purposes). Each record would have a non-unique store number assigned to it.

Store # Need
1 Box
20 Triangle
1 Square
456 Square
20 Box
20 Triangle
355 Circle

Need to express in my report how many stores I have (4 stores - #1, 20, 355
and 456). Can someone help me with an expression to show the total # of
stores?
Click to expand...


Normally this kind of thing is done in a query like:
SELECT Count(*) As CountOfStores
FROM (SELECT DISTINCT [Store #] FROM table)

If you have a report that groups on the store number, then
you can count the groups by using a text box in the group
header/footer with the expression =1 and RunningSum set to
Over All
 
When I did what you said (or so I think I did) I get a count like a subtotal
throughout my report. I just need the grand total of occurances in the
report header/footer - any ideas? Maybe I didn't follow your instructions
properly. Do you know anything about Dcount of Ecount? Would that help me?

Marshall Barton said:
Access said:
If I had a field name "store #" (in table this listed as a number for sorting
purposes). Each record would have a non-unique store number assigned to it.

Store # Need
1 Box
20 Triangle
1 Square
456 Square
20 Box
20 Triangle
355 Circle

Need to express in my report how many stores I have (4 stores - #1, 20, 355
and 456). Can someone help me with an expression to show the total # of
stores?
Click to expand...


Normally this kind of thing is done in a query like:
SELECT Count(*) As CountOfStores
FROM (SELECT DISTINCT [Store #] FROM table)

If you have a report that groups on the store number, then
you can count the groups by using a text box in the group
header/footer with the expression =1 and RunningSum set to
Over All
Click to expand...
 
You might have done one of the things I said, but you forgot
to do the things I didn't say ;-)

Assuming you are grouping on the store field. A running sum
text box (named txtStoreCnt) with expression =1 in either
the group header or footer section will look like a store
counter. Make this text box invisible so it doesn't get in
the way.

To display the total count in the report footer, add a text
box with the expression =txtStoreCnt
--
Marsh
MVP [MS Access]


Access said:
When I did what you said (or so I think I did) I get a count like a subtotal
throughout my report. I just need the grand total of occurances in the
report header/footer - any ideas? Maybe I didn't follow your instructions
properly. Do you know anything about Dcount of Ecount? Would that help me?

Marshall Barton said:
Access said:
If I had a field name "store #" (in table this listed as a number for sorting
purposes). Each record would have a non-unique store number assigned to it.

Store # Need
1 Box
20 Triangle
1 Square
456 Square
20 Box
20 Triangle
355 Circle

Need to express in my report how many stores I have (4 stores - #1, 20, 355
and 456). Can someone help me with an expression to show the total # of
stores?
Click to expand...


Normally this kind of thing is done in a query like:
SELECT Count(*) As CountOfStores
FROM (SELECT DISTINCT [Store #] FROM table)

If you have a report that groups on the store number, then
you can count the groups by using a text box in the group
header/footer with the expression =1 and RunningSum set to
Over All
Click to expand...
Click to expand...
 
SO CLOSE!! One problem (OK - two) 1. I have a record with an empty field
which I do not want to count - how can I get rid of that?

If I make the =1 invisible in the group header, it still leaves a space
(which does not look good with my design - even the slightest space is
inaapropriate) can make the entire group header disappear?

Marshall Barton said:
You might have done one of the things I said, but you forgot
to do the things I didn't say ;-)

Assuming you are grouping on the store field. A running sum
text box (named txtStoreCnt) with expression =1 in either
the group header or footer section will look like a store
counter. Make this text box invisible so it doesn't get in
the way.

To display the total count in the report footer, add a text
box with the expression =txtStoreCnt
--
Marsh
MVP [MS Access]


Access said:
When I did what you said (or so I think I did) I get a count like a subtotal
throughout my report. I just need the grand total of occurances in the
report header/footer - any ideas? Maybe I didn't follow your instructions
properly. Do you know anything about Dcount of Ecount? Would that help me?

Marshall Barton said:
Access Ignoramoose wrote:

If I had a field name "store #" (in table this listed as a number for sorting
purposes). Each record would have a non-unique store number assigned to it.

Store # Need
1 Box
20 Triangle
1 Square
456 Square
20 Box
20 Triangle
355 Circle

Need to express in my report how many stores I have (4 stores - #1, 20, 355
and 456). Can someone help me with an expression to show the total # of
stores?


Normally this kind of thing is done in a query like:
SELECT Count(*) As CountOfStores
FROM (SELECT DISTINCT [Store #] FROM table)

If you have a report that groups on the store number, then
you can count the groups by using a text box in the group
header/footer with the expression =1 and RunningSum set to
Over All
Click to expand...
Click to expand...
Click to expand...
 
Which field is Null? Where is it in the report?
If it's the store number field, then it seems like you have
a garbage record that should either be deleted or filtered
out of the report's record source query. If you really need
the store with no number, then change the running sum text
box expression from =1 to =Iff([store #] Is Null, 0, 1)

To get rid of the space used by the invisible text box, move
it somewhere out of the way and reduce the section's height.
 
Back
Top