Solved

Exists function in Access

Posted on 2010-11-20
5
639 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

914 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

23 Experts available now in Live!

Get 1:1 Help Now