CODEWITHSUNDEEP
vbams-access
Amy
Amy

Reputation: 641

MS-Access: Loop through code for each distinct value in a table

I have a table in my access database called Table_Example_1. Currently I have a loop that loops through an executes code based on the [Flag] field in my table, so if everything is set to 'Flag_1' it loops through these records and executes code to create a string output.

However there are some items that have the same flag for example 'Flag_1' but they need to be executed separately to the other records with the same value. I have added a field called [Grouped] and what I want to do is use this field to loop through everything in that grouping for the same flag.

So if there are 2 records set as Flag_1 and both are Group 1 these will run in the same loop. And records with Flag_1 in Group 2 will run in a separate loop. (The results of these will then be combined at the end)

My table with relevant data is below:

Business        Type            TableName   FieldName   Flag    Grouped
Red             Residential     RedTable    [Field_1]   Flag_1      1
Red             Residential     RedTable    [Field_1]   Flag_1      1
Red             Residential     RedTable    [Field_1]   Flag_1      1
Red             Residential     RedTable    [Field_2]   Flag_1      2
Red             Residential     RedTable    [Field_2]   Flag_1      2

My current code is below, this code currently only loops through records with the same Flag and does not take into account their grouping.

So what I want to do to the below code is somehow add in a section that checks the grouping and looks through all groups and then my below loop will be nested in this.

I first need to get the distinct number of groups from the recordset. My example provided only has groups 1 and 2 but this will differ each time.

I then want to nest by below loop in this. I was thinking of using a for loop but I'm not sure how to check for the number of groups in the recordset and execute this for each of these groups.

 Public Function CreateFilterString(Business As String, Type As String, TableName As String)
        'Define variables
        Dim resultString_1 As String
        Dim resultStringFinal As String
        Dim recSet As Recordset
        
        'Flag_1 Related Flag RecordSet
        Set recSet = CurrentDb.OpenRecordset("SELECT * FROM Table_Example_1 WHERE Table_Example_1.TableName = '" & TableName & "' AND Table_Example_1.Business = '" & Business & "' AND Table_Example_1.Type = '" & Type & "' AND Table_Example_1.Flag = 'Flag_1' ")
        
        'If there is no data in the recordset, then set the resultString_1 to blank
        If recSet.RecordCount = 0 Then
            resultString_1 = ""
        'Otherwise execut the following
        Else
            
            'If there is data in the recordset and the Flag field is Flag_1
            If recSet.RecordCount > 0 And recSet.Fields(7).Value = "Flag_1" Then
            
            'Set the resultString variable
            recSet.MoveFirst ' recSet now contains first record from the query
            While Not (recSet.EOF)
                'If it is the first case in the recordset
                If recSet.RecordCount = 1 Then
                    'code executed here that sets value of resultString_1
                Else
                    'code executed here that sets value of resultString_1
                End If
            'moves to next record
            recSet.MoveNext
            Wend 'end while
            
            recSet.Close 'close recordset when done
            
            'End of Flag_1 Related Flag
            End If
        End If
        
        'Set the result variable returned by the function
        CreateFilterString = resultStringFinal
        
        End Function

EDIT: So to summarize and hopefully clarify what I want:

I want to be able to get the distinct values from the GROUPING field and then loop through each of these values. I need a loop that runs through each unique value in the 'Grouped' field. So it will execute once for any record with a grouping of 1 and then reach the end of the loop and re-run for anything with a grouping of 2.

I understand that I can create a recordset using
CurrentDb.OpenRecordset("SELECT DISTINCT Grouped FROM Table_Example_1 WHERE ... in order to get the unique values, but I don't know how to then use this information in order to create a loop to run through each value in the GROUPED field.

Thanks in advance

Upvotes: 1

Views: 776

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

If I understood your question, you need two loops. One that will retrieve unique groups and one that will retrieve data for specific a group.

Something like this:

Public Sub T()
    On Error GoTo Trap
    
    Dim rGroup As DAO.Recordset, rData As DAO.Recordset
    Dim idx As Long
    
    'Get groups
    Set rGroup = CurrentDb().OpenRecordset("SELECT DISTINCT Grouped FROM Table", dbOpenSnapshot)
    If rGroup.EOF Then GoTo Leave
    With rGroup
        .MoveLast
        .MoveFirst
    End With
    
    'Loop through unique groups and get data for each group
    For idx = 1 To rGroup.RecordCount
        Set rData = CurrentDb().OpenRecordset("SELECT * FROM Table WHERE Grouped=" & rGroup![Grouped], dbOpenSnapshot)
        
        'Now the rData holds data specific to a group
        'Do more work
        
        rData.Close
        rGroup.MoveNext
    Next idx
    
Leave:
   If Not rData Is Nothing Then rData.Close
   If Not rGroup Is Nothing Then rGroup.Close
   Exit Sub
   
Trap:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Sub

Upvotes: 1

Related Questions