Subtract records from same field, different rows

[gdearka]

Hi there,
I have tried searching for a solution but to no avail. I am working on a query to determine the difference between the value of a mutual funds on a certain date compared to its previous day, in the past month and year-to-date.
We were trying this query for the 1-day change : (SELECT TheTime, TheNAV, TheNAV) - (SELECT Top TheNAV From tblFundsNAV WHERE T.TheTime < tblFundsNAV.TheTime ORDER BY TheTime Desc)
The query, however, did not work.
Thank you

 

[pbaldy]

See if this works for you:

http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[gdearka]

Many thanks Paul. We'll give it another try.

 

[pbaldy]

No problem, and welcome to the site by the way.

 

[mtonkins]

This is a very useful post. I have had a read of the information about sub-queries.

I have one question. I too am trying to create a query that calculates the difference between a field and its previous field. I used the code from the Sub query basics tip sheet.

Now, I keep running into this problem with an error message that states

'At most one record can be returned by this sub query'.

I cannot find any way around this.

The code I was using is as follows:-

 SELECT SurveyData.SurveyID, SurveyData.datetimez, SurveyData.mN - (Select Top 1 mN FROM SurveyData as Q WHERE SurveyData.SurveyID=Q.SurveyID AND Q.mN < SurveyData.mN ORDER BY Q.DateTimez ASC) AS dNcum
FROM SurveyData;

This code, as stated in the link provided by pBaldy basically calculates the cumulative amount,i.e the difference between the current record and the initial value (picked up by the SELECT TOP statement).

Can anyone help?