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:
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
DISTINCT to the SELECT/WHERE EXISTS
SELECT DISTINCT True ' I am explicitly defining a BOOLEAN VALUE
FROM ZT_000_STAFF_INFO_LOG AS T1
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!