Subract sums of two queries from each other to get subtotal

You are very welcome! Happy Halloween to you too.

Cheers

>Okay. As long as you are aware of the issue. I just wanted to be sure it would not take you by surprise two months down the road ;-)

I do appreciate that :) I would rather think about it now than be slapping my forehead later!

Once again, you have been *exceedingly* helpful and delightful. Thank you so much, and Happy Hallowe'en!

>So ... change the workshop cost .. and you instantly change those totals.

The cost wouldn't change for the workshops that were already attended and marked paid. If we told them that the workshop was $12, it would stay $12. (This is a Catholic organization - very, *very* little changes around here ;D)

I do understand what you're saying about totals changing; mistakes are sometimes made - I recently set up something for invoicing, and when we printed and sent them out, one of the workshop sessions was incorrectly priced, so some of the invoices were wrong - but it was a simple thing to fix, since we're in constant communication with the schools (there are a little over 60 of them, but usually no more than 5 or so are represented at a workshop). In this case, we are offering them a check for the overage, or credit toward other workshops (which is another one of the reasons I'm trying to work out something that shows the funds left over, just in case). It happens very seldom, only once in the 3 years I've been here, and it only affected about three schools.

If something like this happened after we already applied payments sent, it would just mean (if the workshop changed from higher to lower) that the schools would have extra money to apply toward future workshops, or (if the workshop changed from lower to higher) if it was our mistake, we would probably eat it. They all will be sending teachers in the future, so it isn't a question of whether or not they'll want to take a workshop again!

>It sounds like it would probably work out in most cases, except if the check amount was wrong ;-)

If the check amount is wrong, there are a few different options. We can send the check back and request the correct amount, or we can keep the check, only record the correct amount (we are not accounting or finance so if I only record the amount of the check we're going to use it's fine) and send them a check for the overage. In this case I usually make a note in the 'Memo' field with the actual amount of the check.
Once again, I realize this wouldn't work for a "real world" application - but like I said, if I ever have the opportunity (and skill!) to develop something like that, I would take all of those things into account. I guess it would be good practice now to do it, but frankly, I don't have the luxury of time to worry about it. The workshops keep happening, the checks are piling up! and I'm also in charge of tracking and correcting the registrations, adding new teachers to the db, posting the workshops to the website, etc., etc. - and in the mean time, building something that will allow me to do this all from an interface instead of directly with the db.

>It would be easy to over-allocate check payments ...
>Do you actually have a way of distinguishing between checks that were already allocated...

I have a large pile of paper (registrations and copies of checks) with notes on them! :D The goal of this project is to be able determine the allocation with SQL. Unfortunately I am in the position of both having to do the work that the application I am building will eventually do, *and* building the application. The silver lining is that I think I'll build something very useful to the person who steps in after me (who will possibly not be a web designer, but more of an admin) because I will know what they will need to get the work done efficiently.

It seems like, in the long run, I wouldn't need the 'Paid' field, because I could use the 'WorkshopRegPaymentID' field to indicate payment. Until then, though, I need to keep it because in another part of this project, I'm displaying rosters that show 'paid' and 'completed' that involve that field.

I'm using MS SQL for the active database. I do some work with Access locally.

Sorry for the delay - was in a meeting this a.m.!

I wanted to keep the payments (check info) separate from the registration info, because sometimes the schools send checks to cover multiple workshop events. So that's why the check info would be linked through the registrant table (many-to-one relationship).

The final goal is to create a page where I will display all the unpaid registrations for a school, and also all the available funds that I could use to pay those registrations; I would select the registrations I want to pay for, then select the check I want to use to pay for them.

As I'm writing this, it occurred to me that I really do need, at that point, to show the funds available from each check, not the total amount, since I want to attribute specific payment records to various registration records. But the page you're helping me with is necessary to show that there is a certain amount of funds available from whatever combined check amounts not already used. At some point this will become available for viewing by the schools so that they can see if they have available credit due to overpayment or if someone doesn't attend who was paid for.

> FROM WorkshopRegistration as wr
> LEFT JOIN WorkshopRegPayment AS wrp ON
> wr.WorkshopRegPaymentID = wrp.WorkshopRegPaymentID

Hmm... on second thought ... I am missing a connection. If payments are only linked through the registrants table, then what is the difference between these two values:

> Subtotal the amounts of the checks per school. Add the amounts of the > paid registration fees per school

Oh dear - I just checked something I should have checked before -

I tried outputting just the check amount subtotals, and they're coming up goose eggs - so I need to play with this a little more. I will re-post the code when I figure out why.

That would be lovely. I'm all for simpler ways of doing things!

Glad it is working now.

If you do not need those extra columns, I am relatively certain there is a simpler query that could give you the totals. If I get a chance to review the sql in greater depth later, I may post some suggestions.

Cheers

> Still getting that pesky '"wr.WorkshopSessionID" could not
> be bound' error.)

> FROM WorkshopRegistration as wr, WorkshopRegPayment as wrp

You are missing the LEFT JOIN .. INNER JOIN ... syntax between those two tables. So the database is probably getting confused. Try reverting to what you had in your original query:

FROM WorkshopRegistration as wr
LEFT JOIN WorkshopRegPayment AS wrp
ON wr.WorkshopRegPaymentID = wrp.WorkshopRegPaymentID
....


But you still need to add COALESCE(..) around the columns used in the QoQ SUM (ie wrp_CheckAmount, wes_WorkshopSessionCost) to try and avoid the nullpointerexception.