Hello Experts!
This question loosely relates back to a question previously that GRayL was helping me with. After not finding a solution to that particular problem; I have decided to take a different approach and unfortunately as to be expected this has presented quite a few obstacles. Fortunately however, I have been able to leap over the first few and have come to somewhat of a road block that I can't seem to get around.
It comes in the form of querying data.
The tables I am dealing with are as follows:
tblFund
=====
fundID pk
FundName
tblInvestment
==========
InvestmentID - pk
RegID - fk to tblRegistration
FundName - fk to FundID in tblFund
Units - units of stock owned
Date - if it was manually entered it'll have a date, if this record was a dividend it'll default to 12/30/1899
DivDate - if it is a dividend it will have a date, if not it'll default to the 12/30/1899 date
tblRegistration
===========
RegID - pk
RegName
tblStockDiv
========
StockDivID - pk
FundID - fk to FundID in tblFund
StockDate
StockAmount
Ok, so I've got a query that first compares the tblinvestment records with the tblStockDiv. If a dividend is in tblStockDiv it'll then select the associated tblInvestment records. After finding those records it compares the divdates to the stockDate in tblStockDiv. The query then actually displays the first (read: earliest) tblStockDiv that is available for each particular RegID and FundID.
The SQL is as follows:
SELECT Min(tblStockDiv.StockDate)
AS FirstStockDate, tblFund.FundID, tblFund.FundName, tblRegistration.RegName, tblInvestment.RegID
FROM tblRegistration INNER JOIN ((tblFund INNER JOIN tblInvestment ON tblFund.FundID = tblInvestment.FundName) INNER JOIN tblStockDiv ON tblFund.FundID = tblStockDiv.FundID) ON tblRegistration.RegID = tblInvestment.RegID
WHERE (((tblStockDiv.StockDate) Not In (SELECT a.DivDate FROM tblInvestment As a WHERE tblInvestment.RegID = a.RegID AND tblInvestment.FundName = a.FundName)))
GROUP BY tblFund.FundID, tblFund.FundName, tblRegistration.RegName, tblInvestment.RegID, tblInvestment.FundName, tblInvestment.RegID;
Ok, so that is done and is displaying correctly. So now what I'm needing to do is:
Take that FirstStockDate and find the SUM of the units owned up until that FirstStockDate that are in tblInvestment for that RegID and FundID.
After finding the sum of the units, I need to multiply that by tblStockDiv.StockAmount that is equal to the FirstStockDate (i.e. the stock amount for the correct FundID on that FirstStockDate).
I can't seem to figure out how to do that though. I've tried using subqueries, etc. and just can't seem to get the results I'm needing.
Any help on the query would be greatly appreciate!
Start Free Trial
