UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL - Rollup Data To Another Table By Between Date, Access 97    
 
   
dw85745
post Jul 31 2019, 11:10 PM
Post#1



Posts: 103
Joined: 29-September 10
From: AZ


I'm trying to RollUp Data (see code below) from a Shorter Period to a Longer Period
Executing the following query "appears" to return the correct information for
First, Max, and Min but --- NOT --- Last.

Reading SQL docs, it appears First and Last are similar to .MoveFirst and .MoveLast
but MAY return an arbitrary value. Which appears to be happening in my case.

CODE
PARAMETERS [pDateBeg] DateTime, [pDateEnd] DateTime;
SELECT FIRST(fldHistOpen) AS fldOpen, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow) AS fldLow, LAST(fldHistClose) AS fldClose
FROM Sales1
WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd];


I'm wondering how others handle RollUps ?
Only thing I can think of is to do a Select Query for all the inclusive records to be Rolled Up, ordering by date.
Then run the above posted code against that Query.
Whether First and Last will then work and return the correct result is unknown.

Any suggestions appreciated.



Go to the top of the page
 
cheekybuddha
post Aug 1 2019, 05:39 AM
Post#2


UtterAccess VIP
Posts: 11,469
Joined: 6-December 03
From: Telegraph Hill


FIRST and LAST will be arbitrary if there is no ORDER BY specified in the query.

First thing to try is adding a suitable ORDER BY clause to the query to see if it helps any.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
dw85745
post Aug 1 2019, 06:04 AM
Post#3



Posts: 103
Joined: 29-September 10
From: AZ


cheekybudda
thank for responding.

I previously tried putting in an ORDER BY clause but Access would Not accept the SQL as written.
I tried:
This failed on "You tried to execute a query that doesn't include the specified expression (fldHistDateTime)"

CODE
PARAMETERS [pDateBeg] DateTime, [pDateEnd] DateTime;
SELECT FIRST(fldHistOpen) AS fldOpen, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow) AS fldLow, LAST(fldHistClose) AS fldClose
FROM Sales1
WHERE(fldHistDateTime)  BETWEEN [pDateBeg] And [pDateEnd]
ORDER BY (fldHistDateTime) ASC;



I then tried adding in (fldHistDateTime) in two different ways just after SELECT,
1) (fldHistDateTime) AS ThisDate, and
2) (fldHistDateTime)

Access failed both again with: "You tried to execute a query that doesn't include the specified expression ...)"

CODE
PARAMETERS [pDateBeg] DateTime, [pDateEnd] DateTime;
SELECT (fldHistDateTime) AS ThisDate, FIRST(fldHistOpen) AS fldOpen, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow) AS fldLow, LAST(fldHistClose) AS fldClose
FROM Sales1
WHERE(fldHistDateTime)  BETWEEN [pDateBeg] And [pDateEnd]
ORDER BY (fldHistDateTime) ASC;


Hence my post in the forum.
This post has been edited by dw85745: Aug 1 2019, 06:06 AM
Go to the top of the page
 
cheekybuddha
post Aug 1 2019, 06:39 AM
Post#4


UtterAccess VIP
Posts: 11,469
Joined: 6-December 03
From: Telegraph Hill


Since I have no idea what your table represents and what data you are trying to see, you can try the 2 following variations:
CODE
PARAMETERS [pDateBeg] DateTime, [pDateEnd] DateTime;
SELECT FIRST(fldHistOpen) AS fldOpen, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow) AS fldLow, LAST(fldHistClose) AS fldClose
FROM Sales1
WHERE fldHistDateTime BETWEEN [pDateBeg] And [pDateEnd]
GROUP BY fldHistDateTime
ORDER BY fldHistDateTime ASC;

CODE
PARAMETERS [pDateBeg] DateTime, [pDateEnd] DateTime;
SELECT FIRST(t.fldHistOpen) AS fldOpen, MAX(t.fldHistHigh) AS fldHigh, MIN(t.fldHistLow) AS fldLow, LAST(t.fldHistClose) AS fldClose
FROM (
  SELECT *
  FROM Sales1
  WHERE fldHistDateTime BETWEEN [pDateBeg] And [pDateEnd]
  ORDER BY fldHistDateTime ASC
) t;

--------------------


Regards,

David Marten
Go to the top of the page
 
dw85745
post Aug 1 2019, 07:31 AM
Post#5



Posts: 103
Joined: 29-September 10
From: AZ


cheekybuddha:

Your first example returned the entire BETWEEN record set rather than a rollup.
Your second example errored on the FROM clause.

I tried this -- which gave me just a single rollup record, but instead of just being for the BETWEEN dates it
was for the entire Table.
CODE
PARAMETERS [pDateBeg] DateTime, [pDateEnd] DateTime;
SELECT FIRST(fldHistOpen) AS fldOpen, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow) AS fldLow, LAST(fldHistClose) AS fldClose
FROM Sales1
WHERE EXISTS (SELECT (fldHistDateTime), (fldHistOpen), (fldHistHigh), (fldHistLow), (fldHistClose)
FROM Sales1 As Dupe
WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd]
ORDER BY (fldHistDateTime));


====================
FWIW:
====================
I have a table with a PrimaryKey of fldHistDateTime.
In field order it looks like:
(fldHistDateTime), (fldHistOpen), (fldHistHigh), (fldHistLow), (fldHistClose)

I want to query that table specifying BETWEEN dates and return:
1) the value of fldHistOpen from the First Record (earliest date of BETWEEN)
2) the MAX value from fldHistHigh (from BETWEEN)
3) the MIN value from fldHistLow (from BETWEEN)
4) the value of fldHistClose from the Last Record (oldest date of BETWEEN)

So as I see it a query of a query where:
1) Get all records BETWEEN and ORDER BY Date
2( From query (1) get the FIRST, MAX, MIN, and LAST (provided Access returns the correct info for FIRST and LAST)

Like this which does Not Work as Access Prompts for the t fields

CODE
PARAMETERS [pDateBeg] DateTime, [pDateEnd] DateTime;
SELECT FIRST(t.fldOpen), MAX(t.fldHigh), MIN(t.fldLow), LAST(t.fldClose)
FROM t
WHERE EXISTS
(SELECT fldHistDateTime, fldHistOpen AS fldOpen, fldHistHigh AS fldHigh, fldHistLow AS fldLow, fldHistClose AS fldClose
  FROM Sales1 As t
  WHERE fldHistDateTime BETWEEN [pDateBeg] And [pDateEnd]
  ORDER BY fldHistDateTime ASC);




This post has been edited by dw85745: Aug 1 2019, 08:13 AM
Go to the top of the page
 
cheekybuddha
post Aug 1 2019, 09:16 AM
Post#6


UtterAccess VIP
Posts: 11,469
Joined: 6-December 03
From: Telegraph Hill


Hi, in that case I think you might just be able to use:
CODE
PARAMETERS [pDateBeg] DateTime, [pDateEnd] DateTime;
SELECT MIN(fldHistOpen) AS fldOpen, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow) AS fldLow, MAX(fldHistClose) AS fldClose
FROM Sales1
WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd];


If not, then you might need to post a table with representative data for the folks here to look at.


--------------------


Regards,

David Marten
Go to the top of the page
 
PhilS
post Aug 1 2019, 10:54 AM
Post#7



Posts: 614
Joined: 26-May 15
From: The middle of Germany


QUOTE
FIRST and LAST will be arbitrary if there is no ORDER BY specified in the query.

FIRST and LAST are always arbitrary unless you are able to fully understand and predict the underlying internal mechanics of the database engine. (You should never do this, even if (you think) you are able to!)
(My text DFirst/DLast and the Myth of the Sorted Result Set goes into more detail.)


In the context of the original question you can use a subquery to retrieve the "first" value:
CODE
SELECT fldHistOpen
FROM Sales1
WHERE fldHistDateTime = (SELECT MIN(fldHistDateTime) FROM Sales1)

This can be embedded in the full query, with amended criteria of course.

--------------------
Go to the top of the page
 
dw85745
post Aug 1 2019, 02:48 PM
Post#8



Posts: 103
Joined: 29-September 10
From: AZ


Thanks you both for your help on my behalf.
------------------
cheekybudda:
-------------------
Re:

CODE
PARAMETERS [pDateBeg] DateTime, [pDateEnd] DateTime;
SELECT MIN(fldHistOpen) AS fldOpen, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow) AS fldLow, MAX(fldHistClose) AS fldClose
FROM Sales1
WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd];


Haven't tried it but believe the MIN(fldHistOpen) and MAX(fldHistClose) code will return the Highest and Lowest Values in those columns
just like they do for the other two columns. For fldHistOpen I just want the value in that column of the first date indicated in the BETWEEN clause.
For fldHistClose, I just want the value in that column of the last date indicated in the BETWEEN clause.

Was hoping to do this in a single SQL query. To bad the SubQuery did NOT create a table in Memory where the Primary Query could then
extract the information from that Memory Table.

-------
PhilS
--------
EXCELLENT WriteUp.

Have Not tested your code - yet - but appears will give the result I need and conversely using a similar query with MAX with fldHistClose
hopefully will work. As comment with cheekybuddha, had hope to do this in a single query.
Just recognized my query is similar to what one would use for Stock Market Data where one would need to rollup different chart
time periods. Seems crazy SQL would make one do three queries to do this (that).
This post has been edited by dw85745: Aug 1 2019, 02:51 PM
Go to the top of the page
 
dw85745
post Aug 2 2019, 05:23 PM
Post#9



Posts: 103
Joined: 29-September 10
From: AZ


PhilS: Wanted to get back with you regarding:
QUOTE
In the context of the original question you can use a subquery to retrieve the "first" value:
CODE
SELECT fldHistOpen
FROM Sales1
WHERE fldHistDateTime = (SELECT MIN(fldHistDateTime) FROM Sales1)

This can be embedded in the full query, with amended criteria of course.


Struggled a bit with the above and yet to get it implemented.
As I understand it one cannot use BETWEEN within a subquery so not sure how I go about narrowing down the
dataset to get the MIN value I;m looking for like what occurs in the primary query.

For right now just did a separate query using a SQL BETWEEN clause for both fldHIstOpen and fldHistClose and
works MOSTLY. Because of gaps in recordset BETWEEN "sometimes" doesn't return a value so need to
figureout a workaround for this -- if possible.
Plan to work both on your Subquery suggestion and the BETWEEN issue hopefully this weekend.
This post has been edited by dw85745: Aug 2 2019, 05:27 PM
Go to the top of the page
 
PhilS
post Aug 3 2019, 07:16 AM
Post#10



Posts: 614
Joined: 26-May 15
From: The middle of Germany


QUOTE
As I understand it one cannot use BETWEEN within a subquery so not sure how I go about narrowing down the
dataset to get the MIN value I;m looking for like what occurs in the primary query.

I've never hear about any problem with a subquery and the BETWEEN operator.

I would write it this way:
CODE
PARAMETERS [pDateBeg] DateTime, [pDateEnd] DateTime;
SELECT
    (SELECT fldHistOpen
    FROM Sales1
    WHERE fldHistDateTime = (SELECT MIN(fldHistDateTime)
                        FROM Sales1
                        WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd])
    ) AS fldOpen,
    MAX(fldHistHigh) AS fldHigh,
    MIN(fldHistLow) AS fldLow,
    'can be implemnted with MAX similar to fldOpen' AS fldClose
FROM Sales1
WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd];


The field name fldHistDateTime indicates there might be a time component stored in there as well. - Keep in mind that a plain date (pDateEnd) includes a time of 00:00:00 and thus your criteria might remove records on the last day of the date range, you intended to include in the results.
Recommended reading: Date/Time in in VBA and Access

--------------------
Go to the top of the page
 
dw85745
post Aug 3 2019, 08:04 AM
Post#11



Posts: 103
Joined: 29-September 10
From: AZ


PhilS:
Thanks for your response and assistance (SQL code)
Will give it a test.
One question regarding your comment:
QUOTE
'can be implemnted with MAX similar to fldOpen' AS fldClose


Are you saying I would need to do two separate queries
or that another SELECT can be added into the code presented for fldClose using MAX ?
Remember the fldOpen value I want is the value in that field with the oldest date in the BETWEEN Group
and the fldClose value I want is the value in that field with the most current date in the BETWEEN Group.




QUOTE
he field name fldHistDateTime indicates there might be a time component stored in there as well.


Yes, that correct. A time component is part of that field.

QUOTE
I've never hear about any problem with a subquery and the BETWEEN operator.

I'll see if I can't find a reference (link) to what I read and post it here.
So many flavors of SQL, may Not of been Access SQL.

QUOTE
Recommended reading:


Fairly familiar with DateTime but a reminder never hurts. Thanks

===========================
Also ran a quick experiment on .MoveFirst, .MoveLast using DAO based on your article.
Since:
1) My tables have a PrimaryKey of fldHistDatetime
2) Data entered into those tables has always been in date/time sequence
(i.e. data is entered each day or throughout the day)
3) Access (not the underlying JET database) also presented that information (when looked at the table itself within the Access GUI)
in index (PrimaryKey here) order -- in my case sorted by date .
I always made the presumption that since the table was indexed there was a pointer
behind the scenes which, when DAO was used, that .Movefirst would provide the
record with the oldest calendar date - and - that .MoveLast would provide the record with the most current date.
WAS I WRONG in this thinking.

Here's my code and results.
CODE
Private Sub ATest()
'Test Access Database DAO Return Results

   Dim rsSrc As Recordset
   Dim strSrcTBLName As String
  
   strSrcTBLName = "ATEST"
  
   'Set DB Tables
   Set rsSrc = DaoDb.OpenRecordset(strSrcTBLName, dbOpenTable)       'dbOpenForwardOnly, dbReadOnly)

   'Get Source Table End Date for Loop Exit
   rsSrc.MoveLast
   Debug.Print "Last", CStr(rsSrc!fldHistDateTime)
   rsSrc.MoveFirst
   Debug.Print "First", CStr(rsSrc!fldHistDateTime)


   With rsSrc
  
      Do Until .EOF
         Debug.Print CStr(rsSrc!fldHistDateTime), CStr(rsSrc!fldHistOpen)
      .MoveNext
      
      Loop
      .Close
   End With

End Sub



CODE
Results (The number is the order in which I entered the record into the table):

Last          2/15/2018 6:30:00 AM
First         8/3/2019 9:30:00 AM
8/3/2019 9:30:00 AM         1
7/13/2014 6:25:00 PM        2
9/25/2017 5:27:00 PM        3
10/26/2015 4:27:00 AM       4
2/15/2018 6:30:00 AM        5


Can't THANK YOU enough for that article.
Won't ever forget this one.

So, I need to go back and relook at ALL my previous code to make sure
what I think I am getting is what I am getting.
This post has been edited by dw85745: Aug 3 2019, 08:18 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 3 2019, 08:11 AM
Post#12


UA Admin
Posts: 35,651
Joined: 20-June 02
From: Newcastle, WA


As noted, First and Last are NOT absolute references and it is never safe to rely on them.

Either apply an explicit ORDER BY in a query or use the Min and Max functions, which ARE absolute references. The "largest" or Max() value is always the same regardless of whether it falls in the first position in any given recordset, the last position, or anywhere in between.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
dw85745
post Aug 3 2019, 08:30 AM
Post#13



Posts: 103
Joined: 29-September 10
From: AZ


GroverParkGeorge:
Thanks for responding.
After my little experiment (see example in my post previous to yours), PhilS and your point is one "I hope to never forget".
I say hope as one never knows what the synapses in ones mind will do !!
Go to the top of the page
 
GroverParkGeorge
post Aug 3 2019, 08:46 AM
Post#14


UA Admin
Posts: 35,651
Joined: 20-June 02
From: Newcastle, WA


After more than 25 years calling Access my best friend, it still has the amazing ability to amaze me. Learning new stuff comes with the software license.

Continued success with your project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
dw85745
post Aug 3 2019, 02:10 PM
Post#15



Posts: 103
Joined: 29-September 10
From: AZ


PhilS;

Need to take a break for a bit, so will digest comments, test your sample code
and that posted by RJD here (https://www.UtterAccess.com/forum/index.php?showtopic=2054631)
and post back -- most likely tomorrow (Sunday) rather than today.
Go to the top of the page
 
dw85745
post Aug 4 2019, 04:41 PM
Post#16



Posts: 103
Joined: 29-September 10
From: AZ


PhilS: THANK YOU! THANK YOU! THANK YOU!

After good night sleep, finally dawned on me what you meant by:
QUOTE
'can be implemnted with MAX similar to fldOpen' AS fldClose


Query works like a charm, and as important, I learned a few things.

David
Have a nice day, and again Thank You for your time on my behalf.
This post has been edited by dw85745: Aug 4 2019, 04:42 PM
Go to the top of the page
 
dw85745
post Aug 6 2019, 12:41 PM
Post#17



Posts: 103
Joined: 29-September 10
From: AZ


FollowUp:

I found at random, that the above Access SQL BETWEEN clause would return the wrong value for fldHistOpen.
Query begin = [date 8:30:00 AM], Query end = [date 10;29;59 AM]

I then replicated WHERE BETWEEN with:

WHERE fldHistDateTime >= X and <= X

and also received Random error values for fldHistOpen. It appear Access is randomly returning either
the fldHistClose for the First Record or the fldHistOpen for the record After the First Record.

Converting those DateTimes to Doubles and then Comparing that double value to
various First Records DateTimes with the same DateTime stamp, I could NOT see any difference
in the double values shown by Access.

Changing to:

WHERE fldHistDateTime > X and < X

This change, so far seems to have solved the problem.
This requires the drop or add of one second from the begin and ending times
to receive the dates wanted. Adding / dropping a second may NOT work if
the database deals in milliseconds.
This post has been edited by dw85745: Aug 6 2019, 12:49 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th September 2019 - 08:37 AM