Access problem- Milestone partial payments & Payments made/forecast summary

[basil]

I am relatively new at the whole access thing but i am trying to
develop a database for an investment fund however I have run into a
slight problem trying to figure out how to do a combined payments
made/payments forecast summary by month (based in a form using dlookup
on a query).

I have Milestones belonging to a contract which belongs in turn to a
project.
Each milestone has a $ figure and a due date attached to it and can
have multiple payments associated with it (through a linked table)
which have a $ figure and a payment date.

What i am trying to do is set up a calc (in a query) so if the
milestone has been paid in full the payment summary takes the payment
amount and the payment date, if no payment is made then it takes the
milestone amount and milestone due date. I can do that much, the
problem is when a milestone has a partial payment i want to include the
payment amount and payment date as well as the remainder of the
milestone due with the milestone due date in my payments made/forecast
form.

Explained another way.
milestone amount = x milestone date=xd payment amount =y payment
date =yd

if x=y THEN y & yd
if y is null THEN x & xd
if x does not equal y and y is not null THEN y&yd AND (x-y) & xd

I am probably overlooking something obvious but does anybody have any
ideas about how I could tackle this problem, i have hit a bit of a
brick wall.

Thanks in advance

Desmond

 

[tony]

Hi
I'm not sure of exactly what you are doing
It appears that you need a payment table
there would need to be a link( some form of ID number for each
milestone project..
Then you could add up the payments)(partial or complete) and calculate
what you need to calculate.

Something to think about ... maybe???

HTH
Tony