replacing query with SQL (Access 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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)

Reply | Quote

[indent]

---

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

---

[/indent]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.

Reply | Quote

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

Reply | Quote

Hi,
You can do this as follows:

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"

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.

Reply | Quote