Counting Types of fields in a Report

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

Guest

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?
 
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?
Click to expand...

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?
 
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

fredg said:
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?
Click to expand...

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?
Click to expand...
 
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)

Access Ignoramoose said:
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

fredg said:
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?
Click to expand...

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?
Click to expand...
Click to expand...
 
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?
Click to expand...


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.
 
Back
Top