Grouping/Sorting on values in multiple similar fields in the same record

[BX1]

Hello,

I am looking for a solution to grouping/sorting on values in multiple similar fields.

I have a file that contains information about particular medical services performed (one service per record) and up to four diagnoses that a patient exhibited associated with each service. These diagnoses are in different diagnosis fields in the same record – Diagnosis 1, 2, 3 and 4 (the patient can have anywhere from 1 to 4 diagnoses). I need to take values from all of these diagnoses fields and group/sort on them as if they were one field, and do it across multiple records, to show which services were performed under each diagnosis.

There is only one group/sort. And each diagnosis value is evaluated independently.

Original data:

The client wants to see the range of diagnoses submitted for a patient and the services it was used with. The desired report structure is:

Thanks!

 

[lbass]

Use a command instead of your current tables as a data source and use union statements to combine the fields into one, like this:

Select table.servicecode,
Table.diagnosis1
From table
Where <whatever your selection criteria are>

Union

Select table.servicecode,
Table.diagnosis2
From table
Where...

Union

Select table.servicecode,
Table.dianosis3
From table
Where...

Note that the fields must be in the same order within each union segment. If you want to add an Order by clause, add it only at the end of the final segment, and use a number to indicate which field (by order), e.g., Order by 2.

You can then group on the diagnosis field.

-LB

 

[BX1]

Thanks! That worked.