Access SQL Subquery - Reference a Calculated Column (field)

[vb6forever]

In order to get a total value for several columns, I referenced the actual columns(fields) in the SELECT statement as well as
including the calculation for this total in the SELECT and then referencing (referring to) that calculation using "As Total". Worked as designed.

I Now want to make this select a Sub Query and use the Total in my Main Query. The other Columns in the Sub-Query are NOT needed in Main.

How do I refer to just the Total column in my Main Query?

[jmcilhinney]

By the alias, i.e. Total. You can think of the subquery's result set as a temp table so just use it like you would any other table.

[vb6forever]

jmcilhinney:
Thanks for responding.

My error for not explaining in detail what I'm trying to accomplish.

First I'm trying to do a histogram (frequency distribution).
I have one table (tblStatRanges) which defines rows of upper and lower bounds
for which a number for each row (occurance) should be returned.
I have a table of data (test). This table contains three columns and N number of rows. The first column (primary key) is Date (fldHistDateTime), the Second (fldHistHigh) and the third (fldHistHigh1).

The Histogram is to be based on the total of (fldHistHigh + fldHistHigh1).
As originally posted this worked since I'm dealing with a single table.

====================
My rational (obviously wrongly) for the following SQL code.
I included the Subquery as part of SELECT since
there is No common fleld between the two tables
and hence (IMHO) no way to join them. Access errored without including EXISTS for the subquery.

Access recognizes the following as an acceptable formatted SQL Query.
However, I am prompted for both begin and end dates (which is correct
because of PARAMETER) , however I am also being prompted for Total.

PARAMETERS [pBegDate] DateTime, [pEndDate] DateTime;
SELECT tblStatRanges.fldLower & "-" & tblStatRanges.fldUpper AS Range, count(*) AS Occurance, EXISTS (SELECT Test.fldHistDateTime, Test.fldHistHigh, Test.fldHistHigh1, (Test.fldHistHigh + Test.fldHistHigh1) AS Total
FROM Test
WHERE (Test.fldHistDateTime Between [pBegDate] And [pEndDate]))
FROM tblStatRanges
WHERE ((Total <= tblStatRanges.fldUpper) AND (Total >= tblStatRanges.fldLower))
GROUP BY tblStatRanges.fldLower & "-" & tblStatRanges.fldUpper;

[vb6forever]

While I resolved the issue without a SubQuery using this code:

PARAMETERS [pBegDate] DateTime, [pEndDate] DateTime;
SELECT tblStatRanges.fldLower & "-" & tblStatRanges.fldUpper AS Range, count(*) AS Occurance
FROM tblStatRanges INNER JOIN TEST ON ((TEST.fldHistHigh + TEST.fldHistHigh1) <= tblStatRanges.fldUpper) AND ((TEST.fldHistHigh + TEST.fldHistHigh1) >= tblStatRanges.fldLower)
WHERE (TEST.fldHistDateTime Between [pBegDate] And [pEndDate])
GROUP BY tblStatRanges.fldLower & "-" & tblStatRanges.fldUpper;

Since my objective is/was to learn when/where/how to use subqueries, I hope someone
can explain:

1) if or how this same query can be done using a subquery?
2) In the code that resolved the issue (above) why an INNER JOIN works as my understanding
of JOINS is there needs to be a common field among the two tables?

[FunkyDexter]

1. I'm honestly not sure because I don't really understand your requirement. Could you post some sample data along with the desired result you would get from that data? That generally makes things much clearer.
2. That's a common miss-conception and 99% of the time you would be right. However, a join simply needs some set of conditions to be met between two datasets. For an inner join, a record will be returned whenever the condition is true. For example, if I want a cartesian product (quite common e.g. when building cubes for reporting) I can do this (assuming the flavour of SQL doesn't support Cross Join syntax):-

Select D1.ID, D1.Description, D2.Id, D2.Description, IsNull(Sum(F.Qty), 0)
From Dimension1 D1
Inner Join Dimension D2
   on 1= 1 --<--  NB. No fields at all here
Left Join Fact F
   On D1.ID = F.Dimension1ID
   And D2.ID = F.Dimension2ID
Group By D1.ID, D2.ID

That would create me a record set containing every tuple for two dimensions with a sum of the quantities from a fact relating to each tuple.

You can also join on ranges, single determinate values... anything really as long as it can be expressed as a Boolean condition.

[Zvoni]

FD,

as far as i understood it something along these lines
AIRCODE

SELECT SomeRange, COUNT(*) AS Occurance
FROM RangeTable 
WHERE (SELECT (HistHigh+HistHigh1) AS Total FROM TestTable WHERE HistDate BETWEEN BeginDate AND EndDate) BETWEEN RangeLow AND RangeHigh 
GROUP BY SomeRange

What i don't understand:
Going by his Query of the TestTable, his SELECT-Statement implies, that more than one result can return since his WHERE-clause there is a BETWEEN dates!!!
So it could return n values for Total!
And then he wants to use this/those result(s) again in the BETWEEN for his RangeTable

[FunkyDexter]

as far as i understood it something along these lines

could be. the difference would be that your sub query would crash where multiple rows are returned for total (which I think is what you're pointing out) while the joined query would return multiple rows. I think we'd need more info from the OP to be sure though.

[Zvoni]

*snipp* while the joined query would return multiple rows.*snipp*

Hmm, but since he uses a COUNT with a GROUP without displaying the returned values of his TestTable, wouldn't those multiple returns of his TestTable be aggregated into his final result (a.k.a direct influence on COUNT)?

EDIT: I nearly missed an additional Question/Issue of the OP

however I am also being prompted for Total.

IIRC, Field-Aliases cannot be used in Forward-Direction within the same Statement.

'Doesn't work
SELECT (A+B) AS Result FROM MyTable WHERE RESULT>10
'This Works
SELECT Result FROM (SELECT (A+B) AS Result FROM MyTable) WHERE Result>10

...or i just wrote a lot of bulls***

[vb6forever]

Thank you both for responding.
I have a table named "TEST" with just 3 fields named (fldHistDateTime, fldHistHigh, fldHistHigh1) and some junk data.
I also have a table named "tblStatRanges" which defines Lower and Upper Bound Ranges for which I want the data
in table "TEST" evaluated against. Here's a pic of tblStatRanges:

See Image

After running the above query, I get a count (a histogram) of TEST values
(based on the query) for each range. If no Test data falls within a given range,
no count is returned, and the Range is Not shown. My guess is this is a function
of SQL and not the query.


See Image

[vb6forever]

Thanks for both responses.
--------------
FunkyDexter:
---------------

However, a join simply needs some set of conditions to be met between two datasets. For an inner join, a record will be returned whenever the condition is true.

So that explains why the Inner Join in my query just returns the result set where Test data falls within the ranges shown --
AND -- ignores (doesn't) return the ranges where no data resides.

Inner Join Dimension D2
on 1= 1 --<-- NB. No fields at all here

Where does the 1 =1 come from and why is it used?
My guess is to set up (force) a true condition.

================================
And back to my question of:
Can my query that works also be done using a subquery?
I ask this because I can see a number of situations where one might want to get data
from other than the current row?

[ChrisE]

Hi,

take a look at PIVOT with IIf and PIVOT with the option Switch

here a sample with IIF, open in Access(Northwind) a new Query,
add this Sql ..

TRANSFORM Count(Orders.OrderID) AS OrderAmount
SELECT Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
PIVOT IIf([Customers].[CompanyName] Like "A*",[Customers].[CompanyName],"others");

HTH

[vb6forever]

ChrisE:
Thank you for taking the time to respond.
Will take a look at PIVOT, but as stated, my objective is:
1) Learn to use SubQueries and
2) Given the query example posted in #3, understand if a subquery can be used to accomplish the
same thing as the query that works in post #4, since -- I believe -- a subquery will be required to
get at values in prior rows if generating another histogram where prior row data is required.

Also in your example, the INNER JOIN is using a common field which is not the case in this instance.

[FunkyDexter]

Where does the 1 =1 come from and why is it used?
My guess is to set up (force) a true condition

Spot on. It's just an arbitratry "always true" condition.

I haven't had a chance to look at this yet today and am gonna be kinda busy (the devs have created a first normal form table with a butt load of repetition and redundancy and I've got to try and explain to the business why this is a bad thing) but hopefully I'll get a chance to revisit.

[Zvoni]

*snipp*the devs have created a first normal form table with a butt load of repetition and redundancy and I've got to try and explain to the business why this is a bad thing*snipp*

Oh dear.... my condolences.....

[FunkyDexter]

If no Test data falls within a given range, no count is returned, and the Range is Not shown. My guess is this is a function
of SQL and not the query.

That's because you're using an Inner Join. It requires a match on both sides of the condition to produce a record in the output. If you want a record when the condition is not met you need to use an Outer Join.

So if I'm right you're basically saying you want a row for each Stat Range and you want a count of the number of TEST records who's fldHistHigh + fldHistHigh1 falls within each stat range. Where there are no TEST records you still want a row for that stat range but you want it to display a zero. Have I understood that correctly?

Here's the simplest solution for that (based on TSQL because that's what I've got available but should translate easily to Acces):-

SELECT tblStatRanges.fldLower & "-" & tblStatRanges.fldUpper AS Range, count(T.fldHistHigh + T.fldHistHigh1) AS Occurance
FROM tblStatRanges SR
LEFT JOIN Test T
   ON T.fldHistHigh + T.fldHistHigh1 BETWEEN SR.fldLower And SR.fldUpper
WHERE T.fldHistDateTime Between [pBegDate] And [pEndDate]
GROUP BY tblStatRanges.fldLower & "-" & tblStatRanges.fldUpper

The Left Join ensures you get a row for every stat range and your count need to be across any arbitrary non-null field in TEST (the primary key or the field used in the join are usually good candidates for this)

Note, you could do this with a sub query but I suggest you don't. Joins are more readable and tend to produce better execution plans (though not necessarily). If you really want a sub query for some reason you would do this:-

SELECT tblStatRanges.fldLower & "-" & tblStatRanges.fldUpper AS Range,
   (SELECT Count(*)
   FROM Test T
   WHERE T.fldHistHigh + T.fldHistHigh1 BETWEEN SR.fldLower And SR.fldUpper
   AND T.fldHistDateTime Between [pBegDate] And [pEndDate]
) as Occurance
FROM tblStatRanges SR

[vb6forever]

FunkyDexter:
Thank you for taking your time to post back on my behalf.
Want to take a day to digest your latest post.

[vb6forever]

FunkyDexter:

So if I'm right you're basically saying you want a row for each Stat Range and you want a count of the number of TEST records who's fldHistHigh + fldHistHigh1 falls within each stat range. Where there are no TEST records you still want a row for that stat range but you want it to display a zero. Have I understood that correctly?

Either way was OK for test examples.
The comparison of the LEFT JOIN example to the SubQuery Example was most helpful.

However, "most interesting" is the LEFT JOIN returned the same result set as the INNER JOIN (post #4)
-- that is, no 0 records for Ranges where there is no data --
while the Subquery example returned All Ranges with both a 0 count and a data count.

So a little confused, since my understanding of LEFT Join should return All "criteria" records for the 1st table.
Whether this has something to do with Access JET SQL implementation is unknown.

In regard to subqueries -- I assume I would use TOP to get at each prior row record
(i.e. use prior row column values) in relationship (+, - , *, /, or compare) to the current row column values since my understanding of JOINS is they only apply to the current row.

==========================
The following might seem a bit dumb re Subqueries
===========================
Using subqueries as part of the primary select (as in your example) is logical.
You want to return a value or values to be used elsewhere.

However, I've seen subqueries also used with most other SQL reserved words.
Any general rule of thumb or suggestion you can provide for this usage?

[FunkyDexter]

Whether this has something to do with Access JET SQL implementation is unknown.

Nope, I made a <ahem> deliberate error to teach you a lesson. Think about what a Left Join returns on the right hand side when there are no rows... it returns NULLS. Now take a look at the Where clause. It references fields from the right hand side (Test)… which will be NULL. So it's not the join that's excluding the rows, it's the Where clause. So the lesson I... like... TOTALLY set out to teach was: If you need to filter the rows that are returned on the right hand side of a left hand join you need the filter to be in the ON clause, not the Where clause.

So what I should have given you was:-

SELECT tblStatRanges.fldLower & "-" & tblStatRanges.fldUpper AS Range, count(T.fldHistHigh + T.fldHistHigh1) AS Occurance
FROM tblStatRanges SR
LEFT JOIN Test T
   ON T.fldHistHigh + T.fldHistHigh1 BETWEEN SR.fldLower And SR.fldUpper
   AND T.fldHistDateTime Between [pBegDate] And [pEndDate]
GROUP BY tblStatRanges.fldLower & "-" & tblStatRanges.fldUpper

Using subqueries as part of the primary select (as in your example) is logical.
You want to return a value or values to be used elsewhere

Logical perhaps but not performant.

Notice that the subquery references fields from the outer query: "WHERE T.fldHistHigh + T.fldHistHigh1 BETWEEN SR.fldLower And SR.fldUpper". Because the values of SR.fldLower and SR.fldUpper are unpredictable to the engine until the query resolves the outer query (which will be after the execution plan is calculated) the engine has to resolve this by running the sub query for every single row in the outer query, which is highly inefficient. This is called a "Corelated" sub query and the effect of having to resolve the query separately for each row is known as RBAR (Row By Agonising Row). The Join approach, on the other hand, is able to resolve both sides of the query and then merge them together which is much more efficient.

In this particular case it probably didn't do much damage because the dataset returned by your outer query is very small so it was more a case "Row by Agonising... oh, I've run out of Rows". Indeed, the engine might have chosen to use a RBAR approach. But by using a corelated sub query you gave it no choice, it had no way of optimising the query. Over a larger dataset the performance hit can be VERY significant.

Any general rule of thumb or suggestion you can provide for this usage?

Yeah, non-corelated sub-queries (i.e. they don't reference the outer query) are OK but even then a join will probably be more consumable by the engine (and if it's non-corelated you're probably creating a dataset to join to in the outer query anyway). Other than that, use them when you absolutely, positively can't find a way to express your query in a way that avoids them - and those scenarios are few.

In regard to subqueries -- I assume I would use TOP to get at each prior row record

I'm not sure I'm following you. Could you give me a concrete example?

[vb6forever]

Nope, I made a <ahem> deliberate error to teach you a lesson.

No apologizes necessary.
One always learns more from their mistakes.
Your explanation is most helpful and is what one really needs.
Sadly, I have yet to find a book or internet post of SQL rules that explains filtering and other SQL trade offs as in your explanation.

After your post I did find -- what I consider a simple rule for using WHERE versus ON, if you agree with it.

ON filters data before tables are joined; WHERE filters after.

=================================

In regard to subqueries -- I assume I would use TOP to get at each prior row record
I'm not sure I'm following you. Could you give me a concrete example?

Sorry I mentioned TOP as don't believe it will do it.
Taking the examples of generating a histogram (i.e. a Table of Ranges and a Table of Data),
I can see where the need would exist to use PRIOR and CURRENT data rows in the calculation
with again the final purpose of generating a histogram of this calculation.

Where I see the issue of PRIOR row usage is that rows data may NOT be contiguous in the database depending on how (when) it was entered. So looking for a Prior row number is out. Even, as in the above examples where the fldHistDateTime is (can be) the PrimaryKey or Keyed, presents a problem of gaps in Date Order. Weekends and Holidays are a good example where No data may exist.

So for example, lets say we have a dataset of:

....Date .........TotalA ....TotalB
11/1/2018........10...........10
11/2/2018........20...........20
11/3/2018........15...........15
11/4/2018........30...........30
11/6/2018........50...........50

And one wanted to (+, -, *, or /) each prior row value from each followon row
such as:

11/1/2018 ignored as no earlier data
11/2/2018 result set = (11/1/2018 + 11/2/2018)
11/3/2018 result set = (11/3/2018 + 11/2/2018) '<< Not cumulative (just the two days)
11/4/2018 result set = (11/4/2018 + 11/3/2018) '<< Not cumulative (just the two days)
11/6/2018 result set = (11/6/2018 + 11/4/2018) '<< Not cumulative (just the two days)

and then a histogram generated based on the calculated result sets.

[FunkyDexter]

if you agree with it

I do and I don't. Logically it's true but in reality it usually isn't. When you're working out what a query will return you work out the join first and the where second, that's the "logical" sequence of events the database will follow. However, database query optimisation is all about the engine finding the fastest way of achieving the logical result and it's more likely to go something like:-

1. Filter Table1 and Filter Table2 by the where clause - both operations in parallel,
2. Sort Table1 and Sort Table2 by the fields in the On clause
3. Merge the two result sets using a forward only merge

If there was an order by it would logically be applied last but in reality it's possible the sorts in step 2 would be by the fields in the order by rather than the fields in the join. This would make the join less efficient but would save having to do an extra sort operation at the end.

The logical contract is fulfilled as if the operations were carried out in a set order but the engine is actually likely to go about the whole thing in a different way - as long as it achieves the same logical result.

So if you want to get your head around the best way to write a query you have to consider both the logical sequence of events (to make sure you'll get the result you want) but also learn a bit about what happens under the hood. Unfortunately Access doesn't make this easy because it won't tell you what it actually did. In SQL Server we can look at the execution plans and have a poke around - that's where you start to pick up real experience. Even then, though, the truth is we often end up trying out a few ways of expressing a query and then just accepting whichever one performs "well enough" at scale.


On the TOP issue, I'm afraid I don't know the best way of doing it in Access but in SQL Server we'd use the Row_Number function - which I'm pretty sure Access doesn't support. You could use a sub query with a TOP but I think that would involve creating a "Trinagular Join" which is when you join a table to itself based on an Inequality. Have a google for these if you're curious but for now just take it as read that they're horrible for performance- much worse than sub queries in fact.

I'm about to head out for a meal at the moment but I'll try and take a crack at it over the weekend. Or one of our resident Access experts could probably provide some better advice.

[vb6forever]

FunkyDexter

Had never heard of this JOIN.
FWIW provided pretty good explanation
http://www.devx.com/dbzone/working-w...lar-joins.html

In my case I do not want a running total but just the values from the previous record.
Can accomplish this in VB, but was hoping SQL could do it -- and -- more efficiently.

[Schmidt]

Had never heard of this JOIN....

In my case I do not want a running total but just the values from the previous record.
Can accomplish this in VB, but was hoping SQL could do it -- and -- more efficiently.

Since I use SQLite for testing such stuff (due to its ability to create InMemory-DBs) -
your final SQL will have to be adapted for JET-SQL a bit - but I've deliberately avoided the usage of SQLite-CTEs
(which would have made things easier) - since those are not supported in JET-SQL...

The first approach is using "Unioned SelfJoined SubSelects".

These SubSelects are using a Rank-Numbering-scheme over a SelfJoin ((re)named as Tj) this way:

Select T.Date, Max(T.TotalA + T.TotalB) As Total, Count(*) As Rank 
From T Join T As Tj On T.Date > Tj.Date Group By T.Date

So, given your earlier posted Input (in a Table, named T):
....Date .........TotalA ....TotalB
11/1/2018........10...........10
11/2/2018........20...........20
11/3/2018........15...........15
11/4/2018........30...........30
11/6/2018........50...........50

The above SQL produces this output, if run on its own:

4 Records
Date          Total         Rank          
02.11.2018     40            1            
03.11.2018     30            2            
04.11.2018     60            3            
06.11.2018     100           4

Note, that the record with the smalles InputDate is left out in the above result...

Now we union the above result with the second SelfJoin below,
which is different from the SQL of the first one in only a single Character (marked red below):

Select T.Date, Max(T.TotalA + T.TotalB) As Total, Count(*) As Rank 
From T Join T As Tj On T.Date >= Tj.Date Group By T.Date

The above SQL produces this output, if run on its own:

5 Records
Date          Total         Rank          
01.11.2018     20            1            
02.11.2018     40            2            
03.11.2018     30            3            
04.11.2018     60            4            
06.11.2018     100           5

So, the effect of the >= comparison is, that the first (minimum-date) Record is now included.
Now, if you "Union" the two Sets above - and then group over their Ranking-Index, you will
"Sum-up" a "current date-record" with a "previous-date-record" (just look at the Rank-Numbers of the two resultsets).

Ok - that much to the explantion of "how to produce Ranking with a Self-Join" - and what these Ranking-
Indexes can be used for in a "Grouped Union".

In the Demo below I've included a second "more straight forward" approach (using normal Field-SubSelects) -
which one runs faster on larger sets of data, would have to be tested -
performance will depend heavily on Indexing the Date-Column (in both approaches).


Ok, here is the fully working Code-Example (VBA and VB6 will need a reference to the SQLite-COMWrapper - vbRichClient5)

Option Explicit

Private Cnn As cConnection

Private Sub Form_Load()
  Set Cnn = New_c.Connection(, DBCreateInMemory)
      Cnn.Execute "CREATE TABLE T(ID Integer Primary Key, Date DateTime, TotalA Integer, TotalB Integer)"
 
      Cnn.Execute "Insert Into T Values(Null, '2018-11-01', 10, 10)"
      Cnn.Execute "Insert Into T Values(Null, '2018-11-02', 20, 20)"
      Cnn.Execute "Insert Into T Values(Null, '2018-11-03', 15, 15)"
      Cnn.Execute "Insert Into T Values(Null, '2018-11-04', 30, 30)"
      Cnn.Execute "Insert Into T Values(Null, '2018-11-06', 50, 50)"
  
  With New_c.StringBuilder 'a Self-Join-approach, which Unions two "ranked SubQueries"
     .AppendNL "Select Max(Date) As Date, Sum(Total) As Total From ("
     .AppendNL "  Select T.Date, Max(T.TotalA + T.TotalB) As Total, Count(*) As Rank"
     .AppendNL "  From T Join T As Tj On T.Date > Tj.Date Group By T.Date"
     .AppendNL "  Union All"
     .AppendNL "  Select T.Date, Max(T.TotalA + T.TotalB) As Total, Count(*) As Rank"
     .AppendNL "  From T Join T As Tj On T.Date >= Tj.Date Group By T.Date"
     .AppendNL ") Group By Rank Having Count(*)=2 Order By Date"
 
     DumpRs Cnn.OpenRecordset(.ToString)
  End With
  
  With New_c.StringBuilder 'and a straight forward approach using SubSelects
      .AppendNL "Select Date, Sum(TotalA "
      .AppendNL "               + TotalB "
      .AppendNL "               + ( Select Sum(TotalA + TotalB) From T Where Date < T_outer.Date "
      .AppendNL "                   Group By Date Order By Date Desc Limit 1"
      .AppendNL "                 )"
      .AppendNL "             ) As Total"
      .AppendNL "From T As T_outer Group By Date Having Not Total Is Null"
 
     DumpRs Cnn.OpenRecordset(.ToString)
  End With
End Sub

Sub DumpRs(Rs As cRecordset)
  Dim Fld As cField
  Debug.Print vbLf & Rs.RecordCount; " Records"
  For Each Fld In Rs.Fields: Debug.Print Fld.Name,: Next: Debug.Print
  Do Until Rs.EOF
    For Each Fld In Rs.Fields: Debug.Print Fld.Value,: Next: Debug.Print
    Rs.MoveNext
  Loop
End Sub

The above prints the following into the Debug-Window (for both approaches):

4 Records
Date          Total         
2018-11-02     60           
2018-11-03     70           
2018-11-04     90           
2018-11-06     160

HTH

Olaf

[FunkyDexter]

In my case I do not want a running total but just the values from the previous record

The problem is how to calculate which is the previous record.

In TSQL we have the row number function which is a great shortcut because it means you can join the table to itself based on an equality, e.g. T1.RowNum = T2.RowNum + 1. In Access you don't have that so you end up having to join the table back to itself based on an inequality, e.g. Top 1 T2.Value Where T1.Date > T2.Date. It's that inequality that creates the triangular join.

I've wracked my brain but I just can't come up with a set based approach that will work with access that doesn't contain that self inequi-join somewhere within it. I haven't followed Olaf's solution all the way through but I notice it contains this: "T Join T As Tj On T.Date > Tj.Date" or this "Where Date < T_outer.Date" and anything I'm coming up with exhibits the same pattern. It's a triangular join so the tuples it has to resolve increases as a demi-exponent of the number of rows in the table. Or put simply, it just won't scale. That's the problem with triangular joins, they perform absolutely fine when we develop them at small scale. It's only when you scale them up in production that the performance problems start to reveal themselves.

There are a few ways to avoid triangular joins but most rely on undocumented behaviours (eg quirky updates) or non ansii constructs (there's quite a nice recursive solution I often use and I notice someone has suggested a rather nice use of OVER in the article you linked to). The only way I know that you can reliably avoid them across sql flavours is to abandon the set based approach and use a loop. If you're using Access that probably means loop across your record set using VB and maintain a "last value" variable as you do so.

[vb6forever]

Olaf and FunkyDexter:
Thank you for your efforts on my behalf.

Olaf post little over my head at this moment, so will need some time to study it.

he only way I know that you can reliably avoid them across sql flavours is to abandon the set based approach and use a loop. If you're using Access that probably means loop across your record set using VB and maintain a "last value" variable as you do so.

That's what I am doing now and works fine.
Just wanted to go all SQL -- if possible.

[FunkyDexter]

Just wanted to go all SQL -- if possible.

That's definitely the right attitude and is exactly where you should start out. It's just that this is a particular edge case where you're better off breaking that rule.

[Schmidt]

Olaf post little over my head at this moment, so will need some time to study it...

Ok, here's the slightly adjusted SQL of the simpler approach, which does work against the JET-Engine:
(the otherwise empty Form needs an MSHFlexGrid on it for visualization)...

Private Sub Form_Load()
  Dim Cnn As Object, DBName: DBName = Environ("temp") & "\MyTest1.mdb"
  If CreateObject("Scripting.FileSystemObject").FileExists(DBName) Then Kill DBName
  
  Set Cnn = CreateObject("ADOX.Catalog").Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName)
      Cnn.CursorLocation = 3 'adUseClient
  
      Cnn.Execute "CREATE TABLE T(ID Int, Dat Date, TotalA Int, TotalB Int)"
  
      Cnn.Execute "Insert Into T Values(1, '2018-11-01', 10, 10)"
      Cnn.Execute "Insert Into T Values(2, '2018-11-02', 20, 20)"
      Cnn.Execute "Insert Into T Values(3, '2018-11-03', 15, 15)"
      Cnn.Execute "Insert Into T Values(4, '2018-11-04', 30, 30)"
      Cnn.Execute "Insert Into T Values(5, '2018-11-06', 50, 50)"
      
  Show
  Set MSHFlexGrid1.DataSource = Cnn.Execute("Select * From T")
  MsgBox "Input-Data"
  
  Dim SQL As String
      SQL = SQL & "Select * From ("
      SQL = SQL & "Select Dat, Sum(TotalA + TotalB"
      SQL = SQL & "            +  (Select Top 1 Sum(TotalA + TotalB) From T Where Dat < T_Outer.Dat"
      SQL = SQL & "                  Group By Dat Order By Dat Desc"
      SQL = SQL & "               )"
      SQL = SQL & "         ) As Total "
      SQL = SQL & "From T As T_Outer Group By Dat) Where Not Total Is Null"
      
  Set MSHFlexGrid1.DataSource = Cnn.Execute(SQL)
End Sub

HTH

Olaf

[vb6forever]

----------
Olaf
---------

I took your post and created a T Table in Access and filled it with the sample data.
(Your CREATE TABLE and INSERT INTO portion of your code in post #26)

I then attempted to Create a Query in Access SQL Viewer.
I did edit it slightly to make the relationship between the Table and fields more obvious
by adding T. in front of the field names as I find (IMHO) Access seems to prefer this and
also helps --me-- if multiple tables are involved.
I error on the first FROM.

Select *
From (Select T.Dat, Sum(T.TotalA + T.TotalB +
(Select Top 1 Sum(T.TotalA + T.TotalB) From T Where T.Dat < T_Outer.Dat
Group By T.Dat Order By T.Dat Desc)) As Total 
From T As T_Outer Group By T.Dat) Where Not Total Is Null;

I do NOT understand where these two entries originate

1)   T_Outer.Dat        >> Understand this refers to date but where is T_Outer
                                      coming from as my Understanding the subquery is
                                      executed first, so at that point T_Outer does Not
                                      exist, but is used in the subquery ?
2)   T.Dat Desc          >> What is Desc doing after T.Dat and where is Desc coming from ?

[Schmidt]

1) T_Outer.Dat >> Understand this refers to date but where is T_Outer
coming from as my Understanding the subquery is
executed first, so at that point T_Outer does Not
exist, but is used in the subquery ?

2) T.Dat Desc >> What is Desc doing after T.Dat and where is Desc coming from ?

to 2) ... the Desc snippet is part of the Order by Clause (forcing descending Order on a field)
.... if left out then [...Order by FieldName Asc...] is the implicitely assumed default

to 1) Yeah, the T_Outer was the real "outer select" in the SQLite-example.
But JET-SQL does not allow to refer to a calculated Field directly in the Having-Clause.

Therefore the additional wrapping of the SQL with "another outermost outer" (which was not named this way)...
Just leave this (later added) outermost-outer - out of the SQL and test the real workhorse as shown below.
(Now with fully qualifying Names for the Inner and Outer Select)

  Dim SQL As String
'      SQL = SQL & "Select * From ("
      SQL = SQL & "Select T_Outer.Dat, "
      SQL = SQL & "       Sum(T_Outer.TotalA + T_Outer.TotalB)"
      SQL = SQL & "            +  ( Select Top 1 Sum(T_Inner.TotalA + T_Inner.TotalB) From T As T_Inner "
      SQL = SQL & "                 Where T_Inner.Dat < T_Outer.Dat"
      SQL = SQL & "                 Group By T_Inner.Dat Order By T_Inner.Dat Desc"
      SQL = SQL & "               ) As Total "
      SQL = SQL & "From T As T_Outer Group By T_Outer.Dat" '  Having Not Total Is Null"  
'      SQL = SQL & ") Where Not Total Is Null"  '<- only necessary, because JET does not allow the above Having-Clause

HTH

Olaf

[Ecniv]

re Post#23
Just to point out that in Access VBA (at least up to 2013) you can create a vba function pointed to a global variable (possible use a static variable in the function) and call the function directly from the sql. So your function could add x to a variable... and keep a running total.
If you only need the older values and the columns arent changing, perhaps instead of a variable of one value, use an array. The function just stores them into the global variable. Then a second can read the array position back. Note, as it goes into VBA you'll probably be slowing the response time down a lot, but it should work.

[Ecniv]

As a probably pointless waste of time I made the following... But obviously isnt what you need at the moment

SELECT
sr.fldLabel,
COUNT(sq.id) AS theTotal

  FROM tblStatRanges sr
LEFT JOIN (
SELECT 
t.id,
t.vlr,
(SELECT r.IDRange FROM tblStatRanges r WHERE r.fldLower < t.vlr AND r.fldUPPER> t.vlr) AS idRange
  FROM tblTest t
) AS sq ON sr.idrange = sq.idrange
GROUP BY sr.fldLabel

I used your screen shots from above for the ranges. Probably repeated from an earlier post from the gurus

My preference would be to run vba loop and output to a report table (local) for using in the histogram, since its a specific task
Also the range table could do with a display order column for ordering the final output...

[vb6forever]

--------
Olaf
--------
Again, thank you for all your efforts.

1) First let me apologize for taking up your time on Desc.
FWIW, prior to my post, I looked up "Desc" in Access help and didn't show.
After your post looked up "Order By" and Desc showed there.
Should have made the linkage prior to that -- but what can I say.

2) In regard to your post #28, Access SQL Viewer still errors on the first FROM.
Checked the SQL syntax multiple times and all looks good.
Whether Access SQL (JET) has issues with embedded subqueries ?


----------
Ecniv
----------
Thanks for posting. Will take a look see.

[Schmidt]

2) In regard to your post #28, Access SQL Viewer still errors on the first FROM.
Checked the SQL syntax multiple times and all looks good.

Yep - the JET-Engine is not the problem here (the VB6-Code, which renders the Rs into the HFlex is working just fine)

Whether Access SQL (JET) has issues with embedded subqueries ?

Yeah - seems so (just tested this in Access) -
the problem is not with the Query-Result itself -
it's just that the Access-"GridView" apparently tries to enter a kind of "Edit-Mode" on these *calculated* Fields.

Normally the "AutoDetection" (which Field is a calculated Expression, and which Field is linked directly to an underlying table),
should work - but apparently only when the nesting of SubQueries is not all too deep.

I've googled this problem - and apparently an additional Distinct in the SQL-Query
will give enough hints to the Grid-View, to not enter "Edit-Mode".

But that's of course a horrible workaround, since it costs query-performance -
and some queries will deliver an entirely different result-set with a leading 'Distinct' Keyword...

Have made a little Test-MDB-File anyways: TestQueries.zip

HTH

Olaf

[vb6forever]

--------
Eniv:
--------

Ran into same issue with your code in Post #30 as Olaf's.
Error in FROM clause.
Error higlights the SELECT after LEFT JOIN

It appears JET does not like multiple embedded SubQueries.

[FunkyDexter]

you can create a vba function pointed to a global variable (possible use a static variable in the function) and call the function directly from the sql. So your function could add x to a variable

I wasn't aware of that but I think it's likely to be the best performing solution if you can work out how to do it. Effectively it would behave much like a quirky update and allows a single forward only pass through the table. One big gotcha with this type of approach though, you need to make sure you order the records correctly to give a predictable numbering order.

I do want to advise real caution with the other solutions being proposed here. They are logically correct but they are triangular joins and are highly unlikely to scale. If you're going to adopt one, test it at scale first! Make sure you dummy up a realistic number of records and run across it. Don't scrimp on this because the query time is going to increase exponentially with the number of records so a small number of extra records can have a huge impact.

[Ecniv]

@vb6forever
Strange as I made that sql in an Access db and it ran (v2019).
What error message did you get?

tblTest
id - pk auto number
vlr - number double

tblStatRanges
IDRange pk autonumber
fldLabel string 255
fldLower number double
fldUpper number double

Are you using a VB front end to connect?
Or native inside Access?

[vb6forever]

----------
Olaf
---------
Plan to look at your file this weekend

--------------
FunkyDexter
--------------

I do want to advise real caution with the other solutions being proposed here. They are logically correct but they are triangular joins and are highly unlikely to scale.

Thanks for the heads up. Noted.

---------
Eniv
---------

What error message did you get?

Sytax Error in FROM clause.
Error highlights the SELECT after LEFT JOIN

Are you using a VB front end to connect?
Or native inside Access?

Just use Access as Backend.
So using SQL Viewer and execute directly from Query for testing.
Because of syntax error Access won't save query.
(one of the things I don't like about Access as you can't save a non-functioning query for future edit.)

[vb6forever]

----------
Olaf
--------
Sadly, my Access97 could not load the mdb file.
Spent large part of the weekend trying to track down someone who
had a more recent version. --- No Luck.
Since my version of Access does Not appear to support multiple embedded subqueries -
at an impass on my end.
Will kept the mdb file and reference this thread for future use.

THANKS to everyone for taking their time to help me resolve this issue.

[Ecniv]

Why are you still using Access 97 mdb?
If you can get to 2007/2013 you should have lots more to play with.

free ms Access 2016 Runtime
Not sure you can use this to create mdb/accdb files. But if you get newer files you should be able to connect to and read them.

Does your Sql Viewer read the newer files?
If so, just ask someone to create the structures you need, then send you a zip file of the empty db; you can then connect and update the accdb files.
Only restriction is if you need to manipulate the structure.

But best idea is to get your hands on a newer version.

[vb6forever]

Why are you still using Access 97 mdb?

Because Office 97 (which Access is a part of) has always met my needs -- hence no need to upgrade.

Does your Sql Viewer read the newer files?

No. ASAIK the mdb file needs to be loaded by Access in order for
the Access Sql Viewer (designer) to read the SQL within the mdb file.
I can see most of Olaf code within the mdb file using a Hex Editor but
since assume Access format unknown not sure if intact.

But best idea is to get your hands on a newer version.

I agree. However where I'm at currently health wise, doesn't seem realistic to commit lot resources to keep things up and going.