dcsimg
Results 1 to 5 of 5

Thread: [RESOLVED] Access SQL Range Query

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    265

    Resolved [RESOLVED] Access SQL Range Query

    This fails on the FROM clause.
    I have a table of ranges (tblStatRanges) separately defined and trying to get the
    data within table Test to be grouped on the table range criteria.

    Code:
    SELECT  tblStatRanges.fldUpper as Range, count(*) as NumberOfOccurrences
    FROM tblStatRanges
    INNER JOIN Test 
    WHERE (Test.fldHigh - Test.fldLow)  < tblStatRanges.fldUpper
    GROUP BY fldRange;
    The above was adopted from this example (which may or may not have worked -- internet code).
    here: https://stackoverflow.com/questions/...ccess-database
    Code:
    SELECT r.agerangestart &" - "&r.agerangeend as AgeRange, 
      count(*) as NumberOfOccurrences
    from agerange r
    inner join test t
      on t.age >= r.agerangestart
      and t.age <= r.agerangeend
    group by r.agerangestart &" - "&r.agerangeend
    Also in the internet code example:

    1) I have never seen a table name redefined (e.g. agerange r) and (e.g. test t) in SQL.
    This is not delineated (shown_ at W3School SQL examples.
    Is this acceptable or was it done just as a reference to shorter typing by the poster in the internet example?

    2) I thought to use an INNER JOIN there had to be a common field between the tables?
    Last edited by vb6forever; Nov 5th, 2018 at 11:35 AM.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,895

    Re: Access SQL Range Query

    It's called object aliasing and it is allowed...
    your first example fails, but not because of the from clause... it fails because your inner join is incomplete ... there's no ON clause ... you can't just simply state that two tables are joined with out stating how those tables are related... Might want to double check the W3C School section on joins... I'm sure there's an example there on how to properly join two tables together.

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

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    265

    Re: Access SQL Range Query

    techgnome:

    Thanks for pointing that out.
    Was just going to add to my original post:
    "I thought to use an INNER JOIN there had to be a common field between the tables"

    in the internet example I don't see a common field.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,280

    Re: Access SQL Range Query

    There does not have to be a common field, just a relationship specified in an ON clause... which the example had, but you left out (you did however add a Where clause, which does contain a relationship).

    Your use of Group By is also not valid, because it needs to contain every item from the Select list that does not use aggregate functions (you have specified fldRange, but you should have fldUpper).

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    265

    Re: Access SQL Range Query

    techgnome and si_the_geek

    Thanks, got it, appreciate your help, and learned a few things which is always nice.

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