Count number of columns in a row where value is "Green"

[DrSmyth]

Hi, I have a table called Tbl1. This table has a date and 9 other columns. For each of these other columns the value can be Red/Amber or Green.

I need to be able to count the number of Green, Amber and Red occuring on each date.

Anybody know of a solution?

 

[SkipVought]

The problem is that you table is not normalized. Columns 2 - 9 have similar data.

The way I'd do it is with UNION queries

query col2
UNION
query col3
UNION
...
query col9


each query would count each color

select
iif(col2="red",1,0) as RecCnt
, iif(col2="amber",1,0) as AmbCnt
, iif(col2="green",1,0) as GrnCnt

...


Skip,

_[red][/red]

 

[DrSmyth]

Cheers Skip, thanks for the input, Trouble is that I'm outputting the data via ODBC into an excel spreadsheet and I've never been able to get a union query to work in this situation (sorry I should have mentioned this earlier as it's pretty integral to my problem).

However, I've managed to come up with a simple solution by adding together a bunch of iif statements:

iif(col1="Green",1,0)+iif(Col2="Green",1,0) etc etc

seems to work ok.

The reason for the non normalised data structure is down to the speed at which i've had to construct the database. It has meant that i haven't had chance to build any forms (i've used access data input sheets instead). Funnily enough this has actually worked in my favour as the users are used to inputting into an excel spreadsheet so the data input form looks the same as this.

Thanks again for your input

 

[SkipVought]

I return UNION query resultsets without any problem.

Skip,

_[red][/red]

 

[MichaelRed]

there are a;so several to numerous threads in these (Tek-Tips) fora which show hopw to (easily?) do this with a small UDF, although mayhap not specifically as a count of. use search / advanced search with key word(s) suitable to the occassion. failing that exercise, see the following:

Public Function basCountOf(varCountof As Variant, ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/12/2006
    'To return the count or a series of values which are equal to an argument (varCountOf)

    'Sample Usage" _
    ? basCountOf("Green", "REd", "Blue", "green", "Green", "yellow", , "GrEeN", "Green") _
      4

    Dim Idx As Integer
    Dim MyVal As Variant

    For Idx = 0 To UBound(varMyVals())

        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If

        If (varMyVals(Idx) = varCountof) Then
            MyVal = MyVal + 1
        End If

NextVal:
    Next Idx

    basCountOf = MyVal

End Function

MichaelRed

 

[DrSmyth]

Skip.... Just tried bringing a union query into excel with msquery, and your right it does work... Not sure why i thought that you couldn't... Thanks for the tip and I can only apologise for doubting you...

Michael, cheers for the tip, I'll consider this option when i come across this situation again.. I've gone and written the if statements now so I won't change the database to accomodate you're apparently more flexible solution... But thanks again..

Andy