Nov 14 2020 08:13 AM
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_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 | ||||
Desired Output - flexible number of columns | |||||||
ORIG_PAYOR_NAME | INVOICE_NUM | CPT_CODE | AMOUNT | CPT_CODE | AMOUNT | CPT_CODE | AMOUNT |
BLUE CROSS | 3143609220 | 21086 | -1774.92 | 99202 | -126.43 | ||
Aetna | 3143609230 | 31000 | -1500 | 41000 | -125 | 51000 | -500 |
Thanks
Joe
Nov 14 2020 09:10 AM
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.
Nov 14 2020 09:33 AM
Nov 14 2020 09:40 AM
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.
Nov 16 2020 11:50 AM
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 |
Nov 17 2020 10:28 AM
@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.