Solved

combine informaiton on two rows into 1 row

Posted on 2012-04-12
7
348 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37840616
Danean,

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

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

820 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