Append query that parses data

[Grace]

Hello, my database imports data from excel into a
temporary table called [tbltempECA]. The primary key on
this table is [ClaimNumber]. One of the fields,
[InjuryTypes] contains data that has multiple values (i.e.
12; 24; 32). Each value corresponds to a type of Injury.
The maximum number of Injury types is 25.

I need two Append queries that will take the values in
this temp table and separate them into two tables. The
first append query takes everything but the [InjuryTypes]
and adds them to a table called [tblECA]. I now need to
create another append query that takes the values from the
[InjuryTypes] field and creates one record for each
[ClaimNumber] and its' corresponding [InjuryTypes]into
another table called [tblInjuryTypesECA]

I am having difficulty setting up this second query. Is
there a away to have each value (12; 24; 32) parsed out
into three separate fields, [InjuryType1], [InjuryType2],
[InjuryType3].

Hope this all makes sense. Your help is greatly
appreciated.

Grace

 

[John Spencer (MVP)]

I would do this using VBA and have it create the "child" records.

I can't think of a way to do this in one query. I would probably have to use two
queries repeatedly

INSERT INTO tblInjuryTypeECA (ClaimNumber,InjuryType)
SELECT ClaimNumber,
Left(InjuryType,Instr(1;InjuryType,";")-1)
FROM tblTempECA
WHERE InjuryType Like "*;*"

UPDATE tblTEMPECA
SET InjuryType = Mid(InjuryType,Instr(1;InjuryType,";")+1)
WHERE InjuryType Like "*;*"

Loop through those two until you no longer have any matches and then do one last update

INSERT INTO tblInjuryTypeECA (ClaimNumber,InjuryType)
SELECT ClaimNumber,
InjuryType
FROM tblTempECA
WHERE InjuryType is Not Null