Asterix

[Love Buzz]

Hi all.

Here is my dilemma, I have a table with either one or two asterix in a
column (the same column). One asterix signifies a different outcome than
two. When I try to count one asterix and/or two, it adds them all up. Here
is the SQL view:

SELECT Count([Incoming Return Item Detail].ReturnReasonCode) AS
CountOfReturnReasonCode, Count([Incoming Return Item
Detail].RedepositIndicator) AS CountOfRedepositIndicator, Count([Incoming
Return Item Detail].RedepositIndicator) AS CountOfRedepositIndicator1
FROM [Incoming Return Item Detail]
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
HAVING (((Count([Incoming Return Item Detail].RedepositIndicator)) Like "*")
AND ((Count([Incoming Return Item Detail].RedepositIndicator)) Like "**"));

Hope that made sense.

Any suggestions?

 

[Evi]

An unfortunate choice of data, wasn't it, what with an asterisk being a Wild
Card symbol

If that field contain only either Null, 1 star or 2 then, in a new colum put
a calculation

OneStar:Val(NZ(Len([YourStarField]) = 1),0))
in the another column put
TwoStar: Val(NZ(Len([YourStarField])=2),0))

Now sum these fields in your report footer

Evi

 

[John W. Vinson]

Here is my dilemma, I have a table with either one or two asterix in a
column (the same column). One asterix signifies a different outcome than
two. When I try to count one asterix and/or two, it adds them all up. Here
is the SQL view:

If the field (not column please!!) contains nothing other than a single or a
double asterisk, just don't use the LIKE operator. LIKE treats * as a
wildcard, matching any string - so either LIKE "*" or LIKE "**" will simply
return all records where the field is not NULL.

Try moving the criterion to the WHERE clause (applied before summing and
counting) instead of the HAVING clause, and use = instead of LIKE.

Your AND between the two critera will fail, though, because you'll find all
records where the RedepositIndicator is *at one and the same time* equal to
both * and **. That can never happen of course. In addition, the COUNT
operator doesn't count values, it counts records. Try Summing a calculated
value instead:

SELECT Count([Incoming Return Item Detail].ReturnReasonCode) AS
CountOfReturnReasonCode,
Sum(IIF([Incoming Return Item Detail].RedepositIndicator = "*", 1, 0))
AS CountOfRedepositIndicator,
Sum(IIF([Incoming Return Item Detail].RedepositIndicator = "**", 1, 0))
AS CountOfRedepositIndicator1
FROM [Incoming Return Item Detail]
WHERE ((([Incoming Return Item Detail].[Date]) Between [Start Date] And [End
Date]))
AND
[Incoming Return Item Detail].RedepositIndicator IN ("*", "**")

Leave off the

AND
[Incoming Return Item Detail].RedepositIndicator IN ("*", "**")


if you want to count all records whatever the value of the RedepositIndicator,
while counting the two special values.

 

[Ron2006]

IF the * are instead the first characters of the field then change to
more like this


SELECT Count([Incoming Return Item Detail].ReturnReasonCode) AS
CountOfReturnReasonCode,
Sum(IIF(left([Incoming Return Item Detail].RedepositIndicator,2) =
"**", 0, IIF(left([Incoming Return Item Detail].RedepositIndicator,1)
= "*", 1, 0)))
AS CountOfRedepositIndicator,
Sum(IIF(left([Incoming Return Item Detail].RedepositIndicator,2) =
"**", 1, 0))
AS CountOfRedepositIndicator1
FROM [Incoming Return Item Detail]
WHERE ((([Incoming Return Item Detail].[Date]) Between [Start Date]
And [End
Date]))


Ron

 

[Love Buzz]

Thanks for your help. That did the trick

An unfortunate choice of data, wasn't it, what with an asterisk being a Wild
Card symbol

If that field contain only either Null, 1 star or 2 then, in a new colum put
a calculation

OneStar:Val(NZ(Len([YourStarField]) = 1),0))
in the another column put
TwoStar: Val(NZ(Len([YourStarField])=2),0))

Now sum these fields in your report footer

Evi

Hi all.

Here is my dilemma, I have a table with either one or two asterix in a
column (the same column). One asterix signifies a different outcome than
two. When I try to count one asterix and/or two, it adds them all up. Here
is the SQL view:

SELECT Count([Incoming Return Item Detail].ReturnReasonCode) AS
CountOfReturnReasonCode, Count([Incoming Return Item
Detail].RedepositIndicator) AS CountOfRedepositIndicator, Count([Incoming
Return Item Detail].RedepositIndicator) AS CountOfRedepositIndicator1
FROM [Incoming Return Item Detail]
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
HAVING (((Count([Incoming Return Item Detail].RedepositIndicator)) Like "*")
AND ((Count([Incoming Return Item Detail].RedepositIndicator)) Like "**"));

Hope that made sense.

Any suggestions?