dcsimg
Results 1 to 5 of 5

Thread: SQL Access RollUp Query

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    396

    SQL Access RollUp Query

    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 randomly 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've tried multiple different forms of the query using a SubQuery
    to get the BETWEEN data first,
    -- the latest being, 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);
    ==============
    1) What am I missing in the construct
    2) Do I need to separately query for the FIRST and LAST record specifying the Date of that Record, or
    3) Do I need to build a Temporary Table with the SubQuery and then run my original SQL, but if I do
    that do I run up against the FIRST and LAST arbitrary issue of value returned?

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,381

    Re: SQL Access RollUp Query

    that's because t doesn't exist in the context in which you're trying to use it. Actually, I think all you needed to do was add the ORDER BY to your first query and that should have been that.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    396

    Re: SQL Access RollUp Query

    techgnome:
    Thanks for responding.
    RE:
    Actually, I think all you needed to do was add the ORDER BY to your first query and that should have been that.
    I previously tried putting in an ORDER BY clause but Access would Not accept the SQL as written.
    I tried and got:
    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;
    ====================

    Re:
    that's because t doesn't exist in the context in which you're trying to use it.
    Can you please out where my logic is wrong in the code in Post#1.

    Rereading SQL doc, the Alias "As whatever" only exists for the period of the Query.
    It appears SQL is treating the Subquery as a separate query, and 't" in my case goes
    out of scope for use in the Primary Query.
    Last edited by vb6forever; Aug 1st, 2019 at 10:57 AM. Reason: add comment

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    396

    Re: SQL Access RollUp Query

    FWIW: Came across this article for creating a temporary table in memory and then querying this memory table.
    They used Cold Fusion which I'm not aware of so don't know if this can be replicated in SQL.
    https://www.bennadel.com/blog/259-cr...sumarizing.htm

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    396

    Re: SQL Access RollUp Query

    The following query, returns correctly on what appears to be a random basis,
    whether one uses WHERE BETWEEN or WHERE AND,

    For the beginning date of the query, Access at times returns the fldHistClose value
    instead of the fldHistOpen value for selected dated records. Converting those
    record values to doubles, and comparing them to other records where the
    fldHistOpen value is returned correctly shows no difference in double precission.


    Code:
       strParam = "PARAMETERS [pDateBeg] DATETIME, [pDateEnd] DATETIME; "
       strSQL = strParam
       strSQL = strSQL & "SELECT (SELECT fldHistOpen"
       strSQL = strSQL & " FROM " & strTBLName
       strSQL = strSQL & " WHERE fldHistDateTime = (SELECT MIN(fldHistDateTime)"
       strSQL = strSQL & " FROM " & strTBLName
    '   strSQL = strSQL & " WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd])"
       strSQL = strSQL & " WHERE (fldHistDateTime >= [pDateBeg] And fldHistDateTime <= [pDateEnd]))"      
       strSQL = strSQL & " ) AS fldOpen, (SELECT fldHistClose"
       strSQL = strSQL & " FROM " & strTBLName
       strSQL = strSQL & " WHERE fldHistDateTime = (SELECT MAX(fldHistDateTime)"
       strSQL = strSQL & " FROM " & strTBLName
    '   strSQL = strSQL & " WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd])"
       strSQL = strSQL & " WHERE (fldHistDateTime >= [pDateBeg] And fldHistDateTime <= [pDateEnd]))"
       strSQL = strSQL & " ) AS fldClose, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow) AS fldLow"
       strSQL = strSQL & " FROM " & strTBLName
    '   strSQL = strSQL & " WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd];"
       strSQL = strSQL & " WHERE (fldHistDateTime >= [pDateBeg] And fldHistDateTime <= [pDateEnd]);"
    
    Don't know this gentlemans name -- refers to himself as PhilS -- but credit for above code belongs to him.
    His WebSite:  https://codekabinett.com
    Last edited by vb6forever; Aug 6th, 2019 at 04:50 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width