Multiple Groups For Like Items 1

[OrWolf]

I'm trying to create a zip code list for sales. I'm able to sort by sales person and return Min and Max. However the problem is that there is overlap where zip codes aren't sequential for the territory. So for that I have a query that shows me all of the zip codes for a sales person, sorted by zip code. How can I either group seperately each of these ranges or add an identifier automatically to each new grouping? In the example below I would want four zip code groups, two for Tom and two for Jane.

Example:
Sales Person Zip Code
Tom Smith 01000
Tom Smith 01001
Tom Smith 01002
Jane Gray 01003
Jane Gray 01004
Tom Smith 01005
Jane Gray 01006


Thanks!

 

[GingerR]

Ok, if I get you right, the first three "TOM" items will be i.e. Group 1 for Tom, the first two "JANE" items will be Group 1 for Jane, the 4th "TOM" item will be Group 2 for Tom, and the last item will be Group 2 for Jane? if so, do this:

You'll have to play with it a little bit but this is the basics of how you'd do it. I have a table called "DATA" with fields "GROUP", "NAME" and "ZIPCODE". Group is blank. "NAME" and "ZIP" have the same data you have above. The code runs through the table, and each time it gets to a new name it determines if that name already has had a "Group" assigned to it. If so, it gets the maximum group previously assigned, adds one to it, and writes it to the table. If not, it starts at 1.

1) Make sure you have a reference to MS DAO 3.6
2) Open a new MODULE, and paste this into it:

Function CalculateOrder()

Dim rs, rsName As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from Data ORDER BY Data.ZipCode")
Dim intGroup As Integer
Dim strName, strSQL As String


intGroup = 1
strName = rs!Name
rs.MoveFirst
While Not rs.EOF
    If IsNull(rs!Group) Then
        If rs!Name <> strName Then
            strName = rs!Name
            'Group is blank, so see if the same person has already been assigned a group number
            strSQL = "SELECT Data.Name, Max(Data.Group) AS MaxOfGroup " & _
            "FROM Data " & _
            "GROUP BY Data.Name " & _
            "HAVING (Max(Data.Group) Is Not Null) and Data.Name='" & rs!Name & "'"
            
            
            Set rsName = CurrentDb.OpenRecordset(strSQL)
            'If they have not, make the group number = 1
            If rsName.RecordCount = 0 Then
                intGroup = 1
            Else 'Make the new Group Number be the last one assigned + 1
                intGroup = rsName!MaxOfGroup + 1
            End If
        End If
            'Put the new Group Number into the table
            rs.Edit
            rs!Group = intGroup
            rs.Update
    End If

    
    rs.MoveNext
Wend
End Function

the result I got was this:
Group Name ZipCode
1 Tom 01000
1 Tom 01001
1 Tom 01002
1 Jane 01003
1 Jane 01004
2 Tom 01005
2 Jane 01006

it filled in the "GROUP" field in the table. Now you can use this result to group up your stuff by Name AND Group.

Hope this helps.

 

[OrWolf]

That worked great. Thank you very much for the help. For some reason running this through a function didn't dawn on me.

 

[GingerR]

it's just something that has dawned on me in the past year or so. i fought it for a long time, but now that i know how to do it, it's easy!

Glad it worked for you.

g