Prorating based on dates

[John.Forr]

I'm having some trouble trying to figure out how to prorate a payment
amount based on dates.

My table structure looks like this:

Payperiods:
PayPeriodID (autonumber)
PayPeriodStart (Date)
PayPeriodEnd (Date)


PaymentAmounts:
PaymentID (Autonumber)
PersonID (FK)
EffectiveDate (Date)
PaymentAmount (Currency)

Each person will have multiple payment amounts. Each time their pay
changes, a new entry would be made with the effective date. For
calculating their net pay for a specific period though, I need to
prorate their pay amount if a change occured in the middle of the pay
period.

Also, two other things making this a tad easier: I only need to
calculate the prorated pay for the current payperiod. Another query
calculates the current payperiod which can be joined to the table
Payperiods limiting that table to just one record. The 2nd item: it
can be assumed that a particular person would only have their pay
amount change once within a single payperiod.

What I'm looking for would be the Prorated Pay Amount grouped by
salesperson and PayperiodID

 

[John.Forr]

Some more info about this problem:
I've broken down the problem like this:

PayPeriods:
------------------A--------------------------------------------------------B

Pay Amounts by time for a given person:
X-----------------------------------------Y---------------------------------------------------Z

A = PayPeriodStart
B = PayPeriodEnd

Since the query is grouped by PayPeriodID and is further being filtered
to just a specific PayPeriodID, A and B are known.

X = EffectiveDate(1)
Y = EffectiveDate(2)
Z = EffectiveDate(3)

X,Y, and Z are not known, and may not even exist for a particular
PayPeriod.

Mathematically, the result looks like this:
Prorated Pay = (Y-A)*PayAmount(X) + (B-Y)*PayAmount(Y)

I've tried, unsuccessfully, to create a query that would define X and
Y, but can't quite get the syntax correct. If I could just get those
defined the rest of it should be easy.