Solved

Exists function in Access

Posted on 2010-11-20
5
638 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 125 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 30

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 75 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 50 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now