one to many view in single row (Patients and Diagnosis')

[Guest]

I have clients that want to view patient diagnosis' like this

Patient Diagnosis1, Diagnosis2, Diagnosis3 (upto10)

Is there a way to construct this in a query?

my tables are:

Patient (Patient info, Key is Pat_ID)
Pat_Enc (Which is a patient encounter , Key is Pat_ID)
Pat_Enc_DX (this includes the Diagnosis which is ICD9_Code, Line is the
Diagnosis Number, and also includes Pat_ID)
Clarity_EDG (has the DX_Name which is the ICD9_Code description)

I was going to write a VBA loop, but I bet there's a SQL solution to this -
which would be great for a Crystal Report... Thanks!

 

[John Vinson]

I have clients that want to view patient diagnosis' like this

Patient Diagnosis1, Diagnosis2, Diagnosis3 (upto10)

Is there a way to construct this in a query?

my tables are:

Patient (Patient info, Key is Pat_ID)
Pat_Enc (Which is a patient encounter , Key is Pat_ID)
Pat_Enc_DX (this includes the Diagnosis which is ICD9_Code, Line is the
Diagnosis Number, and also includes Pat_ID)
Clarity_EDG (has the DX_Name which is the ICD9_Code description)

I was going to write a VBA loop, but I bet there's a SQL solution to this -
which would be great for a Crystal Report... Thanks!

The VBA loop is unfortunately your best bet. Search at
http://www.mvps.org/access for "Concatenate" for sample code.

The only SQL option requires a tenfold self-join with criteria on each
diagnosis to exclude all the previous diagnoses... hard to writer,
hard to maintain, and VERY slow.


John W. Vinson[MVP]

 

[Van T. Dinh]

I am not aware of any SQL solution. For VBA, see:

http://www.mvps.org/access/modules/mdl0004.htm

 

[Tom Ellison]

Dear Jonefer:

It seems to me there IS a query solution to this, although the methods used
seem somewhat obscure.

I would construct a query with multiple subqueries. The position of the
Diagnoses in the list (Diagnosos1, Diagnosis2, etc.) would be ranked in a
query. There needs to be a way to rank them (similar to sorting, but
assigning a number to each in order) so they are unique. I do not see how,
or whether this is necessarily possible using just your data (not enough
information in your post) but it can be done using the Rnd() function if
necessary.

Based on the query that ranks them, another query can be built that creates
the diagnoses. This would be done with a series of subqueries for 10
diagnoses. The comma/space can be inserted by repeating the subquery and
testing to see if it is NULL. If the following diagnosis is not null,
append the comma/space, then append the diagnosis. Repeat for each
diagnosis.

Now, this is a general description of the method. To do the whole thing in
a query, I'd need to see complete details of your tables and sample data.

Let me know if you want to proceed with this. It may perform quite well,
even better than the VBA solution. The query will be large, but not really
so complex, as the core of what it does would simply be repeated 9 times.

If you wish, you could instead provide me with the source code for a query
that produces all the columns needed. This would actually be preferable.
Please give a sample of the data it produces. I'll try to show you how to
proceed from there.

Tom Ellison