SQL in a module

  • Thread starter Thread starter Paul
  • Start date Start date
P

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?
 
Paul said:
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?
Click to expand...

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.
 
Back
Top