Splitting a table w/multiple Headers/Trailers

[Michael Abrams]

Access 2003 sp3

I receive a text file with data for 11 unique medical groups.
Tablename: ELIGIBILITY(ALL GROUPS)

Sample:

Code: Select all

REC#	H/T	  GRP CODE	GROUP      NAME              DOB               ETC.......
1	   H	S     005200	ABC
5123	T	 	 
5124	H	S     005204	DEF
6602	T	 	 
6603	H	S     009695	GHI
7344	T	 	 
7345	H	S     020637	JKL
8112	T	 	 
8113	H	S     024058	MNO
8462	T	 	 
8463	H	S     024365	PQR
9228	T	 	 
9229	H	S     024624	STU
9902	T	 	 
9903	H	S     025944	VWX
9904	T	 	 
9905	H	S     025945	YZA
9906	T	 	 
9907	H	S     025946	ZXY
9908	T	 	 
9909	H	S     025950	POY
9910	T	 	 

In between the H and T are lines of data.

I need to make 11 separate tables from this main table.

Currently I have a generic Make table query, and manually enter the Between rec#s for each separate table I require. I also have to manually change the Destination Table to the appropriate table I am making. I have to do this 11 times.
SELECT [ELIGIBILITY(ALL GROUPS)].* INTO [ABC TABLE] <-- Manually change 11 times
FROM [ELIGIBILITY(ALL GROUPS)]
WHERE ((([ELIGIBILITY(ALL GROUPS)].REC#) Between 1 And 5123)); <-- Manually change 11 times
Is there a better way to do this? I’d be more than glad to use a form with VBA, but I cannot figure out where to start !

Any help is appreciated.

Michael

[HansV]

Are the "between" values fixed, or do you have to read them from the table? I'm not sure what exactly you want to accomplish...

[Michael Abrams]

I have a weekly text file that I import. It contains records for 11 medical Group's members.

The static information would be the GrpCode and the Group and the order they are on the file.

What will vary week to week, is how much data each medical group has.

The sample I submitted is just to show you how the data looks in the original file (there are records between each Header & Trailer). The # of records between each medical group varies every week - each medical group IS separated though by a Header and a Trailer record. If there are no gaps between a H and a T, it means that that particular medical group had no activity that week.

What I am trying to do, is take the table that has ALL medical groups (ELIGIBILITY(ALL GROUPS), and separate it into 11 separate tables, one for each medical group. What I currently do, is use a Make Table query (see OP) and change the criteria for each of the 11 groups.

I was just wondering if there was a more efficient way to make 11 tables out of one table other than changing the criteria of that query 11 times.

Michael

[HansV]

I'm still not sure I understand completely, but try this code - make a copy of the database first, and test on the copy!

Code: Select all

Sub SplitTable()
    Dim dbs As DAO.Database
    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim strMake As String
    Dim strGroup As String
    Dim lngStart As Long
    Dim lngEnd As Long
    Dim f As Boolean
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM [ELIGIBILITY(ALL GROUPS)] ORDER BY [REC#]"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do While Not rst.EOF
        If rst![H/T] Like "H*" Then
            If f Then
                strMake = "SELECT * INTO [" & strGroup & "] " & _
                    "FROM [ELIGIBILITY(ALL GROUPS)] WHERE [REC#] Between " & _
                    lngStart & " And " & lngEnd
                On Error Resume Next
                dbs.TableDefs.Delete strGroup
                On Error GoTo 0
                dbs.Execute strMake, dbFailOnError
            End If
            f = True
            strGroup = rst![Group] & " TABLE"
            lngStart = rst![REC#]
        ElseIf rst![H/T] = "T" Then
            lngEnd = rst![REC#]
        End If
        rst.MoveNext
    Loop
    ' Last one
    If f Then
        strMake = "SELECT * INTO [" & strGroup & "] " & _
            "FROM [ELIGIBILITY(ALL GROUPS)] WHERE [REC#] Between " & _
            lngStart & " And " & lngEnd
        On Error Resume Next
        dbs.TableDefs.Delete strGroup
        On Error GoTo 0
        dbs.Execute strMake, dbFailOnError
    End If
    rst.Close
    Set rst = Nothing
    dbs.TableDefs.Refresh
    Set dbs = Nothing
End Sub

This code should create the tables fully automatically.

[Michael Abrams]

I can't wait to try it out at work tomorrow. That looks awesome - thank you hans.

Michael