Does MS Access support sub-query usage

[cantene]

All dears,

Can I write SQL that contain sub-query for MS Access?
Thanks

[Serge]

Of course,

SELECT product_id, supplier_id, product_name
FROM products
WHERE supplier_id IN
   (SELECT supplier_id
  FROM supplier
  WHERE (country = 'USA'))

Assuming that I want to get all products where the suplier is in US.

[cantene]

Thanks a lot


I wish to get the sales statistics (between start date to end date) from the database of all products. But the product name is in another table linking by oproductid, is it true to write the following:

Public Sub GetSalesData(ByVal StartDate As Date, ByVal EndDate As Date)

    sql = "SELECT Product.ProductID AS " & lProductID & ", " & _
          "Product.ProductName AS " & lProductName & ", " & _
          "SUM(OutStock.Quantity) AS " & lOutQuantity & " " & _
          "FROM Product, OutStock " & _
          "WHERE Product.ProductID=OutStock.ProductID " & " " & _
          "AND OutStock.Date BETWEEN #" & StartDate & "# AND #" & EndDate & "# " & _
          "GROUP BY Product.ProductID, Product.ProductName"
:
:

Can you give me advice. Thanks again

[Serge]

The code you have looks ok, but in your case this is not a subquery, but a simple join.

[cantene]

Originally posted by Serge
The code you have looks ok, but in your case this is not a subquery, but a simple join.

Yes, I originally thought of using sub-query, so asking you the question bfore. Now if this method is OK, I will choose this for simplicity. I wish to say deep thanks to yr useful help