Results 1 to 4 of 4

Calculating all possible combinations

  1. #1
    Data is offline 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. #2
    Bob Fitz is online now Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,099
    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
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  3. #3
    Data is offline 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. #4
    Bob Fitz is online now Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,099
    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.
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need a query to figure all possible combinations
    By julestrip in forum Queries
    Replies: 1
    Last Post: 05-27-2011, 07:23 AM
  2. Query for All Possible Combinations
    By Rawb in forum Queries
    Replies: 1
    Last Post: 10-09-2010, 08:33 PM
  3. Multi-Query List Box Combinations?
    By BizIntelGuy in forum Access
    Replies: 3
    Last Post: 07-20-2010, 03:20 PM
  4. Replies: 5
    Last Post: 05-26-2010, 07:13 AM
  5. ms access version combinations
    By marianne in forum Access
    Replies: 1
    Last Post: 08-05-2009, 07:37 AM

Posting Permissions

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