count number of true conditions for 5 fields in a table 1

[demills]

Hi All -

I have a table with 5 true/false fields. I need to calculate totals for each possible combination of true/false for each of these fields. According to my calculations that'd be 128 different queries.

tffff
ftfff
fftff
ffftf
fffft
ttfff
etc.

Does anyone have some code that would allow me to count the number of occurencies in the table?

 

[jebry]

Hi!

You can build one query like this:

Query1

Select IDField, Field1 + Field2*2 + Field3*4 + Field4*8 + Field5*16 As FieldCombo From YourTable

Then

Query2

Select FieldCombo, Count(IDField) From Query1 Group By FieldCombo

The first query will give you a different number for each possible combinations of the true/false fields and assign the correct number to each record and the second query will count the number of records with each combination.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[demills]

Thanks Jebry

It worked perfectly and saved me hours of work.

 

[MichaelRed]

Public Function basRowSum(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/20/2003 (again!)
    'To return the Sum or a series of values

    Dim Idx As Integer
    Dim MyVal As Variant

    For Idx = 0 To UBound(varMyVals())

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

        If (IsNumeric(varMyVals(Idx))) Then
            MyVal = MyVal + varMyVals(Idx)
        End If

NextVal:
    Next Idx

    basRowSum = MyVal

End Function
[code]

of course, to really use this, the fields would need to actually be BOOLEAN (t/f) data type
and the return val would need to  be "wrapped" in an ABS function.

On the other hand, it works (as is) for any number of NUMERIC fields without modification.




MichaelRed