Solved Group By Name then Count on criteria of another field (1 Viewer)

[HeavyD]

Hello lovely people,

I'm trying to Group By ProductName, then Count the ProductNames that are still pending an AssetNumber.

InventoryTable
ProductName AssetNumber
ProductA pending
ProductA pending
ProductA pending
ProductB 1234
ProductB 1234
ProductC 5678
ProductC 5678
ProductD pending
ProductD pending


Question/Problem:
Count of ProductNames with AssetNumber equal to "pending" is : 2 . I'm having issues trying to get the count of 2. I'll later put this value in a text box. This is what I have tried so far (+20 variations).

SELECT InventoryTable.[ProductName], Count(*) AS Expr1
FROM Inventory
GROUP BY InventoryTable.[ProductName]
HAVING (((InventoryTable.[AssetNumber])="pending"));

This gives an error saying that AssetNumber isn't part of the aggregate expression.

I've also tried it through the DesignView (below), but the error refers to a Datatype mismatch in the criteria.

I'm still trying to get an appreciation for the SQL language. I learn by cobbling examples together and playing with the individual peices.

Any insight is appreciated.

 

[pbaldy]

This should fix the error:

SELECT [ProductName], Count(*) AS Expr1
FROM Inventory
WHERE [AssetNumber]="pending"
GROUP BY [ProductName]

Note you've got 2 different table names in your SQL.

 

[arnelgp]

SELECT Count("1") As PendingCount
FROM (SELECT InventoryTable.ProductName
    FROM InventoryTable
    WHERE (((InventoryTable.AssetNumber)="pending"))
    GROUP BY InventoryTable.ProductName);

 

[HeavyD]

This should fix the error:

SELECT [ProductName], Count(*) AS Expr1
FROM Inventory
WHERE [AssetNumber]="pending"
GROUP BY [ProductName]

Note you've got 2 different table names in your SQL.

Thank you pbaldy.
While your suggestion did fix the error, the result was not what I was looking for. This gave me the number of ProductNames that individually don't have AssetNumbers. This is lilely a result of my poor ariculation of the problem/question. Thank you though. I will put this in the tool box as well.

ProductA 3
ProductB 0
ProductC 2
ProductD 2

 

[HeavyD]

SELECT Count("1") As PendingCount
FROM (SELECT InventoryTable.ProductName
    FROM InventoryTable
    WHERE (((InventoryTable.AssetNumber)="pending"))
    GROUP BY InventoryTable.ProductName);

Thank you arnelgp.

This is exactly what I was looking for.

 

[HeavyD]

Well s*&^%!

Turns out I can't display the results of a query in a text box. I read that I have to use DCount(). Is there a translation for the query above to DCount() format?

 

[arnelgp]

use dlookup() to display it on your textbox.
on textbox controlsource:

=dlookup("PendingCount","theNameOfQuery")

 

[Pat Hartman]

I don't use sub queries in Access for two reasons
1. they are not optimized well by Jet/ACE so can be very slow for large recordsets
2. They are awkward to build using the QBE and when you build them in SQL view, Access will almost certainly rewrite your SQL for you in an unreadable format.

So I don't use them unless there is no other way. In this case, you have options.

qGroupPendingAssets
SELECT [ProductName]
FROM Inventory
WHERE [AssetNumber]="pending"
GROUP BY [ProductName]

The domain function as the controlSource would still be:
=dCount("*", "qGroupPendingAssets")

The thing that makes this process confusing is that there are two levels of grouping required. The first to get rid of the duplicate values, the second to count the remainder. Access SQL has no way directly of doing this whereas other SQL implementations such as SQL Server, do.

In my solution, I've used the query to eliminate the duplicates with ProductName. Then used the domain function to count the results.

With arnel's method, which is certainly not wrong, he uses the inner select to remove the duplication, the outer select to do the count and adds the third step of a dLookup to return the result. I've eliminated a step and avoided the potentially inefficient subSelect