Forum Discussion

JoeCavasin's avatar
JoeCavasin
Brass Contributor
Nov 14, 2020

Access Output - Flexible Columns

Morning all,

 

Need assistance with an access report and the output format.  Currently, i pull a report of payments received for a given invoice.  I can pull either a total payment received (if i exclude the individual CPT's/line items) or i can pull a line item payment report.  However, i want the line item report option to export in a multiple column view as in the second format below.  The hard part is getting each new line/CPT within the invoice to move to a new column, and have the number of columns be flexible to adapt to the number of lines/CPT's on the largest invoice  in the report.  Currently the only way access will generate is in the first format, which i understand is based on how the data is stored in a line by line database setup.  Does anyone have experience with how to make this happen? 

 

Current Output - set number of columns     
ORIG_PAYOR_NAMEINVOICE_NUMCPT_CODEAMOUNT    
BLUE CROSS314360922021086-1774.92    
BLUE CROSS314360922099202-126.43    
Aetna314360923031000-1500    
Aetna314360923041000-125    
Aetna314360923051000-500    
        
Desired Output - flexible number of columns     
ORIG_PAYOR_NAMEINVOICE_NUMCPT_CODEAMOUNTCPT_CODEAMOUNTCPT_CODEAMOUNT
BLUE CROSS314360922021086-1774.9299202-126.43  
Aetna314360923031000-150041000-12551000-500

 

 

Thanks

Joe

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor

    JoeCavasin 

    How badly do you need/want this outcome? Enough to write a significant amount of VBA?

    You'll have to implement code that converts each payor's invoice into an array() 

    Then you'll need to figure out which payor has the largest number of CPT codes within any given invoice by counting the items in the array. Then you can probably generate a temporary table with that many columns. And finally, you can read items back out of the array into rows in that temp table, which you can then use as the report's recordource. In the report, you'll also have to write VBA to figure out how many columns will be needed and re-design the report accordingly.

    All in all, quite a lot of manipulation and coding, but certainly doable. Is it sufficiently important to warrant that effort? Only you can decide that. 

    Nonetheless, I might try my hand at it as an exercise, just to see what additional hurdles I haven't thought about yet.

    • JoeCavasin's avatar
      JoeCavasin
      Brass Contributor
      George Hepworth

      Honestly - considering i have to evaluate several thousand invoices at a single time it would be helpful to make it work. Typically, invoices don't go much above 5 lines/CPT's, worst i'm likely to deal with would be 10 lines/CPT's.

      That said - i have absolutely no VBA experience whatsoever. If doing it as an exercise with a 5 or ten CPT/line limit is a good starting point, i'd love to see the outcome. I have to escalate to payors monthly on behalf of our patients and this sort of single source report would make that work unimaginably simpler and more reliable.
      • George_Hepworth's avatar
        George_Hepworth
        Silver Contributor

        JoeCavasin 

        It sounds worthwhile then. Actually, while I was piddling around, another approach occurred to me that might be less work. If you need up to ten CPT codes, it might get crowded on a portrait view and even landscape could be close. Anyway, I'll see what I can come up with, based on your initial sample data, which I assume does reflect the underlying source table accurately. Gimme a couple of hours but do check back as others may have ready solutions at hand.

Resources