Ms access sql grouping

[dmlaz68]

Hello,

Here is a snippet of my data:

LINE# 39
40
78
79
80
81
116
117
118
156
157
195
196
197
198
199

What I would like to accomplish is a grouping of blocks of sequential numbers:

group | LINE#
1 | 39
1 | 40
2 | 78
2 | 79
2 | 80
2 | 81
3 | 116
3 | 117
3 | 118

My data count is in the 10,000's of rows. Can anyone help me here?



thanks!

[JoeM]

This gets rather tricky in Access, but what you are trying to do isn't really consistent with how data is stored in Relational Databases. Someone once put it best when they said that way to think of an Relational Database (such as Access) table is a "bag of marbles". They are all jumbled in the bag and independent of each other, so order and dependence upon one another are concepts that don't really exist here.

What you want to do is actually much easier to do in Excel. If you put the records in Excel and sort them, let's say your first Line# value is in cell B2. Then, if you wanted to put the Group number in column A, first:
- enter a 1 in cell A2
- enter this formula in cell A3: =IF(B3-B2=1,A2,A2+1)
- copy the formula from A3 down for all remaining rows in column A

To do something like this in Access, I think you would need to:
- create a field in your table to store the grouping
- create a query in which you sort your table by the Line# field
- in VBA use Recordsets (i.e. DAO) to loop through the recordset from your query, and assign values to the Grouping field using the same logic that is used in the Excel solution I posted

[dmlaz68]

Wow! I didn't realize it could be accomplished in Excel.

it worked just as you said it would.

thank you ever so much - you saved me precious time!

[JoeM]

You are welcome!

Just for fun, I wanted to see if I could do it like I said in Access. It worked. Here is how I did it.

- Add the Grouping fields to the base table, if one is not already there.
- Create a query based on the table that returns the Line Number and Grouping fields, and have the query sorted by the Line Number field.
(I named my query "MySortQuery" and assumed the field names are "LineNum" and "Grouping")
- Place the following VBA code in a module:

Public Sub AddGrouping()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Dim prevLineNum As Integer
    Dim lineNum As Integer
    Dim groupNum As Integer
    
'   Open up query in recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("MySortQuery", dbOpenDynaset)    'enter your query name
    
'   Initialize variables
    prevLineNum = -999
    groupNum = 0
    
'   Loop through recordset
    rst.MoveFirst
    Do While Not rst.EOF
'       Capture line number of current record
        lineNum = rst!lineNum   'the text after rst! should match the field name in the query
'       Compare line number to previous line number and adjust grouping accordingly
        'MsgBox "lineNum: " & lineNum & vbCrLf & "prevLineNum: " & prevLineNum
        If lineNum - prevLineNum > 1 Then groupNum = groupNum + 1
'       Assign grouping number to current record
        With rst
            .Edit
            .Fields("Grouping") = groupNum      'field name needs to match query
            .Update
            .MoveNext
        End With
'       Set previous line number
        prevLineNum = lineNum
    Loop
    
'   Close recordset
    rst.Close
    
    MsgBox "Grouping numbers assignement complete!"
    
End Sub

- Create a form and place a command button on the form. Then have the command button's On Click event call the procedure, i.e.

Private Sub Command1_Click()
    Call AddGrouping
End Sub

- Click on the button to run the code and update the Groupings.

Note: If you are using a version of Office older than 2007, you may need to go to the VB Editor, select Tools -> References and find and select a Microsoft DAO Object Library (pick the highest numbered one listed). Otherwise, you may get errors when it tries to compile the code.