beginner with queries, very confused

By way of introduction, I have no coding background and am as green as they come with Access VBA. My organization produces data in such a way that a single entry (shown below as "emple_id" below) will appear in multiple rows on the same table, with variable data ("clm_typ_cd") appearing in a separate row opposite the emple_id. In an effort to produce query results where emple_id and all corresponding clm_typ_cd information appears on the same row, I initially created a new table ("tbl102_test") where each iteration of emple_id appeared only once via the "group by" command. I then tried to create a query that would use the formula below to produce results (i.e. emple_id, clm_typ_cd, clm_typ_cd) all on one row:

Code:

---

Option Compare Database

Public Function basic_optional(ByRef emple_id As Long) As String

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim clm_typ_cd As Variant
Dim emple_id As Long
Dim Basic As String
Dim Elected As String

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("tbl02_test", dbOpenDynaset, dbSeeChanges)
    Set rs2 = db.OpenRecordset("dbo_life_benefit_data_fact_t")
   
    rs1.MoveFirst
    Do Until rs1.EOF
        rs2.MoveFirst
        Do Until rs2.EOF
            If rs1![emple_id] = rs2![emple_id] Then
                If rs2![clm_typ_cd] Like "BA*" Or "*ADD*'" Then
                    rs1.Edit
                    rs1![Basic] = "Basic"
                    rs1.Update
                Else
                    If rs2![clm_typ_cd] Like "*opt*" Or "*vo*" Then
                        rs1.Edit
                        rs1![Elected] = "Optional"
                        rs1.Update
                    Else
                    End If
                End If
            End If
        rs2.MoveNext
        Loop
    rs1.MoveNext
    Loop
   
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
db.Close

End Function

---

At this point I'm getting the "This expression is typed incorrectly, or it is too complex to be evaluated..." error. Given how little I know about the subject matter at hand, I'm concerned that I'm just pouring my time into a hole with the hope of stumbling onto the right answer. I honestly have no idea whether any of what I've produced above will even work (it passed the debug test), or even whether I'm on anything resembling the right track. ANY help with this would be greatly appreciated, even if it's just some advice around how to start over.

Sorry for the delay in answering I didn't check the forum for the last 2 days.
First of all I think using the Groupby function to obtain single entries of emple_id is correct, but does not need to go into a table, however I am not sure you need VBA to do what you want.
It should be possible with another Query based on your group by query.
But the major issue is your data structure, which is completely wrong for a relational database.
What you should have is the Main table with the emple_id and some other fixed informtaion fields.
All other data should be in one or more sub tables and related to the main table via the emple_id field.

Some VBA coding errors:
In the body of the function clm_thpe_dc = Empty; emple_id = 0; Basic = ""; Elected = ""

Imagine: When you "Dimmed" emple_id, you abrogated the use of empl_id from the Parameters section of the Function declaration. By not setting its value explicitly, it value was defaulted to zero.

The same Situation holds: The Default value of Basic and Elected is an empty string, or "". Variants default to Null

That makes your code "Translate" as

Code:

---

            If rs1![0] = rs2![0] Then '<----------------
                If rs2![] Like "BA*" Or "*ADD*'" Then '<----------------
                    rs1.Edit
                    rs1![] = "Basic" '<---------------------
                    rs1.Update
                Else
                    If rs2![] Like "*opt*" Or "*vo*" Then '<----------------
                        rs1.Edit
                        rs1![] = "Optional" '<----------------
                        rs1.Update
                    Else
                    End If
                End If

---

I don't do Access, but I suspect this (partial) Code would work better

Code:

---

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim empleFirst 'Declared as variant. Will become same Type as rs1![emple_id]: String or Long, depending on Database

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("tbl02_test", dbOpenDynaset, dbSeeChanges)
    Set rs2 = db.OpenRecordset("dbo_life_benefit_data_fact_t")
    rs1.MoveFirst
        empleFirst = rs1![emple_id]

   
    rs2.MoveFirst
        Do Until rs2.EOF
            If  rs2![emple_id] = empleFirst Then
                If rs2![clm_typ_cd] Like "BA*" Or "*ADD*'" Then
                    rs1.Edit
                    rs1![Basic] = "Basic"
                    rs1.Update
                ElseIf rs2![clm_typ_cd] Like "*opt*" Or "*vo*" Then
                    rs1.Edit
                    rs1![Elected] = "Optional"
                    rs1.Update
                End If
            End If
        ' select next record in res2 '<-----------------


---