Hi Tom,
Many thanks for your help and apologies for not making myself a little clearer!
The essence of my problem lies in trying to translate an actual query in the Access 2002 application into some code in a VBA module. I have a query that I have built using the 'Design view' mode in Access which works perfectly. I can view the results in 'Datasheet view' mode and it is exactly the output I want. But when I switch to 'SQL view' and copy the SQL code shown to use as the basis of a VBA procedure, it all falls apart.
The table that contains the actual data is laid out as follows:-
ID Ticker Date Open High Low Close Volume
1 NCP 22/12/03 10.16 10.20 10.01 10.03 1234567
2 NCP 23/12/03 10.06 10.17 10.06 10.17 2345678
... etc.
There are about 250000 records at the moment, for about 2000 tickers (i.e. different stocks), recording for each trading day the price at which the stock opens and closes, the high and low prices that it makes during the day, and the volume of shares traded.
I take the point about using Date (or for that matter Open, Close etc.) as field names but I might be stuck with it for the moment!
I have a form, also in Access, which lists all the available tickers (2000 odd) in a listbox down the left hand side. When the user clicks on a ticker, I want some code to retrieve all the price data for that particular ticker (e.g. "NCP") and then draw a chart of it on the right-hand side of the form. However, I also want to be able to select daily, weekly or monthly views. So, for example, in a monthly view (e.g. April 2003), the single record for that month would include the price it opened at on the first trading day of the month, the high and low it reached at any point during the month, the price it closed at on the last trading day of the month and the sum of all the volumes for each individual trading day in that month.
The following code extracts the DAILY data beautifully (where 'ChosenTicker' is a string variable containing the ticker that has been selected from the listbox (e.g. "NCP")):-
Dim rsPriceData As New ADODB.Recordset
strSQL_PriceData = "SELECT * " & _
"FROM Prices " & _
"WHERE (Prices.Ticker = " & Chr(34) & ChosenTicker & Chr(34) & ") " & _
"ORDER BY Date;"
rsPriceData.CursorType = adOpenStatic
rsPriceData.Open strSQL_PriceData, CurrentProject.Connection
'(CurrentProject.Connection.Provider = Microsoft.Jet.OLEDB.4.0)
...code here to go and draw the chart based on rsPriceData...
So far, so good - this bit all works. What I am having difficulty with is extracting weekly or monthly data from this table of daily price data. My idea was to create a new column containing the date formatted into the format "yyyymm" (e.g. 200312) and then GROUP BY that column to summarise the data into a monthly format. For each month, I need the date of the first trading day in that month (First(Date)) for labelling purposes, the first Open price, the highest high, the lowest low, the last close and the sum of all the volumes. As I say, it all works perfectly when you design a query on that basis in the Access application. But when you copy the SQL code shown in Access' 'SQL view' to the VBA procedure, the rsPriceData.Open method above fails. The SQL code looks like this:-
strSQL_PriceData = "SELECT First(Prices.Date) AS FirstOfDate, First(Prices.Open) AS FirstOfOpen, " & _
"Max(Prices.High) AS MaxOfHigh, Min(Prices.Low) AS MinOfLow, " & _
"Last(Prices.Close) AS LastOfClose, Sum(Prices.Volume) AS SumOfVolume " & _
"FROM Prices " & _
"WHERE (((Prices.Ticker)=" & Chr(34) & ChosenTicker & Chr(34) & ")) " & _
"GROUP BY Format([Date]," & Chr(34) & "yyyymm" & Chr(34) & ");"
My guess was that the Format([Date],"yyyymm") function was to blame but it sounds as if First, Last etc. might also be problematic. MIN(Open) doesn't really achieve the same effect: I actually do want the first open price for each month.
So I guess my question now is: how can I achieve the same effect in an SQL statement that I can then use in a VBA procedure to generate monthly (and weekly) data? I know I can write some code to go through the daily data and convert it into a weekly or monthly array but it's very slow and the task would be quite laborious! Since I can do it in a native Access query, I must be able to do it programmatically...
Many thanks once again for any help. I'm afraid I'm pretty new to database programming so I'm kind of stuck with this. Do shout if I can provide any further details.
Regards,
James.
----- Tom Ellison wrote: -----
Dear James:
I will assume that, by "Access query" you mean a Jet query. Also,
that you are converting to run with MSDE or SQL Server.
Everyone, please keep in mind that both Jet and MSDE are native to
Access for several years now.
You have used the First() function, which isn't available in ADO (SQL
Server). However, it is likely this function is not what you really
want anyway. Is it the case you want the earliest date and time value
in that column? If so, change it to MIN(), both in ADO and for
Access. The First() aggregate is pretty much guaranteed to give you
fits eventually if what you mean is MIN(). It will appear to work for
a while, then start giving you random results eventually.
I cannot see the purpose of formatting the Date in the GROUP BY. Does
this column contain some values with time as well as date, but you
want to ignore the time? If so, please try:
GROUP BY FLOOR(CONVERT(decimal(12,7), [Date])
I would caution you about the use of a column named Date, or any other
reserved words. Date() is a function in both Jet and MSDE, and
choosing some other name is a good idea.
I'm not sure what you have where you use ChosenTicket. That isn't in
the list you give for the columns in the query. From where does that
come? I'm thinking that may be something mysterious to ADO.
Like the First() function, using the Last() function is also a crap
shoot. This may not work reliably for Jet databases, and isn't
available at all for ADO.
I expect there's a lot more to it than this, but without seeing a bit
of sample data and the results desired from that, and perhaps some
explanation of how you expect it to work, it's a bit fuzzy to me at
this point.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Hi guys,
I am trying to convert an Access query to SQL so that I can use it in VBA as part of an ADO recordset.open command and I am running into a few problems that I would really appreciate any advice on!
The query deals with a database of daily stock prices (fields: date, open, high, low, close, volume). My Access query is designed to convert it into monthly data. I have designed something that works fine in Access itself and when I look at it in SQL view, the code is essentially as follows:-
SELECT First(Prices.Date) AS FirstOfDate, First(Prices.Open) AS FirstOfOpen,
Max(Prices.High) AS MaxOfHigh, Min(Prices.Low) AS MinOfLow,
Last(Prices.Close) AS LastOfClose, Sum(Prices.Volume) AS SumOfVolume
FROM Prices
WHERE (((Prices.Ticker)=" & Chr(34) & ChosenTicker & Chr(34) & "))
GROUP BY Format([Date]," & Chr(34) & "yyyymm" & Chr(34) & ");
All this works fine as a straight Access query but when I use this SQL as the basis of an ADO recordset.open in a VBA procedure, the method fails. The code works fine for other SQL queries, so the problem is definitely with the above text. I think it is down to the 'Format([Date],"yyyymm")' function which works in native Access but is presumably not part of SQL. If so, can anyone tell me how to achieve the equivalent effect in SQL, or is there some kind of identifier that will allow me to use Access/VBA functions in an SQL query? I am trying to do a weekly one as well, with the format string as "yyyyww", so I would appreciate any thoughts on that as well!
Many thanks for any help that anyone can offer and merry christmas to you all!
James.
