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

    Substituting into SQL in place of a saved query

    If you have an SQL statement which refers to a saved query
    then is there any simple way to substitute the SQL definition of
    the query into the main SQL statement so that the saved query is no longer needed?
    That is, can you rework the SQL statement to remove all mention of the query.
    Here is the code I am working on... I want to remove all references to 'Query3'
    Can anyone point me in the right direction?

    MAIN SQL STATEMENT

    SELECT Calls.CallID, Calls.ContactsInterests, Query3.NextCallID, Calls_1.ContactsInterests
    FROM (Query3 INNER JOIN Calls ON Query3.CallID = Calls.CallID) INNER JOIN Calls AS Calls_1 ON Query3.NextCallID

    = Calls_1.CallID;


    Query3 (SQL STATEMENT FOR)

    SELECT T2.CallID, Min(T1.CallID) AS NextCallID
    FROM Calls AS T2 INNER JOIN Calls AS T1 ON T1.CallID>T2.CallID
    WHERE ( T1.ContactID= T2.ContactID)
    GROUP BY T2.CallID


    David Hitchins

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

    Re: Substituting into SQL in place of a saved query

    It might help if you could explain what you're trying to do here. Attempting to follow all those nested self-joins is making my head ache.

    Meanwhile, to help get you started, here's a simple SQL statement that uses a subquery in the FROM clause. But be aware that it won't work in Access 97, although it does in Access 2000:

    <pre>SELECT Orders.*, E.LastName, E.FirstName
    FROM Orders
    INNER JOIN (SELECT * FROM Employees) AS E
    ON Orders.EmployeeID = E.EmployeeID;</pre>


    If you save the query and then look at it later, you'll see that Access has changed it to this:

    <pre>SELECT Orders.*, E.LastName, E.FirstName
    FROM Orders
    INNER JOIN [SELECT * FROM Employees]. AS E
    ON Orders.EmployeeID = E.EmployeeID;</pre>


    However, you can't write it that way in the first place, so don't try.
    Charlotte

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

    Re: Substituting into SQL in place of a saved query

    Sorry Charlotte! It also made my head ache too and that's why I am desperate for a solution.
    Basically the first query looks up a table of Calls or Messages from a CALLS table which is joined to a CONTACTS
    table (similar to the Northwind Database) via a contactID.
    I am trying to create (within a subform called "Messages") two panes: the left one is a message composition pane and the right one is the previous message either received from that contact or sent to him/her. Practically I can then write a reply answering the client's questions without switching between windows as you do in Outlook. And it is all done in my Access application so I don't have to invoke Outlook at all except via some VBA automation. The query called QUERY3 finds the minimum of all CallID numbers from the Calls table (or Messages table if you like) greater than or equal to the current CallID - this being decided by which line I am currently viewing in the subform. The main SQL statement then returns the message pane for that minimum CallID. I am using a self-join to do this on the same table (Calls). Hence the message returned (the contents of the 'ContactsInterests' field) will be the very next communication to or from the contact after the current message. Hence I can simultaneously view both the current message and the one that follows it. The one that follows it is my message and the one before, the one I am responding to. I hope this is clear! Anyway, I have come up against this problem before where I want to include a subquery in both the SELECT statement and the FROM clause - not just the FROM clause. Maybe you can't do this because it is "ungrammatical" but surely the SQL statement can be rephrased so that it is "grammatical" and says the same thing? It would seem to be that this must be possible. But I don't know how.
    Thanks!

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

    Re: Substituting into SQL in place of a saved query

    Charlotte, a slight correction to my last message: the right pane is the composition pane, not the left.
    David

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Substituting into SQL in place of a saved query

    Why do you not want it to be a stored procedure (Access Query)? If you're concerned about portability to other databases, you can write the code to create the Query at runtime...

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

    Re: Substituting into SQL in place of a saved query

    The trouble with stored procedures is that I have a Window
    full of them (full to overflowing), many of which are merely stepping stones to the final "super" query. I am only interested in the "super" query, not all the composite queries that lead up to it. So I want to be rid of the stepping stone queries so I can easily find the important ones in the query window. Also, it all seems rather inelegant and I think my question deserves an answer if only for aesthetic reasons! Call me a fusspot but I prefer an SQL statement that stands on its own two legs and doesn't refer to a dozen others. So, does anyone know the answer to my original question?

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

    Re: Substituting into SQL in place of a saved query

    Thanks Charlotte!!! I looked at your example, thought about it and tried to apply it but failed each time. Then I went all perverse and did what you said not to do, viz
    <<SELECT Orders.*, E.LastName, E.FirstName
    FROM Orders
    INNER JOIN [SELECT * FROM Employees]. AS E
    ON Orders.EmployeeID = E.EmployeeID;
    However, you can't write it that way in the first place, so don't try.>>
    and it worked! Thanks for all your help.
    David

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

    Re: Substituting into SQL in place of a saved query

    Glad it worked for you. I've had it blow up on me when I tried to enter it that way, so it may have something to do with the way the individual machines are set up or which references are set. I'll let you tackle converting your own SQL yourself.[img]/w3timages/icons/grin.gif[/img]
    Charlotte

Posting Permissions

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