dcsimg
Results 1 to 39 of 39

Thread: Access SQL Subquery - Reference a Calculated Column (field)

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Access SQL Subquery - Reference a Calculated Column (field)

    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?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,641

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.

    Code:
    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;
    Last edited by vb6forever; Nov 25th, 2018 at 01:28 PM.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    While I resolved the issue without a SubQuery using this code:
    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?

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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):-
    Code:
    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.
    Last edited by FunkyDexter; Nov 26th, 2018 at 03:31 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,537

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    FD,

    as far as i understood it something along these lines
    AIRCODE

    Code:
    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
    Last edited by Zvoni; Nov 26th, 2018 at 06:35 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,537

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    Quote Originally Posted by FunkyDexter View Post
    *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.
    Code:
    '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***
    Last edited by Zvoni; Nov 26th, 2018 at 10:06 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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
    Attached Images Attached Images   
    Last edited by vb6forever; Nov 26th, 2018 at 10:16 AM.

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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?
    Last edited by vb6forever; Nov 26th, 2018 at 10:32 AM.

  11. #11
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,435

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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 ..
    Code:
    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.

  13. #13
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,537

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    Quote Originally Posted by FunkyDexter View Post
    *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.....
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  15. #15
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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):-
    Code:
    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:-
    Code:
    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
    Last edited by FunkyDexter; Nov 29th, 2018 at 03:28 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  16. #16

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

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

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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?
    Last edited by vb6forever; Nov 29th, 2018 at 09:25 AM.

  18. #18
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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:-
    Code:
    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?
    Last edited by FunkyDexter; Nov 29th, 2018 at 02:25 PM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  19. #19

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.
    Last edited by vb6forever; Nov 30th, 2018 at 11:52 AM.

  20. #20
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  21. #21

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.

  22. #22
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,903

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    Quote Originally Posted by vb6forever View Post
    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:
    Code:
    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:
    Code:
    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):
    Code:
    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:
    Code:
    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)
    Code:
    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):
    Code:
    4 Records
    Date          Total         
    2018-11-02     60           
    2018-11-03     70           
    2018-11-04     90           
    2018-11-06     160
    HTH

    Olaf

  23. #23
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.
    Last edited by FunkyDexter; Dec 4th, 2018 at 04:04 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  24. #24

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.

  25. #25
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  26. #26
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,903

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    Quote Originally Posted by vb6forever View Post
    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)...
    Code:
    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

  27. #27

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

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

    Code:
    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
    Code:
    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 ?
    Last edited by vb6forever; Dec 5th, 2018 at 09:06 PM.

  28. #28
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,903

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    Quote Originally Posted by vb6forever View Post
    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)
    Code:
      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
    Last edited by Schmidt; Dec 6th, 2018 at 01:22 AM.

  29. #29
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,314

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.

    BOFH Now, BOFH Past, Information on duplicates

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

  30. #30
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,314

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    As a probably pointless waste of time I made the following... But obviously isnt what you need at the moment
    Code:
    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...

    BOFH Now, BOFH Past, Information on duplicates

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

  31. #31

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

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

  32. #32
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,903

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    Quote Originally Posted by vb6forever View Post
    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)

    Quote Originally Posted by vb6forever View Post
    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

  33. #33

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

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

  34. #34
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  35. #35
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,314

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    @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?

    BOFH Now, BOFH Past, Information on duplicates

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

  36. #36

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

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

  37. #37

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

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

  38. #38
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,314

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.

    BOFH Now, BOFH Past, Information on duplicates

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

  39. #39

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    293

    Re: Access SQL Subquery - Reference a Calculated Column (field)

    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.

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