Calculation Expression for Average

[Guest]

Hi,

I could be appreciated if you could help me on this:

I have create a query to append some fields onto a new table and create a
new Index field (Autonumber & primary key). I want to calculate the 14days
average closing price of a stock price. The fields of the table are:

1. [ID] - Autonumber & Primary Key
2. [Stock Name] - Text
3. [Close] - Number (Daily Closing price of the stock)
4. [Trade Date] - Date (Note: The trading date is not everyday affair, stock
mkt only open on weekdays)
5. Expr[14daysAvg] - Calculation expression which i have difficulty in
getting right
5. And other fields which I dun think it a concern over my question.

I want to calculate average 14days closing price for the stock. How could I
do it in Access2007?

Hmm, in programming lang, i used to write like this : [Close[ID]] +
[Close[ID+1]] + [Close[ID+2]] or create a FOR statement. But in Access, what
can i do?

Please help.

Thank

Nelson Chou

 

[John Spencer]

This could be done with a coordinated subquery, but you would need to fix
your field names (and table names) so that they don't require brackets to
delimit them. If you can't rename the fields in the table then build a
saved base query that does alias all of the fields to eliminate spaces in
the field names and use that as the source for this query.

SELECT T1.StockName
, T1.Close
, T1.TradeDate
, (SELECT Avg(T4.Close)
FROM YourTable as T4
WHERE T4.ID IN
(SELECT TOP 14 T2.ID
FROM YourTable as T2
WHERE T2.StockName = T1.StockName
And T2.TradeDate Between T1.TradeDate-20 and T1.TradeDate
ORDER BY T2.TradeDate Desc, T2.ID) ) as Running14Avg
FROM YourTable as T1

This will be slow.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..