By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,271 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,271 IT Pros & Developers. It's quick & easy.

No results from query of joined tables

P: n/a
I have two tables, one contains contacts and the other has all the
events and years that the contacts participated in. What I want is to
be able to choose the years, say 2005 and 2006, and get all the
contacts that attended in both 2005 and 2006.

Table1
ContactID ContactName
1 John
2 Bob
3 Amy
4 Sue

Table2
ContactID Year
1 2005
2 2005
2 2006
3 2004
3 2005
4 2004
4 2005
4 2006

If I use this Query
-----------
SELECT Table1.*
FROM Table1 LEFT JOIN [Table2] ON Table1.ContactID =
[Table2].ContactID
WHERE ((([Table2].Year)="2005" OR ([Table2].Year)="2006"));
-------------
then I get all contacts that attended either 2005 or 2006, and the
results show duplicates for any contacts that attended both. So using
the example I would get
Results:
John
Bob
Bob
Amy
Sue
Sue

However if I change the OR to AND in attempt to get contacts that
attended both years only
---------
SELECT Table1.*
FROM Table1 LEFT JOIN [Table2] ON Table1.ContactID =
[Table2].ContactID
WHERE ((([Table2].Year)="2005" AND ([Table2].Year)="2006"));
----------
then I do not get any results. I would assume this is because in my
Contact Events table their are multiple rows for the contact and they
are either 2005 or 2006 on each row but not both on the same row so I
do not get any results.

What I want to get would be
Results:
Bob
Sue
How can a query this so it gives me all contacts that attended both
years using the joined Contact Events table? Any help would be
greatly appreciated.

Thanks...

Jul 23 '07 #1
Share this Question
Share on Google+
2 Replies

P: n/a
When you use the AND, Access looks to find any record where the year is 2005
and 2006 at the same time. Since 2005 is never the same as 2006 in any
record, it doesn't find any matches.

What you are wanting is the people who have a record in 2005, and also have
another record in 2006. To get Access to examine 2 records at once, you need
another approach such as a subquery.

This query returns everyone who attended 2005:
SELECT Table1.ContactID, Table1.ContactName
FROM Table1 INNER JOIN Table2
ON Table1.ContactID = Table2.ContactID
WHERE Table2.[Year] = 2005;

Now to add the requirement that they attended 2006 as well:
SELECT Table1.ContactID, Table1.ContactName
FROM Table1 INNER JOIN Table2
ON Table1.ContactID = Table2.ContactID
WHERE Table2.[Year] = 2005
AND EXISTS (SELECT ContactID
FROM Table2 AS Dupe
WHERE (Dupe.ContactID = Table1.ContactID)
AND (Dupe.[Year] = 2006));

That's untested, but hopefully gives the idea.
If subqueries are new, this should get you started:
http://allenbrowne.com/subquery-01.html#AnotherRecord

Note that I used an inner join: since you are applying criteria against the
table on the outer side of the join, anyone who has never been (so Year
would be Null) is excluded.

Also, we added square brackets around the word Year. Since it is a reserved
word, it can cause problems. For a list of name to avoid when designing your
tables, see:
http://allenbrowne.com/AppIssueBadWord.html

I have assumed that the Year field is a Number, though the layout of your
example makes me concerned you may have used check boxes in different
columns. That would not be a good idea:
http://allenbrowne.com/casu-23.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<KT*****@gmail.comwrote in message
news:11*********************@q75g2000hsh.googlegro ups.com...
>I have two tables, one contains contacts and the other has all the
events and years that the contacts participated in. What I want is to
be able to choose the years, say 2005 and 2006, and get all the
contacts that attended in both 2005 and 2006.

Table1
ContactID ContactName
1 John
2 Bob
3 Amy
4 Sue

Table2
ContactID Year
1 2005
2 2005
2 2006
3 2004
3 2005
4 2004
4 2005
4 2006

If I use this Query
-----------
SELECT Table1.*
FROM Table1 LEFT JOIN [Table2] ON Table1.ContactID =
[Table2].ContactID
WHERE ((([Table2].Year)="2005" OR ([Table2].Year)="2006"));
-------------
then I get all contacts that attended either 2005 or 2006, and the
results show duplicates for any contacts that attended both. So using
the example I would get
Results:
John
Bob
Bob
Amy
Sue
Sue

However if I change the OR to AND in attempt to get contacts that
attended both years only
---------
SELECT Table1.*
FROM Table1 LEFT JOIN [Table2] ON Table1.ContactID =
[Table2].ContactID
WHERE ((([Table2].Year)="2005" AND ([Table2].Year)="2006"));
----------
then I do not get any results. I would assume this is because in my
Contact Events table their are multiple rows for the contact and they
are either 2005 or 2006 on each row but not both on the same row so I
do not get any results.

What I want to get would be
Results:
Bob
Sue
How can a query this so it gives me all contacts that attended both
years using the joined Contact Events table? Any help would be
greatly appreciated.

Thanks...
Jul 23 '07 #2

P: n/a
This loosk like what I am looking for, I will try it out and see if I
can get it to work. Thank you for the advice as well, I am failry new
to this and learning as I go.

Thanks,
Keegan

Jul 23 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.