none
Access Data Query/SQL Help RRS feed

  • Question

  • I currently have a table that looks like this
    #ID       Provider type A     Date A     ICD9 A     Provider type B     Date B     ICD9 B
    5555     20                             1/1/16     102          10                             2/1/16     105
    3333     10                             1/2/16     222          10                             1/2/16      203
    1111     10                             2/1/16     105
    1111     20                             3/1/16     600           20                            3/1/16      605

    And I want to create rows for each of the differentiated columns (my table goes from a to j) with a given #ID.  The table should look like this

    #ID        Provider type         Date        ICD9
    5555     20                             1/1/16     102          
    5555     10                             2/1/16     105
    3333     10                             1/2/16     222          
    3333     10                             1/2/16     203
    1111     10                             2/1/16     105
    1111     20                             3/1/16     600           
    1111     20                             3/1/16     605

    I do not care if the information came from provider A vs B I just want to see all the information aligned in the same column (essentially the information from provider, date and ICD9 A is all one row).

    Thanks.
     
    Monday, February 29, 2016 5:15 PM

Answers

  • You can create a union query

    SELECT [#ID], [Provider type A] AS [Provider Type], [Date A] AS [TheDate], [ICD9 A] AS [ICD9]
    FROM MyTable
    WHERE [Provider type A] Is Not Null
    UNION SELECT [#ID], [Provider type B], [Date B], [ICD9 B]
    FROM MyTable
    WHERE [Provider type B] Is Not Null

    Note that I used TheDate as name for the date field; I try to avoid Date as field name because Date is also a data type and a built-in function.

    Next, create a make-table query based on the union query.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Monday, February 29, 2016 8:10 PM