Counting Types of fields in a Report

[Access Ignoramoose]

For example, I have a field named "colors" and I have 6 records - blue, red,
blue, blue, green, red in that field. I would like to put an expression on
my report to count how many are blue (3), red (2) and green (1) colors
without sorting and subtotaling them. Also, if I had a record that said
"newred" I would also like to count it with the red grouping. Can anyone
help me with the correct syntax expression?

 

[fredg]

For example, I have a field named "colors" and I have 6 records - blue, red,
blue, blue, green, red in that field. I would like to put an expression on
my report to count how many are blue (3), red (2) and green (1) colors
without sorting and subtotaling them. Also, if I had a record that said
"newred" I would also like to count it with the red grouping. Can anyone
help me with the correct syntax expression?

Add an unbound control to the report.
Set it's control source to:
=Sum(IIf([Colors] Like "*red*",1,0))

Do the same for each additional possible color you wish to count.
How will you count a color value such as BlueGreen, or Reddish Brown?

 

[Access Ignoramoose]

Thank you so much - that worked perfectly!! The BlueGreen value would not
apply to my report at this time - I REALLY appreciate the tip!! :^D

For example, I have a field named "colors" and I have 6 records - blue, red,
blue, blue, green, red in that field. I would like to put an expression on
my report to count how many are blue (3), red (2) and green (1) colors
without sorting and subtotaling them. Also, if I had a record that said
"newred" I would also like to count it with the red grouping. Can anyone
help me with the correct syntax expression?

Add an unbound control to the report.
Set it's control source to:
=Sum(IIf([Colors] Like "*red*",1,0))

Do the same for each additional possible color you wish to count.
How will you count a color value such as BlueGreen, or Reddish Brown?

 

[Access Ignoramoose]

One more question: can you count how many different colors that you have in a
given field? (as to original example, I would have 3 different colors - red,
blue and green)

Thank you so much - that worked perfectly!! The BlueGreen value would not
apply to my report at this time - I REALLY appreciate the tip!! :^D

For example, I have a field named "colors" and I have 6 records - blue, red,
blue, blue, green, red in that field. I would like to put an expression on
my report to count how many are blue (3), red (2) and green (1) colors
without sorting and subtotaling them. Also, if I had a record that said
"newred" I would also like to count it with the red grouping. Can anyone
help me with the correct syntax expression?

Add an unbound control to the report.
Set it's control source to:
=Sum(IIf([Colors] Like "*red*",1,0))

Do the same for each additional possible color you wish to count.
How will you count a color value such as BlueGreen, or Reddish Brown?

 

[Marshall Barton]

Access Ignoramoose <Access

For example, I have a field named "colors" and I have 6 records - blue, red,
blue, blue, green, red in that field. I would like to put an expression on
my report to count how many are blue (3), red (2) and green (1) colors
without sorting and subtotaling them. Also, if I had a record that said
"newred" I would also like to count it with the red grouping. Can anyone
help me with the correct syntax expression?

This can be a bigger problem than just couning a few values,
especially when you want "newred" to be counted as red.

I think you should seriously consider using a query to do
the counting and then use the query as the basis for a
subreport.

The simple case, without considering the "newred" problem,
would use a query like:

SELECT color, Count(*) As ColorCount
FROM originalreportquery
GROUP BY color

Adding the "newred" issue can get complex depending on how
many and what kinds of variations of eacl color name you
have. Most likely, you will want another table to map color
aliases to the basic color names.