Solved Group By Name then Count on criteria of another field

[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")