combine informaiton on two rows into 1 row

I need some help with a query that will do the following:

If Table1.SSNO are equal and Table1.CUNITS = "1" then Table1.PROCDESC is added together to form 1 one PROCDESC replacing TRANSCRIPTION BY LINE description.

So this table1 example will become what the second table looks like.


1      RMR      3/6/2012      YAMP      10201      IR INJECT SHOULDER ARTHROGRAM      1      321      $164.50      2
2      RMR      3/6/2012      YAMP      10201      IR FLUORO GUIDE FOR NEEDLE      1      321      $123.00      2
3      RMR      3/6/2012      YAMP      10201      TRANSCRIPTION BY LINE      38      321      $8.36      2
4      RMR      3/6/2012      YAMP      10201      MRI EXTREMITY UPPER  JOINT W/O      1      326      $294.00      3
5      RMR      3/6/2012      YAMP      10201      TRANSCRIPTION BY LINE      61      326      $13.42      3

Table 2 example:

3      RMR      3/6/2012      YAMP      10201      IR INJECT SHOULDER ARTHROGRAM/IR FLUORO GUIDE FOR NEEDLE      38      321      $8.36      2
5      RMR      3/6/2012      YAMP      10201      MRI EXTREMITY UPPER JOINT W/O      61      326      $13.42      3

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
I'm confused...

How does the "Combined" record in table 2 end up with the CHGAMOUNT and CUNITS from ID3 in table 1.
Why is the new number ID 3 in table 2?

What is this to be used form,...this is really creating "Un-Normalized" data...
DaneanAuthor Commented:
The way I supplied is probably confusing.  I will try to shorten what I really need.

In the table I am supplied with a procedure and the procedures trancription line count.

For every procedure I need the "transcription line count" description to be over written by the actual name of the procedure.

The overwritten description will line up with the actual line count and I can discard the other rows.

It is not consistant.  Some accounts will have more than one procedure where then I would have to use the incident number so that the correct decriptions will end up on the correct line counts.
DaneanAuthor Commented:
I will try another way to hopefully clarify what I need.

This is the data in excel.  I used fewer columns because the charge amount does not matter in what I am trying to do.

10201      IR INJECT SHOULDER ARTHROGRAM      1      321
10201      IR FLUORO GUIDE FOR NEEDLE      1      321
10201      TRANSCRIPTION BY LINE      38      321
10201      MRI EXTREMITY UPPER  JOINT W/O      1      326
10201      TRANSCRIPTION BY LINE      61      326
10203      TRANSCRIPTION BY LINE      60      343
10203      MRI CERVICAL SPINE W/O CONTRAS      1      343
10208      TRANSCRIPTION BY LINE      69      392
10208      MRI LUMBAR SPINE W&W/O      1      392
10214      TRANSCRIPTION BY LINE      23      376
10214      XR CHEST 2 VIEWS      1      376
10219      TRANSCRIPTION BY LINE      16      333
10219      XR SPINE CERVICAL 4 VIEWS      1      333
10228      TRANSCRIPTION BY LINE      53      394
10326      MRI EXTREMITY LOWER JOINT WITH      1      399
10326      TRANSCRIPTION BY LINE      28      399
10326      IR FLUORO GUIDE FOR NEEDLE      1      399
10326      IR INJECT HIP ARTHROGRAM      1      399
10326      TRANSCRIPTION BY LINE      43      399

Each claim ID should have at least two descriptons:
MRI, XRAY, or whatever the exam was.

I need the true exam description to overwrite the "TRANSCRIPTION BY LINE".

There are cases where I will only have one CLAIMID and either no TRANSCRIPTION BY LINE, or no true exam description.  In this case I would do nothing.

There are cases where there are multiple procedure descriptions per 1 TRANSCRIPTION BY LINE row.  In these cases the mulitple procedures descriptions need to be on 1 row separated with "/" for example IR INJECT SHOULDER ARTH/IR FLOUORO GUIDE FOR NEEDLE.

There are cases where there are multiple INCIDENTNO per CLAIMID in which case

the 1's would be paried, the 2's would be paired. etc.

I was hopeing to pull this into access and use a query or SQL but excel macro might work also.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
can you post the actual Excel file please?

...Then based on this exact Excel file, what is the *exact * output you are seeking?
Rey Obrero (Capricorn1)Commented:

test this sample db
Rey Obrero (Capricorn1)Commented:
this one have another option, "query1"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DaneanAuthor Commented:
Thank you.  Works perfect.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.