Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    replacing query with SQL (Access 2000)

    I have a working sql clasue based on the query QBas.It is the following"

    strSales = "SELECT DISTINCTROW QBas.StudentID, QBas.CompanyName, Sum(QBas.Liters) AS ProductSales " & _
    " FROM QBas " & _
    " GROUP BY QBas.StudentID, QBas.CompanyName"
    I want to base my first sql not on the query QBas, but on a second sql, so i converted the query QBas into an StrBas and tried to replace it

    as follows:

    strSales = "SELECT DISTINCTROW strBas.StudentID, strBas.CompanyName, Sum(strBas.Liters) AS ProductSales " & _
    " FROM strBas " & _
    " GROUP BY strBas.StudentID, strBas.CompanyName"

    Me.RecordSource = strSales

    But then i get the error message " The Microsfot jet engine cant find STrBas".May be i have to put some brackets?

  2. #2
    New Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replacing query with SQL (Access 2000)

    Have you actually created the table StrBas as a table within the mdb?

    Dr Godfrey Nicholson
    Ofek Technologies Ltd
    Auckland, NZ (the hub of the Pacific)

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

    Re: replacing query with SQL (Access 2000)

    <hr>I want to base my first sql not on the query QBas, but on a second sql, so i converted the query QBas into an StrBas <hr>
    I'm sorry but this makes no sense at all. What exactly is strBas? The only way you could use it as you're trying to is if strBas is a table or a saved query, which is what QBas already appears to be.

    It is possible to base one query on another even without using a saved query, but you can't use a string as a replacement for a query, and there are limitations on where and how you can use subqueries in a query. You will find that in many cases, basing a query on a saved query gives you better performance than using a subquery.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replacing query with SQL (Access 2000)

    Thank you for your reply. I am afraid my question was rather foggy and i think i should apologize for that.
    I will try to explain myself in more detail.

    Below is the original record source of a report:
    Qbas is a query

    Dim StrSales as string
    strSales = " SELECT DISTINCTROW QBas.afid, QBas.CompanyName, Sum(QBas.Liters) AS ProductSales " & _
    " FROM QBas" & _
    " GROUP BY QBas.afid, QBas.CompanyName"

    Me.RecordSource = StrSales
    I want to base my sql not on the query QBas, but on the sql from that query.But i receive errors.

    I converted the query QBas into an sql and i received the folowing:

    strBas = " SELECT DISTINCTROW Format([invoicedate],'yy-mm') AS [Month], Products.size, Sum([Order Details].liters) AS Liters,

    orders.paymentid, orders.invoicedate, customers.afid, affiliates.CompanyName " & _
    " FROM ((affiliates INNER JOIN customers ON affiliates.afid = customers.afid) INNER JOIN orders ON (customers.Customerid =

    orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN (Products INNER JOIN [Order Details] ON

    Products.Productid = [Order Details].ProductID) ON orders.orderid = [Order Details].OrderID " & _
    " GROUP BY Format([invoicedate],'yy-mm'), Products.size, orders.paymentid, orders.invoicedate, customers.afid,

    affiliates.CompanyName " & _
    " HAVING (((orders.paymentid)>0) AND ((orders.invoicedate)>#1/1/2001#))"


    Now i tried to replace the QBAs with the StrBas into the code like that:
    strSales = "SELECT DISTINCTROW afid, CompanyName, Sum(Liters) AS ProductSales " & _
    " FROM " & strBas & _
    " GROUP BY afid, CompanyName"
    But i receive the error Sytnatx error in FROM clause

    I also tried the following"
    strSales = "SELECT DISTINCTROW afid, CompanyName, Sum(Liters) AS ProductSales " & _
    " FROM " & strBas & " & GROUP BY afid, CompanyName"

    And also:
    strSales = "SELECT DISTINCTROW strBas.afid, strBas.CompanyName, Sum(strBas.Liters) AS ProductSales " & _
    " FROM strBas " & _
    " GROUP BY strBas.afid, strBas.CompanyName"

    but again with the same result

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: replacing query with SQL (Access 2000)

    Hi,
    You can do this as follows:
    <pre>strSales = "SELECT DISTINCTROW QBas.afid, QBas.CompanyName, " & _
    "Sum(QBas.Liters) AS ProductSales FROM (SELECT DISTINCTROW " & _
    "Format([invoicedate],'yy-mm') AS [Month], Products.size, " & _
    "Sum([Order Details].liters) AS Liters, orders.paymentid, " & _
    "orders.invoicedate, customers.afid, affiliates.CompanyName " & _
    "FROM ((affiliates INNER JOIN customers ON affiliates.afid = " & _
    " customers.afid) INNER JOIN orders ON (customers.Customerid = " & _
    "orders.customerid) AND (customers.Customerid = orders.customerid)) " & _
    "INNER JOIN (Products INNER JOIN [Order Details] ON Products.Productid " & _
    "= [Order Details].ProductID) ON orders.orderid = [Order Details].OrderID" & _
    " GROUP BY Format([invoicedate],'yy-mm'), Products.size, orders.paymentid," & _
    " orders.invoicedate, customers.afid, affiliates.CompanyName " & _
    " HAVING (((orders.paymentid)>0) AND ((orders.invoicedate)>#1/1/2001#))) QBas" & _
    " GROUP BY QBas.afid, QBas.CompanyName"
    </pre>

    but as far as I can see from what you're retrieving, you'd be far better off writing a new SQL retrieving the data from the original tables rather than running one SELECT statement off another, particularly if you're not storing the original query.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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