COMBINING UNIQUE VALUES FROM 4 COLUMNS INTO 1

[watembo]

Dear friends,

I have data split into 4 columns: diagnosis1, diagnosis2, diagnosis3,
diagnosis4. Each column may have a unique value upto code 15 and a
code cannot be repeated in any of the four columns. Unique records are
defined by date of diagnosis and a unique patient code. Thus a patient
can have 3 conditions in one visit. I then want a simple crosstab to
summarise the data in, say 2 columns (Diagnosis and Date) such that if
one record has 3 codes in one date, access should split the 3 record
into 3 rows. From there I can have a crosstab of Diagnosis by Date. If
I were using SPSS, it would be easy for me to just create a multiple-
response group out of diag1, diag2, diag3, diag4 and crosstabulate it
with date. Anyone out there kind enough to assist?

Will be most grateful.

 

[Bob Quintal]

m:

Dear friends,

I have data split into 4 columns: diagnosis1, diagnosis2,
diagnosis3, diagnosis4. Each column may have a unique value upto
code 15 and a code cannot be repeated in any of the four columns.
Unique records are defined by date of diagnosis and a unique
patient code. Thus a patient can have 3 conditions in one visit. I
then want a simple crosstab to summarise the data in, say 2
columns (Diagnosis and Date) such that if one record has 3 codes
in one date, access should split the 3 record into 3 rows. From
there I can have a crosstab of Diagnosis by Date. If I were using
SPSS, it would be easy for me to just create a multiple- response
group out of diag1, diag2, diag3, diag4 and crosstabulate it with
date. Anyone out there kind enough to assist?

Will be most grateful.

Proper relational database design would have been to create a
subtable with one Diagnosis column, where you add rows related to the
subtable instead of the multiple columns.

What you will need to do is create an UNION query to simulate the
properly designed table and use that as a source for the crostab
query.

The union query would look like
SELECT PatientID, DateOfDiagnosis, diagnosis1 as diagnosis from table
UNION
SELECT PatientID, DateOfDiagnosis, diagnosis2 as diagnosis from table
UNION
SELECT PatientID, DateOfDiagnosis, diagnosis3 as diagnosis from table
UNION
SELECT PatientID, DateOfDiagnosis, diagnosis4 as diagnosis from table
;

 

[John W. Vinson]

Dear friends,

I have data split into 4 columns: diagnosis1, diagnosis2, diagnosis3,
diagnosis4. Each column may have a unique value upto code 15 and a
code cannot be repeated in any of the four columns. Unique records are
defined by date of diagnosis and a unique patient code.

Then you have an incorrectly normalized table.

Thus a patient
can have 3 conditions in one visit.

And they might have five. What will you do then? Restructure your table, your
forms, all your queries, all your reports? Kick the patient out? Deny the
existance of the fifth condition? OUCH!

A many to many relationship - a table of Patients, a table of Diagnoses, each
related one to many to a table of PatientDiagnoses with fields PatientCode,
DiagnosisCode, DIagnosisDate, with one *record* per diagnosis for that patient
on that date - is a better structure.

I then want a simple crosstab to
summarise the data in, say 2 columns (Diagnosis and Date) such that if
one record has 3 codes in one date, access should split the 3 record
into 3 rows. From there I can have a crosstab of Diagnosis by Date. If
I were using SPSS, it would be easy for me to just create a multiple-
response group out of diag1, diag2, diag3, diag4 and crosstabulate it
with date. Anyone out there kind enough to assist?

A "Normalizing Union Query" will do this for you. You need to be in SQL view
(the query grid can't do it); the SQL code would resemble (making guesses at
your fieldnames)

SELECT PatientCode, [Date], Diagnosis1 AS Diagnosis
FROM yourtable
WHERE Diagnosis1 IS NOT NULL
UNION ALL
SELECT PatientCode, [Date], Diagnosis2 AS Diagnosis
FROM yourtable
WHERE Diagnosis2 IS NOT NULL
UNION ALL
SELECT PatientCode, [Date], Diagnosis3 AS Diagnosis
FROM yourtable
WHERE Diagnosis3 IS NOT NULL
UNION ALL
SELECT PatientCode, [Date], Diagnosis4 AS Diagnosis
FROM yourtable
WHERE Diagnosis4 IS NOT NULL;

Save this query and base your crosstab on it.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[watembo]

Dear friends,

I have data split into 4 columns: diagnosis1, diagnosis2, diagnosis3,
diagnosis4. Each column may have a unique value upto code 15 and a
code cannot be repeated in any of the four columns. Unique records are
defined by date of diagnosis and a unique patient code.

Then you have an incorrectly normalized table.

Thus a patient
can have 3 conditions in one visit.

And they might have five. What will you do then? Restructure your table, your
forms, all your queries, all your reports? Kick the patient out? Deny the
existance of the fifth condition? OUCH!

A many to many relationship - a table of Patients, a table of Diagnoses, each
related one to many to a table of PatientDiagnoses with fields PatientCode,
DiagnosisCode, DIagnosisDate, with one *record* per diagnosis for that patient
on that date - is a better structure.

I then want a simple crosstab to
summarise the data in, say 2 columns (Diagnosis and Date) such that if
one record has 3 codes in one date, access should split the 3 record
into 3 rows. From there I can have a crosstab of Diagnosis by Date. If
I were using SPSS, it would be easy for me to just create a multiple-
response group out of diag1, diag2, diag3, diag4 and crosstabulate it
with date. Anyone out there kind enough to assist?

A "Normalizing Union Query" will do this for you. You need to be in SQL view
(the query grid can't do it); the SQL code would resemble (making guessesat
your fieldnames)

SELECT PatientCode, [Date], Diagnosis1 AS Diagnosis
FROM yourtable
WHERE Diagnosis1 IS NOT NULL
UNION ALL
SELECT PatientCode, [Date], Diagnosis2 AS Diagnosis
FROM yourtable
WHERE Diagnosis2 IS NOT NULL
UNION ALL
SELECT PatientCode, [Date], Diagnosis3 AS Diagnosis
FROM yourtable
WHERE Diagnosis3 IS NOT NULL
UNION ALL
SELECT PatientCode, [Date], Diagnosis4 AS Diagnosis
FROM yourtable
WHERE Diagnosis4 IS NOT NULL;

Save this query and base your crosstab on it.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Thanks alot John. This worked perfectly. I had earlier tried another
approach [normalised tables] using sub-forms and though it worked, I
think my screen looks nicer on this. On the other hand my
relationships structure reads Indeterminate. Otherwise thank you very
much. I will closely evaluate the table and will succeed using your
advice.

 

[John W. Vinson]

Thanks alot John. This worked perfectly. I had earlier tried another
approach [normalised tables] using sub-forms and though it worked, I
think my screen looks nicer on this. On the other hand my
relationships structure reads Indeterminate. Otherwise thank you very
much. I will closely evaluate the table and will succeed using your
advice.

It's almost always a mistake to let the user interface dictate your table
structure. Get your tables right first - properly normalized, no repeating
fields (such as Diagnosis1 - 4!), good relationships, etc.; you may have to
put in a bit more effort designing a user-friendly form, but your job will be
much easier down the road!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[rumkus]

It's almost always a mistake to let the user interface dictate your table
structure.

Such a comment !
Thank you John.