Criteria based on value in another field

[karusya]

Hello,



I have a query in Access with the following fields (grouped):

"Name" "Facility" "Number of Visits"

I want to limit the query to only pull those names that have at least one visit to a specific facility (let's say facility "A"), but I also need to see all of the other facilities that those who visited Facility A visited. So in this case "= "A"" in the criteria section of the Facility field wouldn't work. I think I need to plug something in the criteria section under the Name field, but I am not sure what.

Thanks for your help!

[Rawb]

like you need a SubQuery!

Make a Query that shows you a list of of everyone that visited facility A.

Then Make a Query that shows you a list of all the facilities visited by Visitor B.

Then stick 'em together! You'll probably end up with something like so:

SELECT
  *
FROM
  Visits
WHERE
  Visitor IN 
  (
    SELECT
      Visitor
    FROM 
      Visits
    WHERE 
      Facility="Facility A"
  );