How to get the remaining balance of payment to be made for item in Query

[knightslacker]

Hello Everybody



This is my first post. Can you please help me with the following problem:

In my database I have certain defined milestones, each having a certain amount of money allotted as its cost.

This cost is released in several steps over time.
1. Sometimes the cost is released as an absolute amount,
2. sometimes the released amount is described as a percentage of the allotted cost and
3. sometimes as a percentage of the payment yet to be released.

i.e. say, if the cost of milestone A1 is $10,000

i may release $100 on 1 Jan
then 5% of $10,000 on 7 Jan
and then 20% of whatever is the balance payment
and so on, in no particular order, until the entire cost is paid

My inputs are the milestone number, amount released (either [%] or [absolute] or [% of balance]) and date of release
The payments for many milestones can also be released simultaneously (either with the same amounts or differing amounts)

Please tell me how to construct a query such that i get a tabulation sorted by milestone number and then sorted by date and an accompanying column indicating the amount of payment left after that particular date's payment.

for eg. something like below,

Milestone no. Date Amount Balance
A1 1 jan 1000
A1 3 jan 500
A1 7 Jan 200
A2 1 jan 10000
A2 3 jan 4000


If the problem is not clear please ask me, I have also attached a small demo file.

[rpeare]

1. Do not use multi value fields, they are a bad, bad, bad idea
2. Don't try to do this sort of thing in a query, particularly if this database is any larger than tiny. It will involve a lot of domain functions (dsum, dcount, etc) If you're looking to create a report you should be able to do this on a report (running sums)
3. You can design your data entry to calculate actual deduction at the time of data entry rather than trying to figure it on the fly as well.

The real sticking point in this (I don't know if someone has an elegant solution that does not involve domain functions) is the % of balance the other two are easy to figure out in a running sum report. The best solution I see is to modify your data entry to figure the amount of disbursement for you, you can still collect the information on which type of disbursement it was but it will make it loads easier on you to do it at the data entry point rather than reporting/query stage.

[knightslacker]

@rpeare

Do you mean that the query field that I want should actually be a column in the [Released payments] table?
In that case, is it possible to enter a calculated value using form-based entry into that column.
If yes, any pointers on how to do that would be very much appreciated?

[orange]

Further to rpeare's comments (with which I agree):

It seems there is something missing in your set up --to me anyway.
Your tables show a 1:1 Relationship.

When I think of Milestones and Payments, I think of Projects (or Work).

A Project can have 1 or Many Milestones
A Payment is made when a Milestone is reached.
Any Payment may be 1 of Many PaymentTypes.

Good luck with your project

[rpeare]

Yes, that's what I'm suggesting, having another field in your RELEASED PAYMENTS table (say ACTUALVALUE for instance) that could be calculated at the time of data entry. You don't actually need 3 RELEASED COST fields you have, those can be unbound controls on your form (like an option group) but you can certainly store them if you want though I would be inclined to store it in 2 fields rather than 3 with one of those fields always containing a dollar value and the second indicating whether it was a % of total allotment or % of balance (after all if it's neither of these it's a flat allocation) It's relatively painless to get remaining balances on a form/subform if there's an actual value you can add/subtract and from there equally painless to get a 'remaining balance'. I tend to make all my forms unbound controls but it can be done with bound forms too.