how to get several payment dates

[aceavl via AccessMonster.com]

Hi all!

i can't think of how to do this, mabey someone can help me.

i have a tbl employees (not our employees but employees from a company thet
we sold to)
i have the $ amount sold and the # of payments they have to do (credit) this
payments vary in that some are every 7 days others every 14, 15 or 30 days,
so say an employee bought $100 in Feb-02-2007 and has a credit of 8 weekly
payments:

EmpID = 1
EmpAmount = $100
EmpCredit = 8
EmpDays = 7
EmpDate = 02/02/2007

now i need to calculate very date we expect payment from them:

EmpID EmpNextPay1 EmpNextPay2 EmpNextPay3 EmpNextPay4 ETC...
1 09/02/2007 16/02/2007 23/02/2007
02/03/20074

and then i need to make a report of how much $ do we expect to colect in
between StartDate and StopDate.

the last part i can do but the way to get all those payments dates i just
don't see how unless i do a query with adddate() and get the first date and
then another query based on the first one to get the second date and so on....


anyone can help me please?

Thanks

 

[Michel Walsh]

Have an extra table, Iotas, one field, its primary key, iota, with values
from 0 to, say, 999.



SELECT EmpID, iif(iota=0, -EmpAmount, EmpAmount/EmpCredit), EmpDays,
EmpDate + iotas.iota * empDays
FROM originalTable INNER JOIN iotas ON originalTable.EmpCredit <=
Iotas.iota



will create 9 records, one for each date (the earliest date being
ith -EmpAmount, the other dates with +EmpAmount/EmpCredit ), for each date
where a transaction would occur.


To get the amount of money expected to be collected, over all empID, try
something like:


SELECT SUM(EmpAmount/EmpCredit)
FROM originalTable INNER JOIN iotas ON originalTable.EmpCredit <
Iotas.iota
WHERE (EmpDate + (1+iotas.iota) * empDays) BETWEEN startingDate AND
endingDate


( that does not include money getting out, and I changed the <= to a strict
<, also adjusting iotas.iota* empDays to (iotas.iota+1)* empDays )




Hoping it may help,
Vanderghast, Access MVP

 

[aceavl via AccessMonster.com]

Thanks, will try to implement it on monday and let you know if i could make
it work.

Have an extra table, Iotas, one field, its primary key, iota, with values
from 0 to, say, 999.

SELECT EmpID, iif(iota=0, -EmpAmount, EmpAmount/EmpCredit), EmpDays,
EmpDate + iotas.iota * empDays
FROM originalTable INNER JOIN iotas ON originalTable.EmpCredit <=
Iotas.iota

will create 9 records, one for each date (the earliest date being
ith -EmpAmount, the other dates with +EmpAmount/EmpCredit ), for each date
where a transaction would occur.

To get the amount of money expected to be collected, over all empID, try
something like:

SELECT SUM(EmpAmount/EmpCredit)
FROM originalTable INNER JOIN iotas ON originalTable.EmpCredit <
Iotas.iota
WHERE (EmpDate + (1+iotas.iota) * empDays) BETWEEN startingDate AND
endingDate

( that does not include money getting out, and I changed the <= to a strict
<, also adjusting iotas.iota* empDays to (iotas.iota+1)* empDays )

Hoping it may help,
Vanderghast, Access MVP

Hi all!

[quoted text clipped - 35 lines]

Thanks