Solved

combine informaiton on two rows into 1 row

Posted on 2012-04-12
7
313 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:Danean
  • 3
  • 2
  • 2
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37839602
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...
0
 

Author Comment

by:Danean
ID: 37839683
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.
0
 

Author Comment

by:Danean
ID: 37839859
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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37839914
can you post the actual Excel file please?

...Then based on this exact Excel file, what is the *exact * output you are seeking?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37840616
Danean,

test this sample db
db-Q-27673299.accdb
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 37840943
this one have another option, "query1"
db-Q-27673299.accdb
0
 

Author Closing Comment

by:Danean
ID: 37842757
Thank you.  Works perfect.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now