-----------------------
Daily Table to which fldPrice query results will be posted:
--------------------------
Date fldOpenPrice fldHighPrice fldLowPrice fldLastPrice
--------------------------
Original QUERY which returns min and max for each minute
not each day.
---------------------------
PARAMETERS [pBegDate] DateTime, [pEndDate] DateTime;
SELECT fldTickDateTime, Min(fldTickPrice) AS [MinPrice], Max(fldTickPrice) AS [MaxPrice]
FROM SP1U
WHERE ((([fldTickDateTime]) Between [pBegDate] And [pEndDate]))
GROUP BY fldTickDateTime;
------------------------------
REVISED SUBROUTINE USING DateValue to extract date
from fldDateTime in order to try and GROUP BY Date
Returns aggregrate function error!!!
-------------------------------
Sub TestQuery()
On Error GoTo ErrorQuery
Dim qd As QueryDef
Dim qryName As String
Dim strSQL As String
Dim strParm As String
Dim rsQuery As Recordset
Dim TBLName As String
Dim BegLookUP As Date
Dim EndLookUP As Date
Dim junk As Integer
Set qd = New QueryDef
'qryName = "Query_UpDateDaily"
'qd.Name = qryName
TBLName = "SP1U"
strParm = "PARAMETERS [pBegDate] DateTime, [pEndDate] DateTime; "
strSQL = strParm & "SELECT fldTickDateTime, DateValue(fldTickDateTime) As TickDate, Min(fldTickPrice) AS MinPrice, Max(fldTickPrice) AS MaxPrice " _
& "FROM " & TBLName & " " _
& "WHERE ((([TickDate]) Between [pBegDate] And [pEndDate])) " _
& "GROUP BY TickDate;"
'Create a Temporary Query
Set qd = db.CreateQueryDef("", strSQL)
Are you running your code in Access or have a standalone app?
If in Access then I think the problem is in the GroupBy... I would make a separate SELECT statement that outputs TickDate instead of fldTickDateTime. Something like:
Let's Call this Query "tempMinMax"
*Use parameters wherever needed
SELECT DateValue(fldTickDateTime) AS TickDate, fldTickPrice FROM YourTable WHERE TickDate between ....
(I am not sure about the Where here, you may wanna use it in the second SQL instead)
And then Make a second SQL:
SELECT TickDate, Min(fldTickPrice) AS MinPrice, Max(...) AS MaxPrice FROM tempMinMax GROUP BY TickDate;
SELECT Min(MyTable.Field1) AS MinPrice, Max(MyTable.Field2) AS MaxPrice
FROM MyTable
WHERE (((MyTable.DateField)=#9/11/2001#));
Note US Date format
SELECT Format(MyTable.DateField, "mm/dd/yyyy") As DateOnly, Min(MyTable.Field1) As MinPrice, Max(MyTable.Field2) As MaxPrice
FROM MyTable
WHERE (((MyTable.DateField) Between #09/08/2001# And #09/11/2001#))
GROUP BY Format(MyTable.DateField, "mm/dd/yyyy");
Grouping by a formatted date field ensures that you group on the day only and not the time.
Cheers,
P.
Note that you could get the global maxima and minima by using
SELECT Min(MyTable.Field1) As MinPrice, Max(MyTable.Field2) As MaxPrice
FROM MyTable
just a check:
Can you group records by a field defined using the AS keyword
e.g. in your example could you do GROUP BY DateOnly ?
For me it always caused problems and I could only use it if this was a "supply" query for another one. So I am just curious.
THanks guys will try examples in a little bit and let you know.
Brandon: I'm using this from a standalone app not Access
as Access will not accept a variable table name with an imbedded query -- as least as far as I've been able to determine.
I am curious how you handle multiple SELECT statements in a row. If you have a example you can post it would assist myself and I'm sure others.
I am afraid you cannot use multiple select in Access. But maybe I am wrong but I don't think so. What I wrote was that you use 2 queries where one selects data from the other one:
Q2: SELECT fields FROM Q1
Q1: SELECT fields FROM Table
Doing this in a standalone application is impossible I think unless you put these queries into mdb file and call the "main" query. That would be one way of doing it and you wouldn't have to type all the SELECT ... in your code. Other way would be perhaps creating a temporary rcordset /Q1/ and then final recordset from the temporary /Q2/. But I have never done ... just thinking loud here
Got the query to mostly work as a stored query in Access97 -- See below
FYI: Originally got error "tried to execute the query that doesn't include the specified expression "Format (fldTickDateTime, "mm/dd/yy") as part of an aggregate function"
---------------------
Below works EXCEPT for instead of the DateOnly value being returned (ie. 09/06/01) I just get the literal "fldTickDateTime" in the DateOnly return field.
[Highlight=VB]
PARAMETERS [pBegDate] DateTime, [pEndDate] DateTime;
SELECT Format("[fldTickDateTime]", "mm/dd/yy") AS DateOnly, Min(fldTickPrice) AS MinPrice, Max(fldTickPrice) AS MaxPrice, First(fldTickPrice) AS FirstPrice, Last(fldTickPrice) AS LastPrice
FROM SP1U
WHERE ((([fldTickDateTime]) Between [pBegDate] And [pEndDate]))
GROUP BY Format("[fldTickDateTime]", "mm/yy/dd");
[/VBCode}
Since I plan to include this in code so I can pass various table names any suggestion on how to format the SELECT as a string with all the embedded quotes???
BRANDONSK
- I see what you are after with multiple selects.
I "ASSUME" once the recordset is returned from the 1st query,
you would use the results to generate the second recordset.
I am not sure but what type is your fldTickDateTime field? I assume it is Time/Date and the format should work. Also in your SELECT staement I got confused why do you have Format("[fldTickDateTime]", "mm/dd/yy") ... why is the [fldTick...] in " ??? If that field is causing you problems and you want to convert it to string then use Cstr([fldTick...]) but in the Format statement I would use CDate([fldTickDateTime]) just to make sure
Try it. I'll try also to create a sample db and test the query.
I am not sure but what type is your fldTickDateTime field? I assume it is Time/Date and the format should work. Also in your SELECT staement I got confused why do you have Format("[fldTickDateTime]", "mm/dd/yy") 1... why is the [fldTick...] in " ???
'----------
Field is DateTime
Using the format so query will return just a date instead of
a date time. Remember this query is searching the table and is using several functions (First, Last, Min, Max) to return the appropriate values. Currently I have only tested it with the beginning and ending date being the same -- so only one record is returned.
This may be why (???) the date isn't returned because if the search criteria includes multiple days, then which date should be returned since each of the functions might return a different date
for each of the functions. Whether this would generate 4 separate records or one I'm not sure. Will test.
When using a function in Access (here 97) examples show field must be in "[field]" format, otherwise per my earlier post it generates an aggregate function error.
'--------------
If that field is causing you problems and you want to convert it to string then use Cstr([fldTick...]) but in the Format statement I would use CDate([fldTickDateTime]) just to make sure
'------------
Haven't tried other functions but will test to see what happens.
If you are in a stand alone app, why not use four queries to return each of the things you want (max price and time/date, min price and time/date, first price and last price) and store in variables?
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
OK. Tried it, tested it - WORKS!!! (It worked also with Format function but since DateValue seems to be more "targeted" on what you want than let's use that).
Here is the SQL:
PARAMETERS BegDate DateTime, EndDate DateTime;
SELECT Max(DataTable.tP) AS MaxP, Min(DataTable.tP) AS MinP, (DateValue([DataTable].[fldTDT])) AS DateOnly
FROM DataTable
GROUP BY (DateValue([DataTable].[fldTDT]))
HAVING ((((DateValue([DataTable].[fldTDT]))) Between DateValue([BegDate]) And DateValue([EndDate])));
tp=TickPrice 'Long field in my DB
fldTDT=fldTickDateTime 'Date/Time field in my DB
Parameters are both Date/Time type
I'll try to attach the db file here (MS Access 97) so you can have a look there. There's also a module that I used to fill the table with. You only have to change system Date if you want more dates in the table
I have not tried to run it from external App but should be no problem.
Just do this:
VB Code:
Set DBFile=Workspaces(0).OpenDatabase("...\db2.mdb")
Set QD=DBFile.QueryDefs("Select Min Max")
With QD
.Parameters("BegDate")=whatever date
.Parameters("AndDate")=whatever date2
Set RS = .Execute
end with
*DBFile -> Database, QD-> QueryDef, RS->Recordset
And I am sure you know what to do w/ the recordset then ...
Downloaded your db, copied query to mine, made changes, and it worked. Thought query was slow but better than nothing.
I find it interesting that using DateValue with HAVING no errors are generated. When I copied the DateValue line in the SELECT and tried it with the last one I posted using WHERE, an aggregate error is generated.
I don't know about you, but I'm confused on this issue.
'-----------------
Ecniv
I don't disagree you could use 4 separate queries. Based on the time it takes with the combined query it may (have to test) be faster.
'------------------
'------------------
What I'm trying to do is develop several queries to roll price information from raw form to Daily Totals, then Daily Totals to Weekly, Monthly and Quarterly.
Most businesses need this for their accounting but have yet to find query examples for this.
BRANDONSK -THanks for all your efforts and for PaulW too for pointing us in the right direction. Keep me in mind as I will you if you stubble across some examples of rollups.
Hi. I am glad it works. It is slow perhaps because of how many times the DateValue function gets called for each record. If speed is essential I would use the two query example I suggested before.
Anyhow, I am not SQL pro and lately I got so lazy that I let Access to generate almost all the queries for me. I was also confused about the HAVING statement but your problem made it more clear to me. Again - I am not sure but this is what it seems to be:
When your recordset returns rows without using any agregate function (e.g. there is no GROUP BY statement) then to specify condition you must use WHERE.
However if you are using the GROUP BY statement then to specify condition you must use HAVING.
!!! As I am thinking about it now /and don't feel like erasing all the stuff / perhaps more precise definition is: For the fields you have included after GROUP BY -> you must use HAVING, however for the fields that are NOT part of the GROUP BY you must use WHERE.
Example:
SELECT DepID, Name, Max(Children) as MaxCh, Salary FROM Empl WHERE Salary > 1000 GROUP BY DepID HAVING Max(Children)<5;
Confusing maybe but should return for each DepartementID a name of an employee who has the most children but not more than 5... but the employees that will be compared will be only those whose salary is higher than 1000.
Guess I was too busy responding to your last post instead of doing my homework. Access help says it best (See HAVING).
[per access}
HAVING
Specifies which grouped records are displayed in a SELECT statement with a GROUP BY clause. After GROUP BY combines records, HAVING displays any records grouped by the GROUP BY clause that satisfy the conditions of the HAVING clause.
Syntax
SELECT fieldlist
FROM table
WHERE selectcriteria
GROUP BY groupfieldlist
[HAVING groupcriteria]
A SELECT statement containing a HAVING clause has these parts:
Part Description
fieldlist The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table The name of the table from which records are retrieved. For more information, see the FROM clause.
selectcriteria Selection criteria. If the statement includes a WHERE clause, the Microsoft Jet database engine groups values after applying the WHERE conditions to the records.
groupfieldlist The names of up to 10 fields used to group records. The order of the field names in groupfieldlist determines the grouping levels from the highest to the lowest level of grouping.
groupcriteria An expression that determines which grouped records to display.
Remarks
HAVING is optional.
HAVING is similar to WHERE, which determines which records are selected. After records are grouped with GROUP BY, HAVING determines which records are displayed:
SELECT CategoryID,
Sum(UnitsInStock)
FROM Products
GROUP BY CategoryID
HAVING Sum(UnitsInStock) > 100 And Like "BOS*";
A HAVING clause can contain up to 40 expressions linked by logical operators, such as And and Or.
[end Access]
Thanks again for your efforts. See I'm now going in right direction I'll see if I can speed up query as well as do groupings on Week, Month, Qtr, and Year.
Reply With Quote
