Count

[Guest]

I have a huge question.

I have a query that right now only shows the true to a check box.
That checkbox represents months on the year.
I have the months of the year set out to 1-12

I need to count how many months show up for each provider.
Ex. If 1 2 3 4 are for Joe blow then the total number would be 4.
I hope this makes sense. I don't know where on my report to put this.
If there is a way of eliminating the month idetifiers that would be great.
If I could just count the months I would prefer that.

Thanks

 

[Guest]

Your problem really stems from an incorrect table design I'm afraid. What
you are doing by having separate Boolean (Yes/No) columns for each month is
what's known in the trade as 'encoding data as column headings'. Data should
only be stored as values at column positions in rows in tables. Ideally
therefore you should decompose the table into three tables, the current one
without the Months columns, a Months table and a third which models the
many-to-many relationship between them by having two columns referencing the
primary keys of the other two.

You can torture your present design into giving you the result, however.
What you need to do is use a computed control in your report which adds the
return value of IIF function calls for each Boolean column, e.g.

= IIf([Month1],1,0) + IIf([Month2],1,0) <and so on to> + IIf([Month12],1,0)

Nevertheless I'd strongly recommend that you consider decomposing the table.