Forum Discussion
Access Output - Flexible Columns
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.
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.
Thanks George. The initial sample data in the screenshot does represent the typical layout in the database. It is Invoice / CPT / Amount, and repeats until the number of CPT's on the Invoice are exhausted. If an invoice has 3 or 5 or 27 CPT's, it will repeat as below for the appropriate number of rows.
If the code you are thinking of could work for up to 10 CPT's per invoice, could we make it such that should it encounter more than ten CPT's on an invoice, it either:
A. retains the allowable 10 lines of data for that invoice
-or-
B. skips the invoice in the report output entirely - but key being that it doesn't cause the script to bomb out?
Thanks!Joe
Current Output - set number of columns ORIG_PAYOR_NAME INVOICE_NUM CPT_CODE AMOUNT BLUE CROSS 3143609220 21086 -1774.92 BLUE CROSS 3143609220 99202 -126.43 Aetna 3143609230 31000 -1500 Aetna 3143609230 41000 -125 Aetna 3143609230 51000 -500 JoeCavasin
Sorry for the delay. The first two approaches I tried turned out not to be very effective, but I re-grouped and came up with an approach that should be a good starting point. It relies on a procedure to convert rows in a normalized table into columns in a non-normalized table. In order to prevent excessive bloating, I incorporated a previously existing set of functions to create a "scratch" accdb which is deleted at the end. I found that a report in landscape view comfortably accomodated about a dozen CPT Code/Payment Amount pairs. I think you could reduce font size and squeeze one or two more in, but that is the upper limit. If you have to accommodate up to 20 pairs, it would be possible to split them out in a couple of different ways I guess, but I didn't try that.
See if this works for you.