Unstack Data and Match with Unique ID

[lukejones]

I have thousands of rows of data listed by ID# with an identifier fro each component with the corresponding component ID#. For each top level ID#, there are 6 other component ID#'s which reside on this top level ID#. I would like to create a simple query that has the unique top level ID# and has columns with the associated component ID#'s on the same row.



Here's what I have (formatting is screwed up):

Main ID -Identifier- Component ID
93827- 1 -298320
93827 -2- 395482
93827 -3 -390285
......(and so on)
99928 -1- 928387
99928 -2- 283728
......(and so on)


I want this:

Main ID Identifier1 2 3 ......
93827 298320 395482 390285......
99928 928387 283728 .........

I started with my main table with all of the "stacked" data as is available. I then created 6 queries from this table and filtered each by the unique identifier. I then created a query for the unique ID#'s (group by) and created relationships, relating each query to the ID. I get an error that "You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table......"

Any ideas?

[SES]

HELP PLEASE! Really looking for an answer to this also. Needed for working with ICD coded health data.

[orange]

Please tell readers in some detail WHAT you are trying to do in plain English. ICD doesn't help me, so that's the level we need to understand.
As for post#1, what does this data represent. Is this something you created or have inherited? With all respect, it appears you are trying to use Access to solve a problem you haven't yet defined. Tell us more and there may be some answers/options.

[SES]

Please tell readers in some detail WHAT you are trying to do in plain English. ICD doesn't help me, so that's the level we need to understand.
As for post#1, what does this data represent. Is this something you created or have inherited? With all respect, it appears you are trying to use Access to solve a problem you haven't yet defined. Tell us more and there may be some answers/options.

Hello Orange, thank you for responding; here is my problem in greater detail and any assistance is greatly appreciated.


Complex healthcare data is coded using the ICD10 system. Each hospital admission episode is given a Unique Identifier (UID). The ICD system codes at different levels (Types: Primary Diagnosis (PD), Other Diagnosis (OD), Treatment/procedure (PR)). Every patient has one ICD code for the PD code type, but can have any number of ICD Codes entered against of the other codes types,. The ICD codes are listed in one column resulting in multiple entries per admission episode (from 1 to say 20, usually around 4). The Block column codes for the procedure group codes at a higher level.
Thus there are from 1-20 rows of data each beginning with the unique identifier for each admission episode multiplied by thousands of episodes of care for the entire health system. The SEQuence numbers the codes, but only the PD will consisyantly be given a 1.


I need to find the group of admission episodes where the PD or OD is coded for a specific disease (coded by any one of a set of related ICD codes) AND where a specific procedure was then performed (can also be one or more ICD codes). I wish to extract the data in one line per Unique Identifier so I can export the data to excel to manipulate in pivot tables.
I started out by creating two select queries- one for PD or OD codes of the specified diagnosis (I only used a single code) and another select table for the specific procedure code.Then I tried to update the first table with a new column for the PR code from the second table, which partly worked except that I got multiple duplicate identical rows. The original access table is as below and can't be changed.

what I'd really like is:
UID TYPE PD_CODE OD_CODE 1.....n BLOCK_CODE PR_CODE From the original table below.

Sorry the columns don't line up. Not all rows have a block code, all rows end with the SEQ number.


UID TYPE ICD_CODE BLOCK_CODE SEQ
410283 PD H269 1
410283 OD Z8643 2
410283 PR 4270204 0197 3
410283 PR 9251529 1910 4
410283 PR 9250929 1909 5
410284 PD R040 1
410284 OD Z952 2
410284 OD Z921 3
410284 PR 4167700 0373 4
410285 PD K296 1
410285 OD B9681 2
410285 PR 3047301 1008 3
410285 PR 9251599 1910 4
410286 PD R31 1
410286 OD D303 2
410286 M M81200 3
410286 PR 3681200 1089 4



Thanks for for any advice!

[orange]

I hadn't heard of ICD10 so I did a little searching. I found some sites re standard nomenclature and also found that my initial source of info had been closed in June 2013. I found a new site, and while looking for an overview or high level data model, I found this link.
Also this one.
I don't know where CIDS fits, but am wondering if you are working within some standards, or if you are working totally independently.

[June7]

Manipulating data to the single line will probably require VBA. Review: http://allenbrowne.com/func-concat.html

[orange]

Here's a sample of func-concat. Hope it's useful.
I really don't know the data or requirement.

using SQL

SELECT DISTINCT UID
, Concatrelated("ICD_CODE","Stack_ICD","UID=" & [UID] ) AS ConcatICD_Codes
FROM  stack_ICD

UID
ConcatICD_Codes

410283
H269 , Z8643 , 4270204 , 9251529 , 9250929

410284
R040 , Z952 , Z921 , 4167700

410285
K296 , B9681 , 3047301 , 9251599

410286
R31 , D303 , M81200 , 3681200

[SES]

Thank you very much. I will give the concatenation a go. Alternatively I might dump the data in a stats pack (STATA) which has data re shaping tools and then reload it into the database. I am somewhat constrained by the way the data systems are set up and used in this area.

BTW the ICD10 is the latest version of the International Classification of Diseases codes (http://apps.who.int/classifications/...browse/2010/en) and is the standard used around the world to code health data. The standards you found re the CIDS data system are probably a local system. The ICD codes are used at enterprise level for health data, however it doe not detail a lot of specialist data which are therefore captured by specialist or bespoke Clinical Information Systems