Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


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

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


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:


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

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,
From table
Where <whatever your selection criteria are>


Select table.servicecode,
From table


Select table.servicecode,
From table

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.


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

Thanks! That worked.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close