[AccessD] Using IN (SELECT SQL STATEMENT)

Charlotte Foust cfoust at infostatsystems.com
Wed Dec 21 11:23:36 CST 2005


Would Exists be any faster or slower?

Charlotte


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Wednesday, December 21, 2005 9:00 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Using IN (SELECT SQL STATEMENT)


I have a situation where I need to be able to update just a subset of
records in a table - where the PK of those records appear in another
table. I created a query to pull a DISTINCT set of those IDs, saved the
query and then joined my existing query to that new query.  Doing this
made the query non-updatable. By using an IN() in the where clause under
the ID that I was trying to join on, the query became updateable again.

In other words, instead of an inner join between DOC_ID and the matching
field in qselDocIDIntblSystemDocument, I used a where clause:

WHERE (((tblDocuments.DOC_ID) In (SELECT SD_IDDOC FROM
qselDocIDIntblSystemDocument)));

I have had this issue (a join causes a non-updateable query) in the past
and never really solved it.  I think this is going to solve that problem
for me.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list