CODEWITHSUNDEEP
sqlms-access
J. Doe
J. Doe

Reputation: 199

MS Access IIF using IN condition

Im trying to add a field into a table that indicates whether the value exists in another table. Heres what I have

table1:     table2:
id          id  
1           2
2           4
3           6

End result:

table3:
id   inTable2
1    no
2    yes
3    no

Heres what I have so far.

SELECT id, IIf(id In (SELECT id FROM table2 GROUP BY id), "yes", "no") AS inTable2 INTO table3

I'm getting error "An action query cannot be used by a rowsource". Any help would be appreciated, thanks!

Upvotes: 1

Views: 772

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You are close. The group by is unnecessary:

IIf(id In (SELECT id FROM table2), "yes", "no")

However, I'm not 100% sure this will fix your problem. This will work in a SELECT query, but your error suggests a different type of query.

You can try putting an index on table2(id). However, I might write this as:

iif(exists (select 1 from table2 where table2.id = ?.id), "yes", "no")

This should take advantage of an index on table2(id).

Upvotes: 1

Erik A
Erik A

Reputation: 32642

You can probably optimize this query by using a LEFT JOIN instead:

SELECT t1.id, IIF(t2.id IS NULL, "yes", "no") INTO table3
FROM table1 t1
LEFT JOIN table2 t2 ON t1.ID = t2.ID

Upvotes: 1

Related Questions