It's not just a different layout that you need, but a related table. This can be table of all payments received both as cash and from insurance checks. These records can link to your current table to show the treatment date and other details, but with separate payment records for each payment received and a date received field, you can sort your records by month and then by payment type to get a sub total in cash, sub total of insurance payments as well as then a monthly total of the two combined. This requires a single summary field placed in different sub summary parts. your report can list the individual payments or just have one row for each each type of payment and its subtotal.
And here's a month calculation that can allow you to easily group payments by month:
PaymentReceived - Day ( PaymentReceived ) + 1
Select a date result type. It calculates a date for the first day of the same month as PaymentReceived so it gives all payments of the same month the same value so that sorting can group them together, yet sorts into correct chronological order.
I'm not sure I follow this though. I think that maybe I'm doing some of these things already but then definitely missing others. Here's what I have so far:
So, starting on the left upper part, that's the data I fill in every time a patient comes in. The left lower part is what I fill in when I bill insurance and when I receive an insurance check. All of the stuff on the right are calculations (determining credit card fee, paying my mentor (Russ), paying myself (Alex), and putting money into savings for taxes).
This is what I'm working with right now and am unable to do a sort where I can get the monthly totals for what I need to pay myself, my mentor, and my savings account (because insurance payments are being sorted by the Treatment Date (Tx Date) and not by the date the check is received (Ins Paid Date).
This is how the sort is set up at the moment. Please don't pay too close attention to the individual fields as I'm pretty sure some of them are correct and some aren't (and some are missing). I keep starting and stopping this project and it's a bit of a mess at the moment.
I was trying to do a leading sub summary sort for the cash and a trailing sub summary sort for the insurance, but this is not working correctly.
Finally, here's what I have for the relationships. I definitely do not feel confident in this!
I believe that your month calculation is what I have for the MonthSort field. The way I have MonthSort set up at the moment is this calculation: Month ( Tx Date_Pk )&" "&Year ( Tx Date_Pk ) I am very open to change though!
Not sure any of this makes sense or if what I have above is so off-base that you won't be able to make sense of it, but I appreciate all of your help.
Doesn't like what I am suggesting much at all.
The relationship between your table of patient visits would have exactly ONE match field on each side of the relationship and there should be a portal where all types of payments are entered on your layout.
Ok, thanks. I think I don't have enough background knowledge to understand this.