SQL Stock Query - Summing fields to use in Multiplication

[kabradley]

Hello all!

I have a logic problem that I have been working on for quite some time now and can't seem to fix. Hopefully someone can shed some light on the subject.
I am starting to work on an access database that will hold stock data. The database needs to be able to do the following:
Compute the correct amount of stock share dividends that each individual receives, at runtime.

Let me explain further. There are 3 tables:
tblFund
=====
FundID
FundName
FundPrice

tblReg
====
RegID
RegName

tblInvestment
=========
InvestmentID
RegID - fk
FundID - fk
InvestDate
UnitPurchase (how many shares they bought/sold)

These three tables make up the buying / selling side of things.
To handle the dividends I have the following:

tblDividend
========
DivID
FundID - fk
DivAmt
DivDate

The DivAmt is based on 1 unit (and unfortunately this is the way it must be done, i.e. the dividends for each person cannot be entered separately). So, if a stock distributes .5 shares per 1 unit. Then a registration that ownes 4 units would receive 2 units, making the grand total shares owned 6.

The problem I am having is the SQL in the query to retrieve this information.
I'm needing a query that would render similar to the following:

FundID RegID   Date        OrginalPurchase  TotalDivAmt     TotalUnitsOwned
1             1     11/14/05          10                   20                       30
1             1     12/15/05          10                   60                       90
1             1     1/14/06          10                     45                      135
 

Edit: I can't get the info to display properly but, it should read 20, 60, 45 for totaldivamt and 30, 90, 135 for Total Units Owned.

The 1 Unit Dividends for the above table in order were 2, 2, and .5

As you can see what happens is the share dividends the person receives increases their units owned. This new total of units owned then needs to be used to calculate the next dividend amount. And so on and so forth.

Using a previous rows sum is what seems to really be giving me the trouble. I'm not sure if this is possible, or what but that is essentially (at least from what I can tell) I am needing to do.

Any suggestions on how to do this, or another way of approaching the problem would be great!

Thank you all in advance.

[MMcCarthy]

Will there be more than one dividend per fund?
Will there be only one fund per dividend?
Can an investment refer to more than one fund?

[kabradley]

Will there be more than one dividend per fund?
Will there be only one fund per dividend?
Can an investment refer to more than one fund?

Yes, typically a dividend will occur once a month.
The dividend would refer specifically to a particular fund.
The investment has the particular RegID (person purchasing), FundID (particular fund they are purchasing), UnitsPurchased (how many they are buying), and Date (Date transaction took place).

So, to answer your third question, yes...? A person (i.e. RegID) can invest in multiple funds (i.e. FundID) and can invest in the same fund multiple times (i.e. purchase more shares or sell off shares).

Hopefully that helps :)