Counting '*' in a query

[Love Buzz]

Hello all.

I pull data from a report that uses * and ** to indicate a certain action
taken on a particular transaction. Since this is a wildcard in Access, I am
having some difficulty trying to count. It counts any field with an asterix,
whether there is one or two. Not every field has an asterix in this column,
but I am trying to count the fields with one asterix and fields with two.

Here is my SQL. Any thoughts? Thanks for your help.


SELECT [Incoming Return Item Detail].queue, [Queues and Descriptions].[Queue
Name], Count([Incoming Return Item Detail].queue) AS CountOfqueue,
Count([Incoming Return Item Detail].RedepositIndicator) AS
CountOfRedepositIndicator, Count([Incoming Return Item
Detail].RedepositIndicator) AS CountOfRedepositIndicator1
FROM [Queues and Descriptions] INNER JOIN [Incoming Return Item Detail] ON
[Queues and Descriptions].Queue = [Incoming Return Item Detail].queue
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].queue, [Queues and
Descriptions].[Queue Name]
HAVING (((Count([Incoming Return Item Detail].RedepositIndicator)) Like
"[*]") AND ((Count([Incoming Return Item Detail].RedepositIndicator)) Like
"[**]"));

 

[John W. Vinson]

Hello all.

I pull data from a report that uses * and ** to indicate a certain action
taken on a particular transaction. Since this is a wildcard in Access, I am
having some difficulty trying to count. It counts any field with an asterix,
whether there is one or two. Not every field has an asterix in this column,
but I am trying to count the fields with one asterix and fields with two.

First off... Access doesn't count fields, it counts *records*. I'd use just
Count(*) to count records, regardless of the field content. Secondly, you're
applying the criterion to *the count*, not to the content of the field!!
Count([Incoming Return Item Detail].RedepositIndicator) will be some numeric
digit, and not either an asterisk nor a pair of asterisks.

If the field RedepositIndicator contains ONLY a single or double asterisk,
then you can avoid the entire wildcard issue by using the = operator (which
treats an asterisk as just another character) rather than LIKE. In addition,
you can use the WHERE clause to filter the records before doing all the
counting, rather than counting first and then throwing away all the unselected
records. Try


SELECT [Incoming Return Item Detail].queue, [Queues and Descriptions].[Queue
Name], Count([Incoming Return Item Detail].queue) AS CountOfqueue,
Count([Incoming Return Item Detail].RedepositIndicator) AS
CountOfRedepositIndicator, Count([Incoming Return Item
Detail].RedepositIndicator) AS CountOfRedepositIndicator1
FROM [Queues and Descriptions] INNER JOIN [Incoming Return Item Detail] ON
[Queues and Descriptions].Queue = [Incoming Return Item Detail].queue
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].queue, [Queues and
Descriptions].[Queue Name]
WHERE [Incoming Return Item Detail].RedepositIndicator IN ("*", "**");

 

[John Spencer]

SELECT [Incoming Return Item Detail].queue
, [Queues and Descriptions].[Queue Name]
, Count([Incoming Return Item Detail].queue) AS CountOfqueue
, Count([Incoming Return Item Detail].RedepositIndicator = "*",1,null)
AS CountOfRedepositIndicator
, Count([Incoming Return Item Detail].RedepositIndicator="**",1,Null) AS
CountOfRedepositIndicator1
FROM [Queues and Descriptions] INNER JOIN [Incoming Return Item Detail]
ON [Queues and Descriptions].Queue = [Incoming Return Item Detail].queue
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And
[End Date]))
and RedepositIndicator in ("*","**")
GROUP BY [Incoming Return Item Detail].queue
, [Queues and Descriptions].[Queue Name]


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jeff Boyce]

Given that Access "reserves" the use of that character as a wildcard, is
there a reason you couldn't use an update query to replace all the literal
"*"s with some character Access doesn't reserve? It seems like that would
give you less problems over the long run...

(Backup before doing any update queries.)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/