Complex Query Sorting and Summing Amounts Breakdown

[Pooks_35]

Hopefully someone will be able to see something that I'm not after two days of trying to solve this problem. I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID. What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client. Does anyone have any idea how to do this? Thanks for any help you can provide me as I've been working on this for the last two days with no success!

 

[plog]

I have a shell of my database...

50 MB is a shell?

How about you post just the objects necessary for this issue?

 

[Pooks_35]

plog,
I whittled it down, I just did a compact and repair and it decreased the size for me further. Hopefully this will work now.

 

[plog]

I'm looking solely at your relationships. There should only be 1 path between objects, you have a spider web. You really need to rethink how each table relates to the others. You have too many relationships.

I really think it springs from your level of grainularity. Why must payments be allocated to a specific charge? Why can't you just have a balance and subtract it from that?

 

[Pooks_35]

Hi plog - I understand what you are saying about the relationships. As far as the payments, they have to be applied to specific charges as each charge is specific to a specific week and each payment is specific to a specific week, thus a specific charge, as well.

 

[plog]

each payment is specific to a specific week

That's not what you initially said. You said a payment may go to multiple charges. Further, why? Why do you need it at that level? When you make a credit card payment your payment doesn't get allocated to a specific time you charged food at McDonald's, it goes against the balance.

 

[Pooks_35]

Hopefully this will give you a better idea of why it needs to be done in this way:
We have campers, who each have an CamperID number, each camper can sign up for multiple sessions of camp, thus a separate charge (or ChargeID) is assigned for each session as sessions may have different costs. Here's the tricky part, there are several types of payments that can be applied to each charge as well as multiple payments over a period of time. A payment made by the individual (or family) is considered a private pay and receives a PaymentID number, many times thought a third party is making the payment, so the charge has to be moved to a separate table so that the family's balance is zeroed out and the charges are now moved to the third party payer. The system in place creates a PaymentID when this charge is moved and it is categorized as a Transfer and the charge is assigned the correct third party payer ID. Once a payment is made to a specific charge in by the third party payer, that charge (even if partial) is assigned a TPPaymentID. So at this point I'm stuck with a system that assigns a payment id to every payment and also to every charge that is moved to the third party payer system. The only identifier separating the transfer from the payment is the PaymentMethodType (i.e. Transfer, Third Party, etc.). Even if you can provide me with a different aspect as to how to pull together a query to make the report I need, that would be great, I don't necessarily need an answer to how to fix this current query, but ultimately I just need to figure out how to create the report I need.

 

[plog]

The main problem you need to focus on is your improper relationship. It cannot be a spiderweb. Everything cannot be connected directly to everything else.

My suggestion is to start to rebuild your relationships. Start by brining in the most important table. Then adding tables, 1 by 1, remembering that you cannot create multiple paths between tables.

Work on your relationships.

 

[Pooks_35]

Thanks. I figured I would have to start from scratch, I didn't create the database, but I'm trying to repair it. Hopefully I'll be able to fix it so that it works for what we need!

 

[plog]

I'm not saying start from scratch. I'm saying recreate your relationships. That might mean reassigning some fields to other tables, creating some junction tables, etc.. But I'm sure the guts of what you have is good, just not the relationship they are in.