Merging information from 3 columns into 1

[Johanna Gronlund]

Can anyone help? I have a table with the following columns: ID, Diagnosis1,
Diagnosis2, Diagnosis3, Factor1, Factor2 and Factor3.

What I would need to have is a table with 3 rows for each ID and only two
other columns: Diagnosis and Factor. Like this:
ID Diagnosis Factor
ID1, Diagnosis1, Factor1
ID1, Diagnosis2, Factor2
ID1, Diagnosis3, Factor3
ID2, Diagnosis1, Factor1
ID2, Diagnosis2, Factor2
ID2, Diagnosis3, Factor3
etc.

I have got as far as creating a table with 3 fields for each ID so that all
3 diagnosis and factors can be appended to it. Is there a way to lookup
against the Diagnosis1 in my original table and to append it in my new table
for those rows that say Diagnosis1?

The reason why I need the information laid in rows rather than columns is
that I need to have the maximum factor for each patient rather than diagnosis
(hence I need all factors in the same column).

Many thanks in advance,

Johanna

 

[Amy Blankenship]

Can anyone help? I have a table with the following columns: ID,
Diagnosis1,
Diagnosis2, Diagnosis3, Factor1, Factor2 and Factor3.

What I would need to have is a table with 3 rows for each ID and only two
other columns: Diagnosis and Factor. Like this:
ID Diagnosis Factor
ID1, Diagnosis1, Factor1
ID1, Diagnosis2, Factor2
ID1, Diagnosis3, Factor3
ID2, Diagnosis1, Factor1
ID2, Diagnosis2, Factor2
ID2, Diagnosis3, Factor3
etc.

I have got as far as creating a table with 3 fields for each ID so that
all
3 diagnosis and factors can be appended to it. Is there a way to lookup
against the Diagnosis1 in my original table and to append it in my new
table
for those rows that say Diagnosis1?

The reason why I need the information laid in rows rather than columns is
that I need to have the maximum factor for each patient rather than
diagnosis
(hence I need all factors in the same column).

Why not simply select the maximum factor for each diagnosis, then join that
with the diagnosis?

 

[Johanna Gronlund]

Thanks for your reply. The problem is that I would get maximum for each
diagnosis (3 per patient) when I need maximum per patient (1 per patient).

Clarification to my question: each diagnosis has a factor (values between
0.01-1). I need to know the highest factor for each patient (and the
diagnosis with that highest factor). I will want to discard all other
diagnosis and factors for that patient.

Johanna

 

[tedmi]

It will take three queries:

INSERT INTO NewTable (ID, Diagnosis, Factor)
SELECT ID, Diagnosis1, Factor1 FROM OldTable

NewTable is the one with 3 columns, Oldtable the one with 7.
After running this query, change Diagnosis1 and Factor1 to ...2, rerun, then
change to ...3 and run again.

 

[John Spencer]

Easy way to build the data - USE a UNION ALL query to get the data. Then use
that query as the source to append all the records and all the data to your
new table.

SELECT ID, Diagnosis1 as Dx, Factor1 as Factor
FROM YourTable
WHERE Diagnosis1 is Not Null
UNION ALL
SELECT ID, Diagnosis2 , Factor2
FROM YourTable
WHERE Diagnosis2 is Not Null
UNION ALL
SELECT ID, Diagnosis3 , Factor3
FROM YourTable
WHERE Diagnosis3 is Not Null

INSERT INTO NewTable(ID, Diagnosis, Factor)
SELECT Id, Dx, Factor
FROM TheUnionQuery

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

Sorry missed part of your question.

You can use the Union query as the source of an aggregate query.

SELECT ID, Max(Factor) as MakeUpKing
FROM TheUnionQuery

If you also want the DX, the query is a bit more complex and probably quite
a bit slower.

SELECT ID, DX, Factor
FROM TheUNIONQuery
WHERE Factor =
(SELECT Max(Factor)
FROM THeUnionQuery as B
WHERE B.ID = TheUnionQuery.ID)


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..