Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Unanswered: Append query that parses data

    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],

    Hope this all makes sense. Your help is greatly


  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    two thoughts:

    make three passes:
    etc = 12
    and etc = 32


    a single pass (possibly slower) with
    iif([INJURYTYPES] = 24, 24, null)
    and etc

    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2004
    I am unsure as to the syntax you have recommended. If my [tblTempECA] contains the fields for each record as:

    [ClaimNumber] (sample value: AC12345) this is the primary key
    [InjuryTypes] (sample value is 12;24;32)

    and I would like the table [tblInjuryTypesECA] to have the following fields and values for each record:

    [ClaimNumber] = AC12345
    [InjuryType1] = 12
    [InjuryType2] = 24
    [InjuryType3] = 32

    I also have another table [tblInjuryTypeList] that provides descriptions that correspond to the numbers. For example InjuryType 12 = broken leg, etc.

    So, I'm not sure if we are on the same page with the syntax you provided. I appreciate your help.

    Thanks, Grace

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts