Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Exists function in Access

Posted on 2010-11-20
5
Medium Priority
?
653 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

715 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