Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Exists function in Access

Posted on 2010-11-20
5
Medium Priority
?
668 Views
Last Modified: 2012-05-10
In SQL server, you can check if a record exists using the EXISTS function (see link: http://www.sqlteam.com/article/using-exists).

Is there an equivalent SQL command for Access 2003?

I need to run an update query on a field in one table if a linked record does not exist in another table.
0
Comment
Question by:kittenwhisky
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 34180383
I guess you can do..

update table1 set somefield = "somevalue"
where not exists (select anyfield from table2 where table1.id = table2.matchfield)
0
 
LVL 31

Expert Comment

by:hnasr
ID: 34180453
Is there an equivalent?
Try:

For integer field:

    If DCount("*", "tblToUpdate", "fldToTest=intCriteria") > 0 Then
        'code for field exists
    Else
        'code for field not exists
    End If

For string field:

    If DCount("*", "tblToUpdate", "fldToTest='strCriteria'") > 0 Then
        'code for field exists
    Else
        'code for field not exists
    End If

0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 300 total points
ID: 34180601
Press Alt+F11 to get to the VB Editor.  Click Help, Microsoft Visual Basic Help, Data Manipulation Language, SQL Sub Queries, and have a good read.  As you can see there is an equivalent command in Access 2003
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 200 total points
ID: 34181117
Though the syntax of UPDATE statements does vary somewhat between Jet and Transact SQL - so you might want to bear that in mind too.
You can use a Frustrated Outer Join instead of EXISTS (in either platform).
(Though EXISTS generally optimises every bit as well in T-SQL and sometimes better - in both).

UPDATE Table1 LEFT OUTER JOIN Table2 ON Table1.ID = Table2.ID
SET FieldName = 'Whatever'
WHERE Table2.ID Is Null

That is essentially equivalent to the format you'll have been using:
UPDATE Table1 SET FieldName = 'Whatever'
WHERE NOT EXISTS (SELECT Null FROM Table2 WHERE Table1.ID = Table2.ID)

Though to make the same statement in T-SQL would have been like:
UPDATE Table1
SET FieldName = 'Whatever'
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table2.ID Is Null

Cheers.
0
 
LVL 4

Author Comment

by:kittenwhisky
ID: 34181476
After reading up on subqueries (thx gray), I used a tweaked version of peter's code:

UPDATE TblToUpdate INNER JOIN TblToCheck ON TblToUpdate.LinkedFld= TblToCheck.LinkedFld SET TblToUpdate.FldToUpdate= True WHERE TblToCheck.FldToCheck = somevalue

hsnar, your method only performs the check for one record, I need to update a whole batch of records, hence the update query method.

LPurvis, tx for your suggestion for working with Nulls. As both methods are equivalent, I preferred using the Exists method.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

886 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