Solved

How to select cast to boolean

Posted on 2006-06-21
12
11,192 Views
Last Modified: 2008-01-09
Does MS ACCESS support CASE statements like sql?

I basically want to do this:

select customerNo,
(select case count(*) from visits where visits.customerNo = customerNo
          when 0 then false
          else true
          end)
from
Customers

basically, getting a true or false column if they have ever had a visit.
0
Comment
Question by:jjacksn
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 168 total points
ID: 16954342
The closest I know is perhaps to use IIF

SELECT CustomerNo, IIF(Count(*)=0,True,False) AS BoolValue
FROM Visits

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 166 total points
ID: 16954346
>Does MS ACCESS support CASE statements like sql?
No, Access does not use CASE WHEN...THEN...END, only Iff(condition, value if true, value if false)
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 16954351
(too slow)
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 58

Assisted Solution

by:harfang
harfang earned 166 total points
ID: 16955824
Hello jjacksn

The comparison itself will evaluate to a boolean. You do not need a function call:

select customerNo,
(select count(*)>0 from visits where customerNo = Customers.customerNo) As HasVisits
from Customers

(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16955836
Or, for that matter:

select customerNo,
customerNo In (select customerNo from visits) as HasVisits
from Customers

Cheers!
(°v°)
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 16956827
For speed, I think I'd vote with rockiroads and jimhorn's suggestions
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 16956845
But I think you should write the query like this:

select customerNo, iif(count(*) > 0 , true, false)
from Customers as c
 inner join visits as v
 on c.customerno = v.customerno
group by customerNo

or like this

select customerNo, iif(cc>0, true, false)
FROM (
select customerNo, count(*) as cc
from Customers as c
 inner join visits as v
 on c.customerno = v.customerno
group by customerNo
) as subquery

Evaluate for speed and let us know which is quicker for you.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16956978
I beg to differ, but

    IIf( something > 0, True, False ) As Result

is exactly equivalent to

    ( something > 0 ) As Result

only slower. Why call a VB function to tell us that true is true and false is false?

(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16956995
Also, ala_frosty, if you use an inner join, you will only have customers with visits, and hence the condition will always evaluate to true... It would be like saying: select distinct customerNo from visits.

(°v°)
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 16989467
There's a forest around here somewhere .. if I could only see 'round these pesky trees ...
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question