GROUP BY and CASE STMT

[NickName]

Hi,

I'm not an Access expert and understand with MS SQL Server 7 or 2000,
a query like this would work

Select osName, count(*) as osCount

From osTable

Where osName is not Null
Group By
Case When Left(osName,7)='Windows' Then 'Windows OS' Else 'Other
OS' End

would work, however, it won't in Access, what could Access's equivalent
be to get the same result set?

TIA.

[Rich P]

try something like this:

SELECT IIf(Left(os,3)='win','windos os','other os') AS Expr1, Count(*)
AS Expr2
FROM osTable
GROUP BY IIf(Left(os,3)='win','windos os','other os');

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[NickName]

Interesting. IIF fuction never registers with me. However, I have
more than 2 scenarios to take care of,
How If ... Else if ... Else if ... Else
to replace IIF?

Does not seem to work.

Thanks.

Rich P wrote:

try something like this:

SELECT IIf(Left(os,3)='win','windos os','other os') AS Expr1, Count(*)
AS Expr2
FROM osTable
GROUP BY IIf(Left(os,3)='win','windos os','other os');

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rick Wannall]

For multiple cases, use Switch:

Select Switch(Left(os,3)="win", "Windows OS", Left(os, 3) ="mac","MAC OS",
test3, value3, test4, value4..., true, "Value '" & os & "' does not match
any test")

If you don't cover absolutely every possibility in the listed tests, always
include a test for true as the last test and some message that alerts you
according to the severity of the fact that none of your pre-defined tests
passed. Sometimes it doesn't matter.

[Rich P]

You can have nested IIF statements. If the IIF statement doesn't work
for you then you must be using either an older version of Access or
Access needs to be reinstalled. My example is using IIF on Access2000.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[NickName]

Got "invalid procedure call" error from a normal query statement.

btw, the query looks like this:
SELECT Switch(Left(os.osName,7)='Windows', "Windows OS",
Left(os.osName,4)='Unix', "Unix OS",
Left(os.osname,8)="Other OS",
true, "Others") as OS
FROM osTable

Look like the Switch is available for Module or the like but not for
normal query.

Thanks.

Rick Wannall wrote:

For multiple cases, use Switch:

Select Switch(Left(os,3)="win", "Windows OS", Left(os, 3) ="mac","MAC OS",
test3, value3, test4, value4..., true, "Value '" & os & "' does not match
any test")

If you don't cover absolutely every possibility in the listed tests, always
include a test for true as the last test and some message that alerts you
according to the severity of the fact that none of your pre-defined tests
passed. Sometimes it doesn't matter.

[NickName]

My typo, yes it works and good to know. Many thanks.

NickName wrote:

Got "invalid procedure call" error from a normal query statement.

btw, the query looks like this:
SELECT Switch(Left(os.osName,7)='Windows', "Windows OS",
Left(os.osName,4)='Unix', "Unix OS",
Left(os.osname,8)="Other OS",
true, "Others") as OS
FROM osTable

Look like the Switch is available for Module or the like but not for
normal query.

Thanks.

Rick Wannall wrote:

For multiple cases, use Switch:

Select Switch(Left(os,3)="win", "Windows OS", Left(os, 3) ="mac","MAC OS",
test3, value3, test4, value4..., true, "Value '" & os & "' does not match
any test")

If you don't cover absolutely every possibility in the listed tests, always
include a test for true as the last test and some message that alerts you
according to the severity of the fact that none of your pre-defined tests
passed. Sometimes it doesn't matter.

[Rick Wannall]

Switch works fine in queries. In any module, select Tools | References and
make sure you have Visual Basic for Applications selected.

[NickName]

Yes, "Switch works fine in queries", you're right, many thanks.

Rick Wannall wrote:

Switch works fine in queries. In any module, select Tools | References and
make sure you have Visual Basic for Applications selected.

[David W. Fenton]

"Rick Wannall" <wa*****@notadomain.de> wrote in
news:BB*******************@newssvr21.news.prodigy. com:

Switch works fine in queries. In any module, select Tools |
References and make sure you have Visual Basic for Applications
selected.

Any built-in Access function should work in queries (user-defined
ones, too). That's the whole point of the magic of the Access
Expression Service that makes Access such a powerful tool.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/