Solved

combine informaiton on two rows into 1 row

Posted on 2012-04-12
7
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Advanced Training for Admins

Special Offer:  Buy 1 course, get 2nd free!  Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 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