Forum Discussion
Access Output - Flexible Columns
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.
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.