Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL based on query (Access 2000)

    I have an sql based on a query.What i want is to base my sql not on a query but on the sql if this query.
    Is it possible?
    For example, i have the following sql:
    SELECT qryCharts.afid, Sum(qryCharts.Liters) AS SumOfLiters
    FROM qryCharts
    GROUP BY qryCharts.afid;

    This sql is based on ther query "charts".Is it possible to base my sql not on the query "charts"
    but on another sql, say SQLCharts?

    I will be grateful for any help

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL based on query (Access 2000)

    Yes it is easy.
    Open your query in Access in design view and switch to SQL view to see the underlying SQL. Copy all this, except the final ";" and put a bracket like this ( at the beginning and one like this ) at the end.
    Insert the whole lot between FROM and qryCharts.
    Now make a single space between FROM and what you just copied and also put a blank space after what you just copied followed by the word AS then another blank space after that.
    So you get:
    SELECT qryCharts.afid, Sum(qryCharts.Liters) AS SumOfLiters
    FROM (your SQL statement) AS qryCharts
    GROUP BY qryCharts.afid;

    This basically creates a SUBQUERY aliased as qryCharts. You can even replace qryCharts by something shorter such as just Q.
    You can even nest further subqueries within the SQL for qryCharts if that query is in turn based on another query or queries.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL based on query (Access 2000)

    However, subqueries frequently run more slowly than queries based on saved queries.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL based on query (Access 2000)

    In that case I'd rather use saved queries.
    But I swear I read somewhere the opposite is supposed to be true!

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL based on query (Access 2000)

    It may well have been true in an earlier version of Access because I vaguely recall having read something like that once. In Access 2000, though, saved queries don't have to be optimized each time they're run, even parameter queries. A SQL statement in code, though, does and that translates into a difference in speed. You can actually see the difference if you use a SELECT statement as the datasource for a form. The first time you open the form in a session, there will be a noticeable delay. After that, the form will open as you would expect because the query has been optimized for that session.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL based on query (Access 2000)

    Interesting. Is there any recommended paradigm for storing these 'functional' stored queries?
    My spirit rebells against keeping them in the usual Query container along with all the others in case one were accidently deleted and broke the application.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL based on query (Access 2000)

    If you're letting your users get to the database window, you've already shot yourself in the foot ... even if the pain hasn't yet arrived. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> I used to stash the SQL for my queries in a table with the query name in a text field and the SQL in a memo field. That allowed me to retrieve and run the SQL from code in Access 97. The problem you run into with that approach, though, is that references to UDFs in the SQL string will break when you try to run it. You have to force the UDFs to be evaluated so that their actual result will be passed into the query. I did something like this:

    'Replace the function with its value in the SQL
    strSQL = Replace(strSQL, strUDF, Eval(strUDF))

    That does NOT offer a really optimal approach for Access 2000, though, since you pay a penalty for completely unoptimized queries.

    The bottom line is, if you don't want your queries broken accidentally, don't let your users into the database window. <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL based on query (Access 2000)

    My most dangerous "user" is myself. But I think I had better continue to allow access in this case to the database window.
    Sorry for my ignorance, but what is an UDF?

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL based on query (Access 2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Sorry for the jargon. UDF is User-Defined Function.
    Charlotte

  10. #10
    New Lounger
    Join Date
    Jan 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL based on query (Access 2000)

    Based on this post, I think you might be able to help me, since I'm trying to include a field from a subquery in a main query. I've attached a Word document (hopefully it's under 100K) which shows the way it works if the subquery is a saved query. Unfortunately, I'm just using Access to model my query, which is going directly against a SQL server using ASP. So, I would like to accomplish the same thing using SQL only.

    I know in a following post by Charlotte Foust that she mentioned that a SQL subquery might be slower than a saved subquery in Access; I'm hoping that such is not the case when I do this directly against the SQL database.

    I think the attached document is pretty self-explanatory. Would appreciate any help I can get!

    Thanks.


    Kevin Johnston
    Kraft Foods, Inc.
    kjohnston@kraft.com
    Attached Files Attached Files

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL based on query (Access 2000)

    I'm a little confused as to why you want to use a subquery to begin with. You can get the same result by simply making your Main query a GroupBy query - the only thing you added was the date in the Main query, and it is unique as it's coming from the Master table, so you simply do a GroupBy on it. And the SQL should work pretty much as is in the SQL database.

    Charlotte's post dealt with the situation where the subquery is run in SQL and then the main query is run in Access. That will always be slower than running everything in SQL or everything in Access (Jet). But you won't be faced with that issue as you will be running exclusively in SQL. Hope this helps.
    Wendell

  12. #12
    New Lounger
    Join Date
    Jan 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL based on query (Access 2000)

    Thanks for the reply. You are of course right, and I overlooked the simple. However, I didn't mention that the main query is actually quite more complex than the one I originally posted; for example, it has memo fields and several linked tables. When I tried using the simple (and correct) approach you suggested (grouping the main query), it didn't work. I get error messages such as:

    "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

    Without trying to address the cause of the error message, would it be possible to answer the question as I originally posted it? I still think that might work in this particular situation, and if it does, it would save troubleshooting time for both of us.

    Much obliged!!

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL based on query (Access 2000)

    That of course makes sense - memo fields in Groupby queries are particularly troublesome, as they get truncated to 255 characters (at least in Jet). Since you are going to run it in SQL, you will need to have a SQL string on your ASP page, or you will need to connect to a view that does what you want. The query you want to run can easily be done as a subquery - if you need syntax check out the SQL Server books on line for subqueries. They aren't often done in Jet because you can use stored queries which are generally more efficient. In SQL Server you can do views on views, so that would accomplish pretty much the same thing, and it will generally run a fair bit faster in SQL Server. As an alternative you could create a Stored Procedure which does the same thing, but they are somewhat harder to develop and maintain. Since you are using Access2K you could create an ADP, and then create the views in SQL from that platform. Just to be clear, if you are using memo fields, avoid Group By queries - it's a snake pit recently visited in several threads here - the view on a view should work fine just as a query on a query does in Access.
    Wendell

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL based on query (Access 2000)

    Your question seems to be the same one I asked a year or two ago and the simple answer is you just substitute the SQL select statement for your sub-query into the main SQL statement creating a SUB SELECT.
    Like this:

    SELECT dbo_MasterContract.MasterContractID, dbo_MasterContract.CurrentExpirationDate, TestSubQuery.VendorCount
    FROM dbo_MasterContract INNER JOIN <font color=red>(SELECT dbo_MasterContract.MasterContractID, Count(dbo_MasterContractVendors.MasterContractID) AS VendorCount
    FROM dbo_MasterContract LEFT JOIN dbo_MasterContractVendors ON dbo_MasterContract.MasterContractID = dbo_MasterContractVendors.MasterContractID
    GROUP BY dbo_MasterContract.MasterContractID
    HAVING (((Count(dbo_MasterContractVendors.MasterContractI D))>1))
    ) As </font color=red>TestSubQuery ON dbo_MasterContract.MasterContractID = TestSubQuery.MasterContractID;

    Using the correct terminology, you are creating an Alias
    called TestSubQuery by using this phrase:

    (SELECT BLA Bla) As TestSubQuery

    You'll find that after you save your new query in Access
    it is actually modified by Access to change

    (SELECT BLA Bla) As TestSubQuery

    into

    [SELECT BLA Bla]. As TestSubQuery

    and if you try to save this then you get an Access error message saying incorrect SQL syntax.
    But you can just replace the square brackets with the original rounded ones and delete the extra dot
    and it all works again.
    You may need to do this if you subsequently modify your new query and try to save the result.
    Hope this is the answer!

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL based on query (Access 2000)

    I gather you got it working and deleted your post that emailed me saying you couldn't get it working?
    (my guess as to why I get an error message when I tried to connect to the post via the link in the email I received)

Page 1 of 2 12 LastLast

Posting Permissions

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