SQL in a module

[Paul]

Hello again

If i write This SQL statement in a module
Dim dbs as Database
Dim SQL1 as sting
Set dbs as CurrentDb

SQL1 = "SELECT Order.ID" & _
"FROM Order" & _
"GROUP BY Order.ID;

In the second line "FROM", Order must be a table or a
query, or can be something else like another Sql
statement or a querydef or a recordset?
If yes ca i see some examples?

 

[Dirk Goldgar]

Hello again

If i write This SQL statement in a module
Dim dbs as Database
Dim SQL1 as sting
Set dbs as CurrentDb

SQL1 = "SELECT Order.ID" & _
"FROM Order" & _
"GROUP BY Order.ID;

In the second line "FROM", Order must be a table or a
query, or can be something else like another Sql
statement or a querydef or a recordset?
If yes ca i see some examples?

Well, it can't be a recordset or a querydef object as such (though of
course you can refer to stored query represented by the querydef object.
It *can* be an embedded SQL statement, in parentheses or using the
special Jet syntax for such things.

For example,

(standard syntax

SELECT OC.OrderID, OC.CustomerID FROM
(SELECT Order.ID As OrderID
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID)
As OC
GROUP BY OC.OrderID, OC.CustomerID;

(special Jet syntax

SELECT OC.OrderID, OC.CustomerID FROM
[SELECT Order.ID As OrderID
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID].
As OC
GROUP BY OC.OrderID, OC.CustomerID;

I think I have those statements right, though I may have made mistakes
in constructing them.