Solved

combine informaiton on two rows into 1 row

Posted on 2012-04-12
7
365 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

707 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