SQL Query if ID in ANY of 4 fields

[drhansenjr]

I have a Collections table that includes four member ID fields and a collection ID field, and a CollectionTypes table with type ID and type name field, both of which are included in a query. I need to pass a member ID to the query and get back a recordset with Collections table ID and collection names where that member ID appears in ANY of the four member ID fields. After fetching that collection recordset, I use the member IDs it returns to get the member's full name using another query (which works fine). I will use the results to populate a listbox. My problem is that when I run the first query, it returns a recordset with a row for each record in the CollectionTypes (and the same four member IDs in each. I know I could accomplish this with four queries and then combine the results, but hope there is a way to do it in one.




Here is my query code...

PARAMETERS [lngCurrentMemberID] Long;
SELECT tblCollections.ID AS COLLID, tblCollections.strPrefix, tblCollections.lngMember1ID,
tblCollections.lngMember2ID, tblCollections.lngMember3ID, tblCollections.lngMember4ID, 
tblCollectionTypes.txtCollectionTypeName AS TYPENAME,tblCollectionTypes.ID, 
tblCollectionTypes.txtCollectionTypeName

FROM tblCollections, tblCollectionTypes

WHERE [lngCurrentMemberID] IN (tblCollections.lngMember1ID, tblCollections.lngMember2ID, 
tblCollections.lngMember3ID, tblCollections.lngMember4ID);

...and the corresponding VB:

    Set qdfGetCurrentMemberCollections = currDB.QueryDefs("qryGetCurrentMemberCollections")
    qdfGetCurrentMemberCollections.Parameters!lngCurrentMemberID = Me.ID
    Set rstGetCurrentMemberCollections = qdfGetCurrentMemberCollections.OpenRecordset()
    If rstGetCurrentMemberCollections.RecordCount > 0 Then 'Iterate through recordset
        If Not (rstGetCurrentMemberCollections.EOF And rstGetCurrentMemberCollections.BOF) Then
            rstGetCurrentMemberCollections.MoveFirst
            Do Until rstGetCurrentMemberCollections.EOF
                lngCollectionMember1ID = 0
                lngCollectionMember2ID = 0
                lngCollectionMember3ID = 0
                lngCollectionMember4ID = 0
                strMember1Fullname = ""
                strMember2Fullname = ""
                strMember3Fullname = ""
                strMember4Fullname = ""
                strCollectionTypeName = ""
                strCollectionItemPrefix = ""


                lngCollectionID = rstGetCurrentMemberCollections!COLLID
                strCollectionTypeName = rstGetCurrentMemberCollections!txtCollectionTypeName


                If IsNull(rstGetCurrentMemberCollections!strPrefix) Then
                    strCollectionItemPrefix = "UNDEFINED"
                Else
                    strCollectionItemPrefix = rstGetCurrentMemberCollections!strPrefix
                End If


                If IsNull(rstGetCurrentMemberCollections!lngMember1ID) Then
                    strMember1Fullname = "-"
                Else
                    strMember1Fullname = strGetNameByID(rstGetCurrentMemberCollections!lngMember1ID)
                End If


                If IsNull(rstGetCurrentMemberCollections!lngMember2ID) Then
                    strMember2Fullname = "-"
                Else
                    strMember2Fullname = strGetNameByID(rstGetCurrentMemberCollections!lngMember2ID)
                End If
                
                If IsNull(rstGetCurrentMemberCollections!lngMember3ID) Then
                    strMember3Fullname = "-"
                Else
                    strMember3Fullname = strGetNameByID(rstGetCurrentMemberCollections!lngMember3ID)
                End If
                
                If IsNull(rstGetCurrentMemberCollections!lngMember4ID) Then
                    strMember4Fullname = "-"
                Else
                    strMember4Fullname = strGetNameByID(rstGetCurrentMemberCollections!lngMember4ID)
                End If


                lbAssociatedCollections.AddItem CStr(lngCollectionID) & ";" & strCollectionItemPrefix & ";" & _
                    strCollectionTypeName & ";" & strMember1Fullname & ";" & strMember2Fullname & ";" & _
                    strMember3Fullname & ";" & strMember4Fullname


                rstGetCurrentMemberCollections.MoveNext
            Loop
        End If
    End If

Ideas???

[ssanfu]

The main problem is that the table design in not normalized. The Collections Table looks like an Excel spreadsheet design. In an Access table, you would have one field for the member ID number (lngMemberID).
What would you have to do if there is another member ID you wanted to add? You would have to change the table design, the queries, the forms, the reports and possible the code.


You really need to fix your design before you move on. If you don't, you will probably end up bald from pulling your hair out, trying to make things work. (Ask me how I know )


Consider:

tblCollections
-------------
CollectionID_PK (Autonumber)
lngMemberID_FK (Long Integer)
lngCollectionTypeID_FK (Long Integer)
strPrefix (Short Text)




tblCollectionTypes
------------------
CollectionTypeID_PK (Autonumber)
txtCollectionTypeName (Short Text)



tblMembers
-------------
MemberID_PK (Autonumber)
MemberFirstName (Short Text)
MemberLastName (Short Text)

[drhansenjr]

4 members will never be exceeded. And I solved the problem with a simple LEFT JOIN (but thanks!)

PARAMETERS [lngCurrentMemberID] Long;
SELECT tblCollections.ID AS COLLID, tblCollections.strPrefix, tblCollections.lngMember1ID, tblCollections.lngMember2ID, tblCollections.lngMember3ID, tblCollections.lngMember4ID, tblCollectionTypes.txtCollectionTypeName AS TYPENAME,tblCollectionTypes.ID, tblCollectionTypes.txtCollectionTypeName
FROM tblCollections LEFT JOIN tblCollectionTypes
ON tblCollections.lngCollectionType = tblCollectionTypes.ID
WHERE [lngCurrentMemberID] IN (tblCollections.lngMember1ID, tblCollections.lngMember2ID, tblCollections.lngMember3ID, tblCollections.lngMember4ID);

[mike60smart]

I would recommend that you take note of Steve's comments regarding a record for each Member in a separate related table vice fields in the same table.

If you do not correct the bad table design now you will hit further snags down the line.

[drhansenjr]

This is a personal (family history data that will be used to populate a database-driven Wix website. It's a one-time-use and essentially a throw-away app that will go away once the initial data is collected and transferred elsewhere. If this were a serious, important business app I would heartily agree with you, but it's not. In 90% of the cases there will only be two members in collection. I added two more for a little wiggle-room. Members in excess of 4 related to a collection I don't care about and they can be ignored with no adverse effects. Sorry I didn't make this clear earlier.

Thanks --

DH