Avatar of mytfein
 asked on

SQL queston: SELECT /WHERE EXISTS CLAUSE -- ACCESS 2000 platform


I would appreciate help with understanding:

1) Do you agree with the following URL:

 that it's more efficient to use WHERE EXISTS then coding:
                        SELECT COUNT(fieldA)
                        then tesing If COUNT(fieldA) > 0

2) Do you agree with the following URL that the results of a SELECT/WHERE EXISTS returns
a boolean TRUE/FALSE?


3) I want to code a SELECT/WHERE EXISTS  via VBA for ACCESS.
    In my situation, I just want to see if a value simply exists only once
       ( I am aware that I could use the Access function: DLOOKUP,
                    I just want to accomplish via SQL)

    The table has a field to hold a physical box number.
    When employees are archived, they are placed in a box and sent to offsite-storage.
    The box number id# is mm/dd/yy-#### such as 07/01/04-0001

    When employee is archived, it's written to a log table, so I do not have a box# master table.

    So if 10 employees were archived to box 07/01/04-0001, then 10 rows would exist in the log table.

   I'm just interested if 07/01/04 exists as a box# at all.
    I was hoping that the SELECT/WHERE EXISTS would return a boolean value......

    I create an ACCESS query object, and have gotten it to work, but:
            a)  without returning an IMPLICT 1 row result set of just a  boolean value
            b)  since I have 10 rows with 07/01/04-0001, am getting 10 rows of results, so added the

SELECT DISTINCT True   ' I am explicitly defining a BOOLEAN VALUE
WHERE Exists (select Left(T2.InfoArchiveBox, 8)
                                   FROM ZT_000_STAFF_INFO_LOG as T2
                                    WHERE Left(T2.InfoArchiveBox,  8) =  "07/01/04"
                                           and Left(T1.InfoArchiveBox,  8)  =  Left (T2.InfoArchiveBox,  8))

Am I on the right track? Can I get the SQL to return a boolean value? Is the above more/less efficient than testing count(fielda)?

Your feedback is most appreciated! thank you!
Microsoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon
Vitor Montalvão

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Hi VMontalvao,

Thx for your quick response....
Reading your responses backwards:

3) I did not realize that a "fancy" SELECT/where EXISTS  is not needed for my goal.
    thx for pointing it out....
    a)    Until I get more experience, how did you figure it out,
       Is it because the inner SELECT and the outer SELECT  both used the same table name?

   b)  To double-check: Is (3) a better approach than (1) to check if a value is on a table?

2) Suppose I had a meaningful SELECT/where exists statement,
      for the results to be boolean TRUE or FALSE,
             does it need to be clearly coded like this:
                     SELECT DISTINCT True  
     because I was told that Access 2000 vba/sql does not support "IF EXISTS",
                             and     Access 2000 vba/sql              supports SELECT/WHERE EXISTS
     or are you aware of another approach,
         if you possibly has a SELECT/WHERE EXISTS code snippet that was written for access 2000, where the results is a boolean true or false, without having to code it explicitly in the SELECT.....I would love to see it.....

thx in advance, mytfein

Vitor Montalvão

3a) I figured it out because the where clausule in subselect:
      WHERE Left(T2.InfoArchiveBox,  8) =  "07/01/04"
                 and Left(T1.InfoArchiveBox,  8)  =  Left (T2.InfoArchiveBox,  8)
     Logically that's mean that Left(T1.InfoArchiveBox,  8) =  "07/01/04", right? ;-)
    So you won't need the subselect.

  b) That depends in what are you checking.

2) EXISTS is a SQL Server function, but you can make your own Access Exists function, that receive a SQL Statement, then open a recorset and see if recordset.recordcount > 0 return true, else return false.

I hope that can help you.

Hi VMontalvao,

Thanks for your assistance, posted the points for your help.....

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy