Counting occurrances of a string

[lana_faith]

Okay, so I have a table, created from an action query which shows all orders for a specific RepID. One of the fields in this table is ProductName. There are many many prouct names in this table, and potentially many more as more product is added. Many of the names of the products are very very similar. what I want to do is create a query which calculates the number of occurrances of a certain string in the field ProductName... for example...

Some of the values in ProductName are

Exp
ExpUp
Exp/CHS
Dig
DigUp
DigDBB
Dig/CHS

etc.

What would be nice is if I can count the number of occurrances of, say "Exp" in the created table. I'm sitting here with help manuals and such and I'm completely stumped... I'd appreciate some bigger brains than mine taking a look at this one please... Thank you!

Lana F Call

 

[Ryko]

To count the number of times "Exp" appears as standalone value:

SELECT COUNT(ProductName) As Count
FROM YourTable
WHERE ProductName = "Exp"

"Exp" anywhere in a string:

SELECT COUNT(ProductName) As Count
FROM YourTable
WHERE ProductName Like "*" & "Exp" & "*"

Count the number of times any product appears in your table:

SELECT ProductName, COUNT(ProductName) As Count
FROM YourTable
GROUP BY ProductName

 

[pbaldy]

SELECT Count(*) AS CountOfWhatever
FROM TableName
WHERE ProductName Like "*Exp*"

Edit: oops, too slow

 

[lana_faith]

thanks so much you two... i think I see where I was making my mistakes now. you've been a huge help!