# Calculating all possible combinations

1. Novice
Windows XP Access 2003
Join Date
Jun 2011
Posts
2

## Calculating all possible combinations

Problem: I have 6 different fields that use Y/N data. I want to determine how many records have 2 Y's in these 6 fields using a single query instead of running queries for each of the possible combinations.

Example:

Running a single query for each possibility would look like this:

Query (1)
A B C D E F
Y Y N N N N

Query (2)
A B C D E F
Y N Y N N N

and so on...

So I need a query that can run all these possibilities at once instead of doing each of them separately.

It would be of great help if someone could tell me how to do this. Thanks.

2. Hi

I haven't worked out how to use a query to get the required result, but I can offer you the following function that returns the number of records that have 2 fields marked as Yes/True.
Code:

Public Function fnNumOf2Yeses() As Long
On Error GoTo Err_fnNumOf2Yeses_Error
Dim db As Database
Dim rec As Recordset
Dim fld As Field
Dim i As Integer
Set db = CurrentDb
Set rec = db.OpenRecordset("YourYesNoTable")
With rec
Do Until .EOF
i = 0
For Each fld In .Fields
If fld.Value = True Then
i = i + 1
End If
Next
If i = 2 Then
fnNumOf2Yeses = fnNumOf2Yeses + 1
End If
.MoveNext
Loop
End With

'MsgBox fnNumOf2Yeses
Exit_ErrorHandler:
rec.Close
Set rec = Nothing
Set db = Nothing
Exit Function
Err_fnNumOf2Yeses_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") "
Resume Exit_ErrorHandler
End Function

3. Novice
Windows XP Access 2003
Join Date
Jun 2011
Posts
2
Where would I enter this function and which part of the function do I place my field names?

4. Hi

In the following line you need to change the Red Text to the name of your table.
Set rec = db.OpenRecordset("YourYesNoTable")

Put the function in a module.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums