Parse Cell and Flatten Out Table

I have files that are too big for excel to manipulate and need help breaking down the data in access. Below is a sample of 3 lines of data for simplicity

ACK_ID Benefit_Code
A1 1A1G1H1I3D
A2 2E2F2G2J2K3D3H2T
A3

For every ACK ID I need to create a separate/additional record for Each Benefit_Code, each benefit code is 2 chars and there can be either no codes are several for each ACK_ID. For the example above A1 would have 5 rows, A2 would have 8 and A3 none. If anyone could help, it would really be appreciated.

On 09/01/2012 22:32:00, Andrew Swartz wrote:
> I have files that are too big for excel to manipulate and need help
> breakin g down the data in access. Below is a sample of 3 lines of data
> for simplic ity
>
> ACK_ID Benefit_Code
> A1 1A1G1H1I3D
> A2 2E2F2G2J2K3D3H2T
> A3
>
> For every ACK ID I need to create a separate/additional record for Each
> Ben efit_Code, each benefit code is 2 chars and there can be either no
> codes ar e several for each ACK_ID. For the example above A1 would have 5
> rows, A2 w ould have 8 and A3 none. If anyone could help, it would really
> be appreciat ed.
>

Two Tables. Don't know what the ACK stands for so call one Table TblAcks and
the other TblBenefits Table TblAcks and ACK_ID is noy a good name for
something that not a main key to the table (see the end of this post) ID
AutoNumber Primary Key ACK_ID Text ? 2letters, but probably more, Probably
Indexed with probably no duplicates

TblBenefits
BenefitID AutoNumber Primary Key
ID Long Integer Foreign Key for Table TblAcks
BenefitCode Text 2 letters
BenefiDesc Text ' What the code means

Then set up a relationship between the two tables with the two IDs linked and
enforce referential integrety. You will need a form for the TblAcks with a
subform for the benefits. This way you can as many benefits associated with
each ACK_ID as you like.

Getting a string like "2E2F2G2J2K3D3H2T" is trickier, and will require a user
defined function .... but that's another story.

Back to the Table TblAcks
I would prefere to see
AckID AutoNumber Primary Key
AckCode Text ? 2letters, but probably more, Probably Indexed with probably no
duplicates AckDesc ? This must signify something. Surely no-one is going to
dream A3 or G68 out of thin air

and TblBenefits
BenefitID AutoNumber Primary Key
AckID Long Integer Foreign Key for Table TblAcks
BenefitCode Text 2 letters
BenefiDesc Text ' What the code means

Phil

I already have a table that describes all of the types of benefit codes another that has more detailed information on the ACK_ID. My main problem is Creating 5 rows for ACKID A1 and 8 seperate rows for ACKID A2. I dont know how to create a function that can loop through a string ex"2E2F2G2J2K3D3H2T") and can create 8 different rows like below

ACKID Benefit_Code
A2 2E
A2 2F
A2 2G
....
A2 3D
A2 2T

Thanks for the help

On Jan 9, 5:05*pm, Andrew Swartz <andrewcswa...@gmail.com> wrote:
> I already have a table that describes all of the types of benefit codes another that has more detailed information on the ACK_ID. My main problem isCreating 5 rows for ACKID A1 and 8 seperate rows for ACKID A2. I dont knowhow to create a function that can loop through a string ex"2E2F2G2J2K3D3H2T") and can create 8 different rows like below
>
> ACKID *Benefit_Code
> A2 * * 2E
> A2 * * 2F
> A2 * * 2G
> ...
> A2 * * 3D
> A2 * * 2T
>
> Thanks for the help

You wrote
A1 1A1G1H1I3D
A2 2E2F2G2J2K3D3H2T
And then start talking about 5 times and 8 times and it comes out
confusing. The codes mean nothing to us. We aren't in the next cube
over. So if I got the concept wrong, C'est la vie.

Below is aircode. You pass your codes; 1A1G1H1I3D or
A2E2F2G2J2K3D3H2T to the routine. Based on the length of the values
passed and divided by 2 it adds records.

Public Sub HowManyTimes(varValue As Variant, varKey As Variant)
Dim intFor As Integer 'loop counter. Assumes 2 letter increments
Dim strCode As String

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("AddSomeRecs", dbOpenDynaset)

If Not IsNull(varValue) Then
For intFor = 1 To Len(varValue) Step 2

strCode = Mid(varValue, infFor, 2)

rst.AddNew
rst!ForeignKey = varKey 'field you link on
rst!Field1 = Date 'update, pass fields you need to sub
rst.TwoLetterCode = strCode
rst.Update

Next

End If

rst.Close
Set rst = Nothing

End Sub

On 10/01/2012 01:01:37, Patrick Finucane wrote:
> On Jan 9, 5:05*pm, Andrew Swartz <andrewcswa...@gmail.com> wrote:
>> I already have a table that describes all of the types of benefit codes a
> nother that has more detailed information on the ACK_ID. My main problem
> is Creating 5 rows for ACKID A1 and 8 seperate rows for ACKID A2. I dont
> know how to create a function that can loop through a string
> ex"2E2F2G2J2K3D3 H2T") and can create 8 different rows like below
>>
>> ACKID *Benefit_Code
>> A2 * * 2E
>> A2 * * 2F
>> A2 * * 2G
>> ...
>> A2 * * 3D
>> A2 * * 2T
>>
>> Thanks for the help
>
> You wrote
> A1 1A1G1H1I3D
> A2 2E2F2G2J2K3D3H2T
> And then start talking about 5 times and 8 times and it comes out
> confusing. The codes mean nothing to us. We aren't in the next cube
> over. So if I got the concept wrong, C'est la vie.
>
> Below is aircode. You pass your codes; 1A1G1H1I3D or
> A2E2F2G2J2K3D3H2T to the routine. Based on the length of the values
> passed and divided by 2 it adds records.
>
> Public Sub HowManyTimes(varValue As Variant, varKey As Variant)
> Dim intFor As Integer 'loop counter. Assumes 2 letter increments
> Dim strCode As String
>
> Dim rst As Recordset
> Set rst = CurrentDb.OpenRecordset("AddSomeRecs", dbOpenDynaset)
>
> If Not IsNull(varValue) Then
> For intFor = 1 To Len(varValue) Step 2
>
> strCode = Mid(varValue, infFor, 2)
>
> rst.AddNew
> rst!ForeignKey = varKey 'field you link on
> rst!Field1 = Date 'update, pass fields you need to sub
> rst.TwoLetterCode = strCode
> rst.Update
>
> Next
>
> End If
>
> rst.Close
> Set rst = Nothing
>
> End Sub
>
>

I, think, Patrick he wants to build the string, so in order to help him with
the SQL, we need to know his field names

It will be something like
SELECT TblAcks.AckID, Concatenate([AckID],", ") AS Expr1
FROM TblAcks INNER JOIN TblBenefits ON TblAcks.AckID = TblBenefits.AckID;

Function Concatenate(AckID As String, Optional Delim As String) As String

Dim MyDb As Database
Dim RSt As Recordset
Dim SQLStg As String
Dim OutStg As String

SQLStg LStg = "SELECT TblBenefits.* FROM TblBenefits WHERE AckID = " & AckID
& ";"

Set MyDb = CurrentDb
Set RSt = MyDb.OpenRecordset(SQLStg)

With RSt
Do Until .EOF
If Delim > "" Then
OutStg = OutStg & !Benefit & Delim
Else
OutStg = OutStg & !Benefit
End If
.MoveNext
Loop
.Close
Set RSt = Nothing
End With

If Delim > "" Then
Concatenate = Left(OutStg, Len(OutStg) - 1) ' Remove final comma
Else
Concatenate = OutStg
End If

End Function

Phil