Dlookup and Other Query

[emblic]

I'm trying to build a database which keeps track of investment funds over time. At the end of each month the follwing info is captured: FundName, Date, Return - these are the most important. I have follwing tables: FundName(Name,id), Date(Date,id), FundData(lookup FundName,lookup Date, return).

Relationships-- FundName:FundData - One to many
Date:FundData - One to many

My next step is building a query where it creates an index for each fund. The logic behind this is that when you need the return over 60 months you just need the value of the index now and 60 months ago. You can then annualise.

When a fund starts the index starts at a 100. Each month it increases/decreases with the return ie if return is 5% then index = 100*(1+0.05) = 105. Next month return is -1.5% then index is 105*(1-0.015)=103.43.

My query has following fields which i get from a table (FundDataTbl): FundNameID, DateID, PortfolioRet. FundNameID, DateID both displays values which it looks up form FundNameTbl and DateTbl. From these fields I need to calculate the index.

My problem: How do I obtain the return corresponding to a fund for the previous month? I tried using the Dlookup method, but it keeps returning a null value.

It is: dlookup("[PortfolioRet]","FundDataTbl","[FundDataTbl]![FundNameID]=FundNameID AND [FundDataTbl]![DateID]=DateID-1").

DateID is a number so it is not necessary to use dateadd method.

Any Suggestions?


I'm including the relevant parts of the database. All data is dummy data.

 

[Jon K]

>
How do I obtain the return corresponding to a fund for the previous month?
<

PrevMonthRet: DLookUp("[PortfolioRet]", "FundDataTbl", "[FundNameTbl_ID]=" & FundNameTbl_ID & " AND [DateTbl_ID]=" & [DateTbl_ID]-1)


To calculate the Index, you will need some VBA code to loop through the records.

 

[emblic]

Jon you are my HERO!!! Although it was a simple syntax thing in the end it would have taken days for me to figure ot out. This forum really kicks ... Anyway I took it a step further and my index works. Only thing is that it is a bit slow. My code:

Index: IIf(IsNull([PortFolioRet]),Null,IIf(IsNull(DLookUp("[PortFolioRet] ","FundDataTbl","[FundNameTbl_ID]=" & [FundNameTbl_ID] & " And [DateTbl_ID]=" & [DateTbl_ID]-1)),100*(1+[PortFolioRet]),(DLookUp("[Index]","IndexStartQry","[FundNameTbl_ID]=" & [FundNameTbl_ID] & "And [DateTbl_ID]=" & [DateTbl_ID]-1))*(1+[PortFolioRet])))

I tested it and it works, month after month. If you have any alternative suggestions to above I'd appreciate it otherwise all I can say is THANK YOU.

 

[Jon K]

Tom. Brilliant solution! Never thought you could do it with a query. You have proved me wrong.

I couldn't run your Index expression when I copied it to your query "IndexQry". I think it is because I don't have the query "IndexStartQry", which I believe is the key to your solution. Could you attach your DB? I am eager to learn. Thanks.

 

[emblic]

Sorry Jon,

My db looks bit different to the one I posted here on the forum. I had to adapt it because it is large. I corrected the query to the db posted here.

Like I said it is a bit slow(so far i have 11000 records) but it works. I think it will only work in some instances ie when the dates are stored in ascending order etc.

Cheers