Link to home
Start Free TrialLog in
Avatar of Rajesh Joshi
Rajesh JoshiFlag for India

asked on

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.
Avatar of Bill Prew
Bill Prew

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

Avatar of Rajesh Joshi


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 ?
Avatar of Bill Prew
Bill Prew

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.
I am sorry, every thing is ok, I got it.

Are you using Bill's solution or mine?
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.