Sub-query

[JimP]

I have an orders table with several thousand orders. Each order has a
Product_Id and a SubProduct_Id.

I want to create a query that lists each Product_Id and the number of
different SubProduct_Id's associated with it.

I can figure out how to do this in 2 queries, but it seems like it should be
possible to do it in one.

 

[Tom Ellison]

Dear Jim:

You can use a subquery. It might look something like this:

SELECT P.Product_Id, COUNT(*) AS Ct
FROM Products P
LEFT JOIN SubProducts S
ON S.Product_Id = P.Product_Id
FROUP BY P.Product_Id

Here are some details about the above:

- The LEFT JOIN (instead of an INNER JOIN) allows the query to count 0 when
there are no SubProducts for some of the Products.

- I have made assumptions about the table names. Fix them up.

- P and S are aliases for the table names, saving typing. Without aliases,
the query is:

SELECT Products.Product_Id, COUNT(*) AS Ct
FROM Products
LEFT JOIN SubProducts
ON SubProducts.Product_Id = Products.Product_Id
FROUP BY Products.Product_Id

I'm just too lazy for that. Well, except to show you the difference.

Tom Ellison
Microsoft Access MVP

 

[JimP]

Thanks for the reponse, but the query appears to be returning the total
number (count) of SubProduct_Id's associated with a Product_Id - rather than
a count of the number of "different" SubProduct_ID's.

I'm guessing there needs to be some type of grouping in the subquery on
table "S" to get the unique Product_Id's and SubProduct_Ids, before the
count in the main query.

 

[Jason Lepack]

SELECT
Product_Id,
Count(SubProduct_Id) as DifferentProducts
FROM (
SELECT DISTINCT
Product_Id,
SubProduct_Id
FROM Orders)
GROUP BY
Product_Id

Cheers,
Jason Lepack

 

[JimP]

...it works - thank you.

SELECT
Product_Id,
Count(SubProduct_Id) as DifferentProducts
FROM (
SELECT DISTINCT
Product_Id,
SubProduct_Id
FROM Orders)
GROUP BY
Product_Id

Cheers,
Jason Lepack

 

[JimP]

While this code works, when I go to design view for the query, the query
alias is %$##@_Alias.

Is this typical, why?

 

[John Spencer]

If you don't assign an alias for the subquery in the FROM clause, Access
does. And to ensure there is no conflict it apparently generates
something that is unlikely to ever be used.

I normally do assign some name to the subquery, especially since my
subqueries are often involved in joins to other tables or queries.

So something like the following is what I usually enter.

SELECT
Product_Id,
Count(SubProduct_Id) as DifferentProducts
FROM (
SELECT DISTINCT
Product_Id,
SubProduct_Id
FROM Orders) As ListSubProds
GROUP BY
Product_Id

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================