combining 2 fields that are in the same column, into 1 field

[Freida]

I need help writing a query based on Diag codes. If I get a claim with 9
diag codes, then my table will show that claim 9 times(1 for each diag code).
I only want to see the 1st 3 codes, but I don't want to have 3 lines for
each claim, I want to create a field that combines the first 3 diag codes:

Right now I have the following:
Claim# Diag
8052114 607
8052114 492
8052114 774
8052114 802
8052127 429
8052127 607

This is what I want:
Claim # Diag(s)
8052114 607,492,774
8052127 429,607

Please helsp

 

[mscertified]

How will you determine 'the first 3', do you have another column (e.g.
date/time) to sequence on?
Please don't say 'the first 3 encountered' since that would be random.

-Dorian

 

[Freida]

The first 3 diag codes will be determined by the order they appear in, the
first diag code is the primary diagnosis that was given to the patient, the
others that follow are usually a result of the primary.

 

[John Spencer]

But there must be a way to know in which order those diag codes appear.
They are stored in more or less random order and when retreived can be
in a more or less random order unless you sort them by some specific
field. Storage "order" is not the same an data entry sequence.



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

 

[Freida]

But there must be a way to know in which order those diag codes appear.
They are stored in more or less random order and when retreived can be
in a more or less random order unless you sort them by some specific
field. Storage "order" is not the same an data entry sequence.



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


Its grouped by claim number and number 1 is always the first diag code. thats the way the claim comes and thats the way it is entered into the system. What might be confusing is that my database is not access, the back end is sql, and Data entry uses an application to enter in the data, and there is a field for primary diagnoses in the application- not the database, so it is already in order, it is not placing it diag codes in a random place, but in a specific order.