An IN condition answers whether a row belongs to either selected location. To require both locations, test the connection table twice. In Microsoft Access, two correlated EXISTS predicates express that requirement directly without returning duplicate items.
Last updated: October 1, 2026.
PARAMETERS [First location] Long, [Second location] Long;
SELECT T.thingId, T.name
FROM Things AS T
WHERE EXISTS (
SELECT *
FROM LocationThing AS L1
WHERE L1.thingId = T.thingId
AND L1.locationId = [First location]
)
AND EXISTS (
SELECT *
FROM LocationThing AS L2
WHERE L2.thingId = T.thingId
AND L2.locationId = [Second location]
);Each subquery asks a yes-or-no question for the current item. The outer row is returned only when the first location match and the second location match both exist.
Use a unique connection row
Add a unique composite index on LocationThing(locationId, thingId). It prevents the same item-location relationship from being inserted twice and gives Access a useful path for both existence checks. The query still returns one item row even if old duplicate connection rows are present.
Microsoft documents correlated aliases and the EXISTS predicate in its Access SQL subquery reference.
Handle identical or optional locations deliberately
If the two parameters must represent different locations, validate that rule on the form before opening the query. If selecting the same location twice should be allowed, the query naturally reduces to one practical requirement because both checks use the same value.
For parameters taken from controls, see using form controls in Access queries. The guides to finding duplicate records and the SQL IN operator help with the two most common variations of this problem.
