Limit query results to numeric value of text field.

[mafhobb]

I have a table that has several fields including CallID (autonumber) and SKU (text)

SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...

Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:

SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU
FROM Calls
GROUP BY Calls.SKU
HAVING ((Not (Calls.SKU) Is Null))
ORDER BY Count(Calls.CallID) DESC;

The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.

Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.

How can I make this happen?

Thanks

mafhobb

 

[Mile-O]

Create a new field in your query;

IIf(IsNumeric([YourSKUField]),CLng([YourSKUField]),0)

Then put your Between ... And ... criteria on this new field.

 

[mafhobb]

I did not know I could do that.

My final query looks like this:

SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU, IIf(IsNumeric([SKU]),CLng([SKU]),0) AS Expr1
FROM Calls
GROUP BY Calls.SKU, IIf(IsNumeric([SKU]),CLng([SKU]),0)
HAVING ((Not (Calls.SKU) Is Null) AND ((IIf(IsNumeric([SKU]),CLng([SKU]),0)) Between 24520 And 24999))
ORDER BY Count(Calls.CallID) DESC;

Thank you

mafhobb

 

[mafhobb]

What if my range is broken up in two blocks...Between 24520 and 24999 AND 35000 and 36000?

 

[mafhobb]

Got it

SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU, IIf(IsNumeric([SKU]),CLng([SKU]),0) AS Expr1
FROM Calls
GROUP BY Calls.SKU
HAVING ((Not (Calls.SKU) Is Null) AND ((IIf(IsNumeric([SKU]),CLng([SKU]),0)) Between 24600 And 24999)) OR (((IIf(IsNumeric([SKU]),CLng([SKU]),0)) Between 23000 And 24519))
ORDER BY Count(Calls.CallID) DESC;

mafhobb

 

[mafhobb]

Now, finally.....

Given the query above, how can I find the exact oposite...that is, the top 20 for all the SKUs NOT INCLUDED in the query above? This is needed to find the top 20 on all SKUs that are not numeric and/or all the SKUs that are numeric but are not included in the range above.

mafhobb

 

[Mile-O]

Between 24600 And 24999)) Between 23000 And 24519))


(Formula < 23000) OR (Formula Between 24520 And 24599) OR (Formula >= 25000)

 

[mafhobb]

Got it

SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU, [Stats Life SKU Between 23000 and 24999].Expr1
FROM Calls LEFT JOIN [Stats Life SKU Between 23000 and 24999] ON Calls.SKU = [Stats Life SKU Between 23000 and 24999].SKU
GROUP BY Calls.SKU, [Stats Life SKU Between 23000 and 24999].Expr1
HAVING ((Not (Calls.SKU) Is Null) AND (([Stats Life SKU Between 23000 and 24999].Expr1) Is Null))
ORDER BY Count(Calls.CallID) DESC;

Thanks for your help!

mafhobb