1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by jim4004, Jun 1, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. jim4004

    jim4004 Thread Starter

    Joined:
    Feb 23, 2006
    Messages:
    92
    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

    Code:
    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. :)

    Code:
    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
     
  2. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Play with this:

    Code:
    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]
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    Chris & Jim, can't this be achieved with a Query that sorts by Rec_Type and then AllText?
     
  4. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    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... :confused:
    [/edit]
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    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?
     
  6. jim4004

    jim4004 Thread Starter

    Joined:
    Feb 23, 2006
    Messages:
    92
    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.

    Code:
    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.
     
  7. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Well, I'm not so concerned with the Rec_Types 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.
     
  8. jim4004

    jim4004 Thread Starter

    Joined:
    Feb 23, 2006
    Messages:
    92
    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.
     
  9. jim4004

    jim4004 Thread Starter

    Joined:
    Feb 23, 2006
    Messages:
    92
    OK, I got it to work...thanks soooo much for your help.

    Code:
    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 
     
  10. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Good deal. You can mark this thread "Solved" using the Thread Tools at the top of the page.

    chris.
     
  11. jim4004

    jim4004 Thread Starter

    Joined:
    Feb 23, 2006
    Messages:
    92
    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
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/471832

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice