# Solved: Access 2003 VBA - Read and update a table to create record groups



## jim4004 (Feb 23, 2006)

I need to read and update a table that has thousands of records in it with multiple record types that need to be grouped. It's an EDI style format. The problem is I can't find a unique value to tie all of the record types together - so I'm thinking I'll just create a unique value for each group of records. Below is an example of the records. I'd like to create a unique ID for each group of Rec_Type 10-95. I was thinking I'd need to create some VBA code that reads the table and just updates a new field that stores a group ID. Here's the english version


```
Pseaudo Code:
Set counter to 1
Read Table
if rec_type >=10 and rec_type <95 then 
   Group = Counter
 Otherwise If rec_type = 95 Then
   Group = Counter
   Counter = Counter +1
  Else Group = 0

Update Group field on record

Loop - read next record
```
While I understand how to create modules and how to create update queries, I'm a little fuzzy on how to create the VBA to read through a table and evaluate each record for updating an attribute in that record. Thanks in advance for your help. 


```
ID	Rec_Type	AllText
1	1	03833
2	10	11201
3	20	77003
4	30	01770
5	31	01770
6	40	01770
7	41	01770
8	50	01770
9	60	01770
10	60	02770
11	70	01770
12	80	01770
13	90	77003
14	95	00000
15	10	13101
16	20	K1367
17	30	01K13
18	31	01K13
19	40	01K13
20	61	01K13
21	61	02K13
22	70	01K13
23	80	01K13
24	90	K1367
25	95	01615
26	99	03833
27	1	03833
28	10	11101
29	20	85680
30	30	01856
31	31	01856
32	40	01856
33	41	01856
34	50	01856
35	60	01856
36	60	02856
37	60	03856
38	70	01856
39	80	01856
40	90	85680
41	95	07510
```


----------



## cristobal03 (Aug 5, 2005)

Play with this:


```
Private Sub HandleRecordset()
  Dim db   As DAO.Database
  Dim rst  As DAO.Recordset

  Set db = Access.Application.CurrentDb
  Set rst = db.OpenRecordset([i]tablename[/i])

  With rst
    .MoveLast
    .MoveFirst

    Do While .EOF = False
      !Rec_Group = AssignGroup(!Rec_Type)
      .MoveNext
    Loop
  End With

  Set rst = Nothing
  Set db = Nothing
End Sub

' guessing at these data types
Private Function AssignGroup(ByVal num As Integer) As Long
  Static lngCounter As Long

  Select Case num
    Case Is < 10
      AssignGroup = ' something.
    Case Is >= 95
      lngCounter = lngCounter + 1
      AssignGroup = lngCounter
    Case Is >= 10
      AssignGroup = lngCounter
  End Select
End Function
```
That assumes the table will already have the additional attribute *Rec_Group*.

HTH

chris.

[edit]
Changed the second case to >= instead of >.

Changed the third case to >= instead of <=.
[/edit]


----------



## OBP (Mar 8, 2005)

Chris & Jim, can't this be achieved with a Query that sorts by Rec_Type and then AllText?


----------



## cristobal03 (Aug 5, 2005)

I don't see how sorting the recordset would fulfill the evaluative portion of the requirement. I mean, once the recordset was sorted, you'd still need to determine which records had a *Rec_Type* between 10 and 95.

But I think you're onto something, which wasn't really clear from the OP. Jim, do you want each *Rec_Type* to have its own group? Your airware seems to indicate 10-95 constitute one group, then everything above 95 would have a unique *Rec_Group* number. That's the code I wrote. Is this correct?

chris.

[edit]
Er, I guess 10-94, and 95 and above... 
[/edit]


----------



## OBP (Mar 8, 2005)

Now I am not sure I was reading the requirement correctly LOL.
Jim, can you post a zipped "sample" database explaining how you want "group" the data which can't be done with grouping in a query?


----------



## jim4004 (Feb 23, 2006)

Unfortunately the actual data contains confidential medical information that would be very difficult to wipe clean enough for public consumption. It's an internal EDI format. I can post the record types I was given, if that helps. The problem is that there is no unique key for each batch of records. I'm going to try Chris's code solution later today - thanks Chris - and see if that makes this process easier.


```
01-SUBMITTER-RECORD.
10-PROVIDER-RECORD.
20-PATIENT-RECORD.
21-NON-INS-EMPLOYMT-REC.
29-ROUTE-RECORD.
30-THIRD-PARTY-DATA-REC.
31-INSURED-RECORD.
40-CLAIM-RECORD.
41-CLAIM-DATA-COND-RECORD.
46-ADDL-PROV-INFO-RECORD.
50-ACCOMMODATIONS-RECORD.
60-INPATIENT-RECORD.
61-OUTPATIENT-RECORD.
66-TPO-OUTPUT-RECORD.
70-MEDICAL-DATA-REC.
80-PHYSICIAN-RECORD.
90-CLAIM-TRAILER-RECORD.
91-REMARKS-RECORD.
95-BATCH-TRAILER-RECORD.
99-FILE-TRAILER-RECORD.
```


----------



## cristobal03 (Aug 5, 2005)

Well, I'm not so concerned with the *Rec_Type*s as I am with the logic of the grouping. However, if the list you just posted is the reality of the *Rec_Type* attribute in your original post, I doubt my code model will work without substantial changes.

Anyway, let us know how it comes along.

chris.


----------



## jim4004 (Feb 23, 2006)

Thanks again Chris...your post was a good start...I can change some of the cases and where the counter is increased, and I think it will do.


----------



## jim4004 (Feb 23, 2006)

OK, I got it to work...thanks soooo much for your help.


```
Private Sub HandleRecordset()
  Dim db   As DAO.Database
  Dim rst  As DAO.Recordset

  Set db = Access.Application.CurrentDb
  Set rst = db.OpenRecordset("UFE_Import", dbOpenDynaset)

  With rst
    .MoveLast
    .MoveFirst

    Do While .EOF = False
        .Edit
        !GroupCD = AssignGroup(!Rec_Type)
        .Update
        .MoveNext
    Loop
  End With

  Set rst = Nothing
  Set db = Nothing
End Sub

Private Function AssignGroup(ByVal num As Integer) As Long
  Static lngCounter As Long

  Select Case num
    Case Is < 10
        AssignGroup = 0 ' something.
    Case 10 To 94
        AssignGroup = lngCounter
    Case Is = 95
        AssignGroup = lngCounter
        lngCounter = lngCounter + 1
    Case Is > 95
        AssignGroup = 0
  End Select
End Function
```


----------



## cristobal03 (Aug 5, 2005)

Good deal. You can mark this thread "Solved" using the *Thread Tools* at the top of the page.

chris.


----------



## jim4004 (Feb 23, 2006)

You're awesome Chris! Update completed. 

The hokey part of this is that an actual packet is stored in several record types (e.g., Record Types 10 through 95 = 1 submission) With no unique key between them, there's no way to group by on these. They were designed for a COBOL batch program originally. This solution gives me the ability to take that file and check it before loading into our test system. 

Thanks again Chris and OBP


----------

