complex query in M.S.Access.

I have a table in MS Access like following
ID      name      city
2      A      P
3      B      Q
4      C      R
5      A      Q
6      B      P
7      C      P

Now I need the query for records (Name in group by ) where city = P and also city = Q
so the output of records   where above both conditions must fulfil.
so the result should be

I tried ConcatRelated function, but in my actual table the fields are large so it does work.
above table is just to understand clearly.
Rajesh JoshiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
You should be able to just join to itself on NAME, and then make sure there is a row for both P and Q, like:

SELECT Table1.Name
FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.Name = Table1_1.Name
WHERE (((Table1.City)="P") AND ((Table1_1.City)="Q"));

Open in new window

Rajesh JoshiAuthor Commented:
Hi Bill Prew,

Thank you, your answer is perfect, I need some more help,
here I had two matches city='P' and city='Q' so you suggested Table1.City ="P" AND Table1_1.City ="Q"
If more match required, say 3, then more inner join required like Table1_2 ?
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, here is another approach that should be more easily extended.  For the first need you posted you could do:

SELECT t1.Name
FROM Table1 t1
WHERE t1.City = "P"
AND EXISTS (SELECT "X" FROM Table1 t2 WHERE t1.Name = t2.Name AND t2.City = "Q");

Open in new window

then, if you need another city, you can do:

SELECT t1.Name
FROM Table1 t1
WHERE t1.City = "P"
AND EXISTS (SELECT "X" FROM Table1 t2 WHERE t1.Name = t2.Name AND t2.City = "Q")
AND EXISTS (SELECT "X" FROM Table1 t2 WHERE t1.Name = t2.Name AND t2.City = "Z");

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

You can also do this without self-joining the table (once for each city value you're trying to match).

Give me a few minutes to create and test this.  brb
Rajesh JoshiAuthor Commented:
Thank you Bill Prew....
SELECT Q_28702790.Name, Count(Q_28702790.City) AS CountOfCity
FROM Q_28702790
WHERE (((Q_28702790.City) In ("P","Q","S")))
GROUP BY Q_28702790.Name
HAVING (((Count(Q_28702790.City))=3));

Open in new window

You would extend this by changing the values in the IN () clause and matching the number of values in the list to the value in the HAVING clause.
Rajesh JoshiAuthor Commented:
I am sorry, every thing is ok, I got it.

Are you using Bill's solution or mine?
Rajesh JoshiAuthor Commented:
Hi aikimark,

I am using Bill Prew's solution.
By the way your solution is applicable to this example table only. Because you have suggested use of count. If same citty appears twice or thrice then?
your solution is applicable to this example table only
The data you post is a part of the description of your problem, every bit as much as the text.  If you had posted sample data with duplicate/multiple city values, then I would have changed the SQL that I posted as a solution.  Although it would still use a Group By query, it would also have accommodated your data.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.