double check a query to compare prices for yesterday and today

[JRough]

I want to compare todays opening price with yesterdays opening price
for 3 stock exchanges with an open price of >= $5.00.

First pass query I get all the stocks with a open price of greater
than $5.00 for yesterday:


select myTable.exchCD, myTable.permNo, my.Table.date, myTable.ticker,
myTable.excCD, myTable.Bidlo, myTable.AskHi, myTable.Vol, myTable.Ask,
myTable.OpenPrc
WHERE prc > $5
and date = datepart('d', (today())-1) as qryYesterday;

Second query I compare yesterday with today and get the top 20% of
stocks that dropped in price and are still greater than $5.00 because
I made it DESC sort I can get the top 20 % of those that lost value,
also I need to sort by 3 exchanges.

SELECT TOP 20 PERCENT myTable.exchCD, myTable.permNo, myTable.date,
myTable.ticker, myTable.excCD, myTable.Bidlo, myTable.AskHi,
myTable.Vol, myTable.Ask, myTable.OpenPrc WHERE myTable.openPrc > 5
ORDER BY excCD and myTable.openprc - qryYesterday.openPrc DESC;

thanks.

 

[Michel Walsh]

You are missing the FROM clauses.


SELECT *
FROM myTable
WHERE prc > 5 AND [date] =date() - 1


returns yesterday data. I assume there is only one record per 'stock/item'.
I also assume that your field [date] has only a date (or with time =
00:00:00, if you prefer).


SELECT *
FROM myTable
WHERE prc > 5 AND [date] =date()


returns today data.


Make a third query, bring the first two queries you just saved, join them
through their exchCD field (and stock/item field, if any). You can then use
arithmetic between fields of the different queries (used as tables), as it
fits your need, with the graphical query designer. You can definitively sort
on multiple fields/expressions.



Hoping it may help,
Vanderghast, Access MVP