Query Problems

[PhilOfWalton]

I am trying to find the value of a particular share an different dates.

Obviously the price changes daily, and the quantity of shares held changes from time to time.

The 3 basic tables are

Investments
    InvestmentID   'Primary Key Autonumber
    SecurityName   'Name of Investment

BoughtSold
    BoughtSoldID    Primary Key Autonumber
    InvestmentID
    DateBoughtSold
    Balance

Valuations
    ValuationID
    InvestmentID
    ValDate
    SharePrice

All 3 tables are joined on InvestmentID

For simplicity I have set InvestmentID to 172 so that results can be examined

This is Query7 (which I will rename it when it is all working)

SELECT Investments.InvestmentID, 
Investments.SecurityName, 
BoughtSold.Balance, 
BoughtSold.DateBoughtSold, Nz(DMax("DateBoughtSold","BoughtSold","BoughtID < " 
& [BoughtID] & " AND InvestmentID = " 
& [Investments]![InvestmentID]),#1/1/1900#) 
AS PrevDate
FROM Investments INNER JOIN BoughtSold ON Investments.InvestmentID = BoughtSold.InvestmentID
WHERE (((Investments.InvestmentID)=172))
ORDER BY BoughtSold.DateBoughtSold;

This query gives me the balance when shares were bought or sold and also the date of the previous purchase /sale, so that the balance of shares held at each date that the share price was entered was known.
In the case of the first purchace, I have arbitrarily set the previous date to 01/01/1900.

This works perfectly

This is Query8

SELECT Query7.*, Valuations.ValDate, 
Valuations.SharePrice, 
[Balance]*[SharePrice]/100 AS Val
FROM Valuations 
LEFT JOIN Query7 
ON Valuations.InvestmentID = Query7.InvestmentID
WHERE (((Valuations.ValDate)<=[DateBoughtSold] 
And (Valuations.ValDate)>[PrevDate]));

The second query is giving the correct results other than it is not "picking up" information after the date of the last purchase /sale because there is no DateBoughtSold or previous date.

I am looking at ways of picking up those final entries. Somehow I feel that Query7 should have an additional line with the Previous Date set to the last date in the table, and an artificial DateBoughtSold set to 31/12/2099 (far in the future)

I would welcome any suggestions.

Phil

[PhilOfWalton]

OK, not to worry. Had to use a UNION Query to get valuations after the MaxDateBoughtSold.

Seems a bit complex for something that should have been easy.

Phil