Danean
asked on
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.
Table1
ID COMPANY XACDATE FACILITY SSNO PROCDESC CUNITS CLAIMID CHGAMOUNT INCIDENTNO
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:
ID COMPANY XACDATE FACILITY SSNO PROCDESC CUNITS CLAIMID CHGAMOUNT INCIDENTNO
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
Thanks.
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.
Table1
ID COMPANY XACDATE FACILITY SSNO PROCDESC CUNITS CLAIMID CHGAMOUNT INCIDENTNO
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:
ID COMPANY XACDATE FACILITY SSNO PROCDESC CUNITS CLAIMID CHGAMOUNT INCIDENTNO
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
Thanks.
ASKER
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.
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.
ASKER
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.
SSNO PROCDESC CUNITS CLAIMID
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:
TRANSCRIPTION BY LINE
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.
Thanks.
This is the data in excel. I used fewer columns because the charge amount does not matter in what I am trying to do.
SSNO PROCDESC CUNITS CLAIMID
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:
TRANSCRIPTION BY LINE
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.
Thanks.
can you post the actual Excel file please?
...Then based on this exact Excel file, what is the *exact * output you are seeking?
...Then based on this exact Excel file, what is the *exact * output you are seeking?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Works perfect.
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...