DISTINCT or TOP query problem

[Maxie]

I've got a problem for you Access/SQL gurus out there....
And my goal is to solve it using only the built-in
functions of Access 2000.

This problem involves Table1 with these columns: date,
ticker, close. This table is updated daily with the
closing prices of various stocks.

I am trying to develop a query that prompts me for a date
(we can assume that the date entered will be valid, in all
respects) and then proceeds to give me the 3 day moving
average for each ticker in Table1.

The 3 day moving average is calculated as follows: for Day
4, it would be: (Day 4's closing price + Day 3's closing
price + Day 2's closing price)/3.

The 3 day moving average for the next day (Day 5) would
be: (Day 5's closing price + Day 4's closing price + Day
3's closing price)/3.

I don't think this can be done with operations performed
on dates, since stocks don't trade on Saturdays, Sundays
nor on some holidays and thus don't have prices on those
days. I believe a better approach would be to use the TOP
function or something similar to get the last 3 records
for each ticker. But I can't figure out how to do this
correctly.

Just in case it might help....here's what I have so far....

SELECT Sum(close), Avg(close), inputdate, ticker
FROM table1
where date in (SELECT top 3 date from table1 WHERE date <=
inputdate ORDER BY date DESC)
GROUP BY ticker;

This works when I have only 1 ticker in the table. But
when I add 2nd ticker w/ its corresponding price info,
then the query won't work unless I change the "3" to a "6".

Obviously, I'd like to make it so that the query's logic
is independent of how many tickers I have in the table.
(So, I can add stock information without having to worry
about updating the query.)

Ideally, the output would look something like this:

InputtedDate Ticker1 Ticker1MovingAverage
InputtedDate Ticker2 Ticker2MovingAverage
InputtedDate Ticker3 Ticker3MovingAverage
etc...

Also, again I'd like to stay away from creating new
functions, procedures, etc. I think this should be able
to be done with the native operators and functions of
Access.

Thanks in advance for any help,

maxie3

 

[Larry]

-----Original Message-----
I've got a problem for you Access/SQL gurus out there....
And my goal is to solve it using only the built-in
functions of Access 2000.

This problem involves Table1 with these columns: date,
ticker, close. This table is updated daily with the
closing prices of various stocks.

I am trying to develop a query that prompts me for a date
(we can assume that the date entered will be valid, in all
respects) and then proceeds to give me the 3 day moving
average for each ticker in Table1.

The 3 day moving average is calculated as follows: for Day
4, it would be: (Day 4's closing price + Day 3's closing
price + Day 2's closing price)/3.

The 3 day moving average for the next day (Day 5) would
be: (Day 5's closing price + Day 4's closing price + Day
3's closing price)/3.

I don't think this can be done with operations performed
on dates, since stocks don't trade on Saturdays, Sundays
nor on some holidays and thus don't have prices on those
days. I believe a better approach would be to use the TOP
function or something similar to get the last 3 records
for each ticker. But I can't figure out how to do this
correctly.

Just in case it might help....here's what I have so far....

SELECT Sum(close), Avg(close), inputdate, ticker
FROM table1
where date in (SELECT top 3 date from table1 WHERE date <=
inputdate ORDER BY date DESC)
GROUP BY ticker;

This works when I have only 1 ticker in the table. But
when I add 2nd ticker w/ its corresponding price info,
then the query won't work unless I change the "3" to a "6".

Obviously, I'd like to make it so that the query's logic
is independent of how many tickers I have in the table.
(So, I can add stock information without having to worry
about updating the query.)

Ideally, the output would look something like this:

InputtedDate Ticker1 Ticker1MovingAverage
InputtedDate Ticker2 Ticker2MovingAverage
InputtedDate Ticker3 Ticker3MovingAverage
etc...

Also, again I'd like to stay away from creating new
functions, procedures, etc. I think this should be able
to be done with the native operators and functions of
Access.

Thanks in advance for any help,

maxie3
.
iif(format([inputteddate],"w")=2,(inputteddate)+

(inputeddate-3)+ (inputeddate-4),iif(format
([inputteddate],"w")=3,(inputteddate)+(inputeddate-1)+
(inputeddate-4),,(inputteddate)+(inputeddate-1)+
(inputeddate-2))/3
This is assuming you first day to the week is set to sunday

 

[Maxie Maultsby]

John, Thanks! That worked!...but I'm not sure why.

Does the DISTINCT send over multiple sets of 3 records
each???

maxie3

 

[John Verhagen]

Try cutting and pasting the SQL of the subquery into it's own query, and you
will see that 3 dates appear. The 'IN' keyword is what will pick the
correct dates from the table essentially saying, "give me all records with
the following 3 dates".
Without the distinct, it would give 3 dates, but if there were lots of dates
that were the same, it would give the same date 3 times.

In your case, with 2 stocks in the table, it gave the same date 2 times and
the next date once. And that is why when you replaced 3 with 6, you got
what you wanted. Each date was repeated twice.