Calculate field between records

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have a table with 2 Fields, date and value.

the date is always Friday on each week and I want to
calculate the % change from prior week. i.e.

(valuethisweek - valuelastweek)/ valuelastweek

The result from the query should show date, value and
change% as the fields.

I have played around with subquery's but can't seem to get
anything working whatsoever.

Regards,
Michael
 
Try tying something like this as a calculated field in your query:

ValueLastWeek: (SELECT TOP 1 [MyValueField]
FROM [MyTable] AS Dupe
WHERE Dupe.[MyDateField] < [MyTable].[MyDateField]
ORDER BY Dupe.[MyDateField] DESC, ID )
 
Thanks Allen,

I would not have got this far by myself...The Date and
value fields are calculated already in this query as the
table hold daily data and I am using DatePart() function
to group by week ending friday and the value is the
average of these values for the given week.

Appreciate yuor help... :)

Michael
-----Original Message-----
Try tying something like this as a calculated field in your query:

ValueLastWeek: (SELECT TOP 1 [MyValueField]
FROM [MyTable] AS Dupe
WHERE Dupe.[MyDateField] < [MyTable].[MyDateField]
ORDER BY Dupe.[MyDateField] DESC, ID )

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table with 2 Fields, date and value.

the date is always Friday on each week and I want to
calculate the % change from prior week. i.e.

(valuethisweek - valuelastweek)/ valuelastweek

The result from the query should show date, value and
change% as the fields.

I have played around with subquery's but can't seem to get
anything working whatsoever.

Regards,
Michael
Click to expand...


.
Click to expand...
 
Back
Top