By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,081 Members | 2,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,081 IT Pros & Developers. It's quick & easy.

How to create subquery in MS Access

P: 2

I am trying to follow an tutorial. But it uses sql express. I would like to use MS Access. Now I have trouble with a query they use.

This is the query which works with sql express.:

SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
(SELECT CategoryName FROM Categories
WHERE Categories.CategoryID = Products.CategoryID) as CategoryName,
(SELECT CompanyName FROM Suppliers
WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName
FROM Products

Can someone help me to write a similar query but which would work with MS Access DB? (version 2000 or 97)

Nov 8 '08 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 2,653
Hello, daratech.

Frankly speaking I don't see any reason why this query will not run in Access - what error you have encountered?

On the other hand I would suggest to use table join instead of subquery.
Something like
Expand|Select|Wrap|Line Numbers
  1. SELECT     Products.ProductID, Products.ProductName, Products.SupplierID, Products.CategoryID, Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, Products. Discontinued, Categories.CategoryName, Suppliers.CompanyName
  2. FROM Categories INNER JOIN (Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID) ON Categories.CategoryID = Products.CategoryID;
INNER JOIN could be replaced with RIGHT JOIN in a case correspondent fields in [Products] table mey contain Null's.

Nov 8 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.