?
Solved

ACCESS- EASY

Posted on 2006-05-12
17
Medium Priority
?
1,738 Views
Last Modified: 2006-11-18
I use the following sql in oracle t o getr the follwing result:
SELECT DISTINCT id,
         status,
         dob,      
          cctype,
           physician_id,
          dea_id,
          last,
          first,  
 SUM(s_05),
 SUM(s_15),
 SUM(s_50)
 FROM
(SELECT
          p.id,
          DECODE(NVL(f.patient_id,m.to_id),NULL,'POST-STUDY','STUDY') status,
         TO_CHAR(p.date_of_birth,'MM/DD/YYYY') dob,      
          cc.cctype,
            rx.physician_id,
          phy.dea_id,
          phy.last,
          phy.first,
            CASE WHEN it.strength = 0.5  THEN SUM(nd.quantity_to_ship) ELSE 0
END s_05,
            CASE WHEN it.strength = 1.5  THEN SUM(nd.quantity_to_ship)
END s_15,
            CASE WHEN it.strength = 50 THEN SUM(nd.quantity_to_ship)
END s_50
--CASE WHEN item_code =  'IN'  THEN SUM(I_RESPONSE)
--END no_of_IN
           
          --d.year,
          --d.month,
          --it.strength,
          --SUM(nd.quantity_to_ship)
         
         
     FROM
          claim_centers cc,
          flolan_study_patients f,
          inventory it,
          merge_log m,
          noncompounded_detail nd,
          patients_table p,
          patient_insurance pi,
          prescriptions_table rx,
          physicians phy
--           (SELECT TO_NUMBER(TO_CHAR(ADD_MONTHS('01-DEC-1997', pi.i),'MM')) month,
--                   TO_NUMBER(TO_CHAR(ADD_MONTHS('01-DEC-1997', pi.i),'YYYY')) year,
--                   ADD_MONTHS('01-DEC-1997',pi.i) beg_date,
--                   LAST_DAY(ADD_MONTHS('01-DEC-1997',pi.i)) end_date
--              FROM positive_integers pi
--             WHERE pi.i < ROUND(MONTHS_BETWEEN(v_end_date,v_beg_date)) + 1) d
           
    WHERE p.id = rx.patient_id  
     AND P.ID = 126573

          --AND rx.prescr_ship_date >= d.beg_date
          --AND rx.prescr_ship_date <= d.end_date
          AND rx.completed_date IS NOT NULL
            AND rx.prescr_ship_date BETWEEN '01-MAY-1998' AND '28-MAY-1998'
          AND rx.void_date IS NULL
          AND nd.svcbr_id = rx.svcbr_id
          AND nd.prescription_id = rx.prescription_id
          AND nd.refill_no = rx.refill_no
          AND nd.quantity_to_ship > 0
          AND nd.inventory_id = it.id
          AND it.category = 'FLOL'
          AND it.strength IN (0.5,1.5,50)  --added a strength of 50 to capture the DILUENT
          AND pi.patient_id (+) = p.id
          AND pi.claim_center_seq (+) = 1
          AND cc.no (+) = pi.claim_center_no  
          AND f.patient_id (+) = p.id
          AND m.merge_type (+) = 'P'
          AND m.from_id (+) = f.patient_id
          AND rx.physician_id = phy.id (+)
          --and it.id = it1.id(+)
    GROUP BY p.id,
             DECODE(NVL(f.patient_id,m.to_id),NULL,'POST-STUDY','STUDY'),
             TO_CHAR(p.date_of_birth,'MM/DD/YYYY'),      
             cc.cctype,  
             --d.year,
             --d.month,
             it.strength,
             rx.physician_id,
             phy.dea_id,
             phy.last,
             phy.first)
  -- ORDER BY p.id, cc.cctype, d.year, d.month, it.strength;
 
  GROUP BY id,
         status,
         dob,      
          cctype,
           physician_id,
          dea_id,
          last,
          first


I GET THE OUPTPUT IN THE FOLOOWING FORMAT:
ID      STATUS      DOB      CCTYPE      PHYSICIAN_ID      DEA_ID      LAST      FIRST      YEAR      JAN1      JAN2      JAN3      FEB1      FEB2      FEB3      MAR1      MAR2      MAR3      APR1      APR2      APR3      MAY1      MAY2      MAY3      JUN1      JUN2      JUN3      JUL1      JUL2      JUL3      AUG1      AUG2      AUG3      SEP1      SEP2      SEP3      OCT1      OCT2      OCT3      NOV1      NOV2      NOV3      DECEMBER1      DECEMBER2      DECEMBER3                                                                                                                              
12      POST-STUDY      09/17/1960      RX       11      BS470      SANE      ANEYSA      2005      0      0      0      0      311      116      0      140      56      0      140      56      0      140      56      0      140      56      0      150      60      0      151      59      0      140      56      0      140      56      0      140      56      0      140      62                                                                                                                              
12      POST-STUDY      09/17/1960      RX       11      BS470      SANE      ANEYSA      2006      0      140      54      0      0      0      0      280      112      0      140      56      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0                                                                                                                              

IF YOU NOTICE THERE ARE 2 ROWS - ONE FOR 2005 AND ONE FOR 2006 , BUT i WANT TO SOMEHOW IMPORT THIS EXCEL FILE INTO ACCESS AND MAKE IT PRINT OUT IN ONE ROW INSTEAD OF HAVING 2 ROWS FOR EACH PATIENT:



NEW FORMAT WILL BE:

Status      DOB             Patient ID      Start Date      Payor Type      Prescribing MD Id      Prescribing MD Dea_id      MD Last      MD First      Jan-05                  Feb-05                  Mar-05                  Apr-05                  May-05                  Jun-05                  Jul-05                  Aug-05                  Sep-05                  Oct-05                  Nov-05                  Dec-05                  Jan-06                  Feb-06                  Mar-06                  Apr-06            
                                                      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent
POST-STUDY      10/28/1960      12      9/3/1997      INS-INDEMN      22      34G      FY      MARLA      0      56      56      0      56      56      0      56      56      0      56      56      0      56      56      0      56      56      0      56      56      0      56      56      0      22      30      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0


WHERE JAN1 OF OF OLD FORMAT UNDER YEAR 2005 IS NOTHING BUT THE COLUMN 0.5MG UNDER JAN-2005,
SIMILARLY JAN2 - 1.5 AND JABN3-50 (i HAVE JUST NAMED IT DIFFERNETLY IN THE 2 FORMATS , BUT IT IS THE SAME )


and the date will go on until apr-2006.

How do I achieve that???
0
Comment
Question by:Sara_j_11
  • 10
  • 3
  • 2
15 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 16668408
Hi Sara_j_11,

You are going to have to sort this out in XL.
Access is never going to accept 2 rows of headings coming from XL.

Pete
0
 

Author Comment

by:Sara_j_11
ID: 16668465
HOW TO DO IT IN EXCEL....
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16668544
I'm afraid that for me it is not possible to work out what your rows look like.  The layout anyone will see here depends on their screen size and resolution.
I suggest that instead of trying to show the complete row you show say, the first 3 and last 3 columns to see if that makes it any clearer.

Pete
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:Sara_j_11
ID: 16668574
JUST COPY THE THING THAT i HAVE UP HERE AND PASTE INTO EXCEL(SINCE ALL I DID  WAS TO COPY FROM EXCEL AND PASTE HERE):
ID     STATUS     DOB     CCTYPE     PHYSICIAN_ID     DEA_ID     LAST     FIRST     YEAR     JAN1     JAN2     JAN3     FEB1     FEB2     FEB3     MAR1     MAR2     MAR3     APR1     APR2     APR3     MAY1     MAY2     MAY3     JUN1     JUN2     JUN3     JUL1     JUL2     JUL3     AUG1     AUG2     AUG3     SEP1     SEP2     SEP3     OCT1     OCT2     OCT3     NOV1     NOV2     NOV3     DECEMBER1     DECEMBER2     DECEMBER3                                                                                                        
12     POST-STUDY     09/17/1960     RX      11     BS470     SANE     ANEYSA     2005     0     0     0     0     311     116     0     140     56     0     140     56     0     140     56     0     140     56     0     150     60     0     151     59     0     140     56     0     140     56     0     140     56     0     140     62                                                                                                        
12     POST-STUDY     09/17/1960     RX      11     BS470     SANE     ANEYSA     2006     0     140     54     0     0     0     0     280     112     0     140     56     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0                                                                                                        


0
 

Author Comment

by:Sara_j_11
ID: 16668603
COPY AND PASTE THIS INTO EXCEL:
OLD FORMAT:



ID      STATUS      DOB      CCTYPE      PHYSICIAN_ID      DEA_ID      LAST      FIRST      YEAR      JAN1      JAN2      JAN3      FEB1      FEB2      FEB3      MAR1      MAR2      MAR3      APR1      APR2      APR3      MAY1      MAY2      MAY3      JUN1      JUN2      JUN3      JUL1      JUL2      JUL3      AUG1      AUG2      AUG3      SEP1      SEP2      SEP3      OCT1      OCT2      OCT3      NOV1      NOV2      NOV3      DECEMBER1      DECEMBER2      DECEMBER3                                                                                                                              
12      POST-STUDY      09/17/1960      RX       13586      BS4700313      SANE      ANEYSA      2005      0      0      0      0      311      116      0      140      56      0      140      56      0      140      56      0      140      56      0      150      60      0      151      59      0      140      56      0      140      56      0      140      56      0      140      62                                                                                                                              
12      POST-STUDY      09/17/1960      RX       13586      BS4700313      SANE      ANEYSA      2006      0      140      54      0      0      0      0      280      112      0      140      56      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0                                                                                                                              
0
 

Author Comment

by:Sara_j_11
ID: 16668632
DEAR EXPERT!
USE THE DATA ABOVE FOR OLD FORMAT AND USE THE DATA BELOW TO UNDERSATND HOW THE NEW FORMAT SHOULD LOOK..  jUST COPY AND PASTE INTO EXCEL:
Status      DOB             Patient ID      Start Date      Payor Type      Prescribing MD Id      Prescribing MD Dea_id      MD Last      MD First      Jan-05                  Feb-05                  Mar-05                  Apr-05                  May-05                  Jun-05                  Jul-05                  Aug-05                  Sep-05                  Oct-05                  Nov-05                  Dec-05                  Jan-06                  Feb-06                  Mar-06                  Apr-06            
                                                      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent
POST-STUDY      10/28/1934      11      9/3/1997      INS      34      BS0684476      SHUMAN      MARLA      0      56      56      0      56      56      0      56      56      0      56      56      0      56      56      0      56      56      0      56      56      0      56      56      0      22      30      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0      0
0
 

Author Comment

by:Sara_j_11
ID: 16668663
IF YOU STILL HAVE PROBLEMS AND WANT JUST  FEW ROWS HEERE THEY ARE:
OLD FORMAT:
ID      STATUS      DOB      CCTYPE      PHYSICIAN_ID      DEA_ID      LAST      FIRST      YEAR      1-Jan      2-Jan      3-Jan      1-Feb      2-Feb      3-Feb                                                                                                                                                                                    
12      POST-STUDY      9/17/1960      RX       13586      BS4700313      SANE      ANEYSA      2005      0      0      0      0      311      116                                                                                                                                                                                    
12      POST-STUDY      9/17/1960      RX       13586      BS4700313      SANE      ANEYSA      2006      0      140      54      0      0      0                                                                                                                                                                                    
NEW DESIRED FORMAT:

Status      DOB             Patient ID      Start Date      Payor Type      Prescribing MD Id      Prescribing MD Dea_id      MD Last      MD First      5-Jan                  5-Feb                                                                                                                                                                                                                                                            
                                                      0.5mg      1.5mg      diluent      0.5mg      1.5mg      diluent                                                                                                                                                                                                                                                
POST-STUDY      10/28/1934      11      9/3/1997      INS      34      BS0684476      SHUMAN      MARLA      0      56      56      0      56      56                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                          
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16668674
I have just explained:
'The layout anyone will see here depends on their screen size and resolution.'

What you see on your screen is almost certainly not what I see on mine.
The number of rows in your pasted data depends varies based on screen size and resolution and also the size of the IE window.
I can get anything from 8 to 12 rows just by dragging my IE window around.

Pete
0
 

Author Comment

by:Sara_j_11
ID: 16669004
OK i AM GOING TO TRY AGAIN:
OLD FORMAT:
PATIENT    YEAR     JAN_0.5_STRENGTH    JAN_1.5_STRENGTH           JAN_50_STRENGTH      
12             2005        10                                   0                                    10      
12             2006       20                                  10                                   20

NEW DESIRED FORMAT:

PATIENT                  JAN-2005                       JAN-2006              
                            0.5   1.5   50                  0.5   1.5   50
                         
12                        10     0      10                20     10     20



iS THAT CLEAR NOW ... BUT IN THE REAL FILE:
IT WILL GO FROM JAN2005    FEB 2005 .... UNTIL TO APR2006

                   
0
 

Author Comment

by:Sara_j_11
ID: 16669082
DEAR EXPERT , CAN YOU PLEASE LET ME KNOW IF THE FORMAT IS CLEARER NOW...pLEASE ADVICE.
0
 

Author Comment

by:Sara_j_11
ID: 16670158
DEAR EXPERT , CAN U PLEASE ADVICE IF THIS IS POSSIBLE TO DO IN EXCEL?
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 16671561
Pete,

If you want to paste the above into Excel, you will need this small script:
__________________________________________________________________
javascript:(function(){ b=document.body; b.innerHTML = b.innerHTML.replace(/<br>&nbsp;(&nbsp;)/g, '<br> $1').replace(/\s*(&nbsp;){9}/g, ';;<wbr>').replace(/\s*(&nbsp;){4}/g, ';<wbr>') })()
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Select and drag to the address bar of your browser. You will then see more familiar semi-colon separated data, which you can copy and paste into Excel. Then choose "Data / Text to columns..." and expand the data into cells.


Hello Sara_j_11

I believe that the quickest way would be to ask an Oracle guru (perhaps at http:/Databases/Oracle/) to modify your query to obtain the desired result right away. Once the data is in Access, I guess you could do this:
• create a query filtering the 2005 data
• create another for the 2006 data
• link both in a third query, based on the patient ID
• renaming all the duplicate fields (e.g. JAN1 to JAN_05_1, JAN2 to JAN_05_2, etc.)
Thats a pain, basically.

You could also simply your query to obtain the data in a more vertical format. Something like:

ID      YEAR      MONTH      STRENGTH      MG
12      2005      JAN      s_05      0
12      2005      JAN      s_15      0
12      2005      JAN      s_50      0
12      2005      FEB      s_05      0
12      2005      FEB      s_15      311
12      2005      FEB      s_50      116
12      2005      MAR      s_05      0
…      …      …      …      
12      2006      JAN      s_05      0
12      2005      JAN      s_15      140
12      2005      JAN      s_50      54

[Now it's your turn to import this back to Excel ;) ]

In this form, it's much more manageable. This would even be a good base for a PivotTable in Excel. You could obtain it in a few minutes.

Finally, it's of course also possible to move data around in Excel, or – forthat matter – to write a program in Access that distributes the data to various cells while exporting the data. However, this would be a small project in its own right, well outside the scope of an EE question.

In a way, the job is half-done. You have a pivot table of sorts, but on a yearly basis. This is the worst state. Whichever program you choose to do the transform should do it all – Oracle, Access, or Excel.

Cheers!
(°v°)
0
 

Author Comment

by:Sara_j_11
ID: 16710851
Thanks expert  harfang for your suggestions.. I was able to use access and get most of it done. the last part that I am trying to achive is that I have 2 files each with the following columns:
patient_id     apr_0.5     apr_1.5

some of the rows for apr_0.5 do not match in the 2 files. I am trying to identify which are the patients where that column do not match . How do I find that out from access?
0
 

Author Comment

by:Sara_j_11
ID: 16710852
I have both the files in access.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16712867
Hello Sara_j_11

Both tables have a field ID, with unique numbers, right? You can find unmatched records using the built-in "find unmatched query wizard" which you get when you create a new query. The idea is, provided your tables are called Table2005 and Table2006:

    SELECT * FROM Table2005
    WHERE ID Not In (
        Select ID From Table2006
        )

Again, you should not have those two tables, but rather a much more vertical layout at this stage.

Good luck!
(°v°)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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