I mocked up a table to simulate your set up.
Table Values
id |
portfolio |
asofdate |
pmv |
1 |
abc |
01-Jul-99 |
0.2 |
2 |
abc |
03-Jul-99 |
0.31 |
3 |
abc |
09-Aug-99 |
-0.09 |
4 |
abc |
21-Sep-99 |
0.19 |
5 |
abc |
29-Oct-99 |
-0.4 |
6 |
abc |
21-Sep-99 |
-0.06 |
Query sql
Code:
SELECT PortfolioTrans.portfolio, PortfolioTrans.asofdate,Round( (PortfolioTrans.pmv *100),2) as trans,
iif(trans >0, "Bought","Sold") as TransType
FROM PortfolioTrans
WHERE (((PortfolioTrans.asofdate) Between #1/1/1999# And #31-dec-99#))
ORDER By asofdate,pmv
Result
portfolio |
asofdate |
trans |
TransType |
abc |
01-Jul-99 |
20 |
Bought |
abc |
03-Jul-99 |
31 |
Bought |
abc |
09-Aug-99 |
-9 |
Sold |
abc |
21-Sep-99 |
-6 |
Sold |
abc |
21-Sep-99 |
19 |
Bought |
abc |
29-Oct-99 |
-40 |
Sold |