Access97 Query - Still Stuggling$#@%

Hope someone can help me solve this.

Also why won't Access 97 accept a function in the SELECT OR
what am I doing wrong?

PROBLEM:
Need min and max price for each day. Receiving Min and Max prices for each minute.

Ultimate objective is to obtain:

a. The first price of the day
b. The last price of the day
c. The highest price of the day
d. The lowest price of the day

-------------------
SAMPLE TABLES
--------------------

Date/Time fldTickPrice

8/31/00 08:00:00 Am 20.75

8/31/00 08:00:04 Am 20.65

8/31/00 08:00:25 Am 20.65

8/31/00 08:03:00 Am 25.50


-----------------------
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)

BegLookUP = #9/6/01 12:00:01 AM#
EndLookUP = #9/6/01 11:59:59 PM#

qd.Parameters("pBegDate").Value = BegLookUP
qd.Parameters("pEndDate").Value = EndLookUP

Set rsQuery = qd.OpenRecordset

With rsQuery
'See if Query Table is Empty
If .EOF And .BOF Then

'Not found
junk = 1

Else
' Call FoundSelected(qd)
junk = 1
End If

.Close
End With
Set rsQuery = Nothing

' Exit Sub

ErrorQuery:
End Sub


Thanks
David

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;

Should work this way... Let me know if not. ;)

The following queries work for me:

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

No Grouping but it works!

P.

paulw is the best ... :D

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.

BrandonSK and PaulW:

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.

Thanks
David

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 :)

PaulW

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.

Thanks
David

Oh,

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.

BrandonSK

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.

'------------


Thanks
David

I know this is going seem a thick question but...

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?

Just wondering....

Vince

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 ... :D

Any problems ... tell me.

Quote:

---

Originally posted by BrandonSk

.Parameters("AndDate")=whatever date2

---

Sorry... typo ... "EndDate" of course :D :D :D
.Parameters("EndDate")=whatever date2

BrandonSK

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.

David

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.

Can somebody confirm me on this? :D

Thanx.

BrandonSK:

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.

David