Multiple Payments for Events

[grep]

I'm trying to calculate a person's payment balance for a series of
events. I figured that what I could do is sum the prices for all the
events, and the payments made for all the events. Something like this:

sum([Price])-sum([Payments])

This works fine, as long as you make a single payment per event. The
problem is that once you throw a second payment into the mix,
Sum([price]) adds the original price back.

In other words: EventA costs $300. I make a payment of $150 for EventA,
and my calculation comes out to a balance of $150, which is correct. I
then add a second payment of $150, to cover the balance. Now I'm
screwed: Sum([Price]) now = $600 from which I'm now subtracting
Sum([Payments]), which is $300. My balance should be $0, but it's now $300.

So how do I get it to only use the price value once per event?


grep

 

[Jeff Boyce]

Without some idea of how your data is structured, we're also ... up the
creek.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[grep]

Yeah, I knew I wasn't writing that very well. <sigh> Let's see...

*tblEvents*
EventID*
EventName
Price

*tblCustPymts*
PymtID*
CustID
EventID
Payment
PayDate


Then I've got a customer form, with a subform for payments:
subfrmCustPymts, which uses a query to grab the following fields:
CustID
EventID
Payment
PayDate
Price

In the footer of subfrmCustPymts there are the following calculated fields:

TotalOwed which is sourced as =Sum([Price])
TotalPaid which is sourced as =Sum([Payment])

Does this make more sense?

grep

Without some idea of how your data is structured, we're also ... up the
creek.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to calculate a person's payment balance for a series of events.
I figured that what I could do is sum the prices for all the events, and
the payments made for all the events. Something like this:

sum([Price])-sum([Payments])

This works fine, as long as you make a single payment per event. The
problem is that once you throw a second payment into the mix, Sum([price])
adds the original price back.

In other words: EventA costs $300. I make a payment of $150 for EventA,
and my calculation comes out to a balance of $150, which is correct. I
then add a second payment of $150, to cover the balance. Now I'm screwed:
Sum([Price]) now = $600 from which I'm now subtracting Sum([Payments]),
which is $300. My balance should be $0, but it's now $300.

So how do I get it to only use the price value once per event?


grep

 

[grep]

Well, I guess I figured it out... sort of. I did a query and then a
couple of queries of the query, etc... and then a dlookup on the form...
It's not exactly pretty, but it's functional.

grep