Consulting

Results 1 to 3 of 3

Thread: beginner with queries, very confused

  1. #1
    VBAX Newbie
    Joined
    Jul 2021
    Posts
    1
    Location

    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:

    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.
    Last edited by SamT; 07-15-2021 at 07:26 AM. Reason: Added Code Tages. Formatted Code

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,289
    Location
    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.

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    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
                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
    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 '<-----------------
    
    Last edited by SamT; 07-15-2021 at 08:17 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •