[AccessD] SQL help

Pete,

SELECT Table3.CusID, Table3.CustName, Table1.Desc, Table1.Req,
Table2.Comment
FROM (Table3 INNER JOIN Table2 ON Table3.CusID = Table2.CusID) INNER JOIN
Table1 ON Table2.ConID = Table1.ConID
WHERE (((Table3.CusID)=1) AND ((Table1.Req)=True));

Based upon what you say you want, I think you would ONLY get the following
records:

Customer ID = 1
ConID	Desc	Req	CusID	Comment
2	A2	Yes	1	Yes

Customer ID = 2
ConID	Desc	Req	CusID	Comment
1	A1	Yes	2	Me
2	A2	Yes	2	Word

Customer ID = 3
ConID	Desc	Req	CusID	Comment

The reason is:
For ID = 1, there are 2 matches in table 2 (con id =2 and 3)
	For con id=2 (in table 1), you have one match in table 1 where req =
true
	For con id=3 (in table 1), there are 0 records in table 1 where req
= true

For ID = 2, there are 2 matches in table 2 (con id =1 and 2)
	For con id=1, you have one match in table 1 where req = true
	For con id=2, you have one match in table 1 where req = true

For ID = 3, there are 0 matches in table 2

To get to table 3 from table one, you have to go through table 2

Here's the query for cusID = 1:

	SELECT Table3.CusID, Table3.CustName, Table1.Desc, Table1.Req,
Table2.Comment
		FROM (Table3 
			INNER JOIN Table2 ON Table3.CusID = Table2.CusID) 
			INNER JOIN Table1 ON Table2.ConID = Table1.ConID
		WHERE Table3.CusID=1 AND Table1.Req=True

In your expected output below for cusID=1, you will not get the conID=5 from
table 1 because there are no items in table 2 linking cusid=1 and conid=5.

Hope this helps,
Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pete Phillipps
Sent: Saturday, December 13, 2008 2:51 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] SQL help

Hi Everyone,

	I am trying to get some data from the following tables into a list.
The basics of the tables are shown below:

TABLE 1
ConID	Desc	Req
1	A1	Yes
2	A2	Yes
3	A3	No
4	A3	No
5	A5	Yes

TABLE 2
CusID	ConID	Comment
1	2	Yes
1	3	Yep
2	1	Me
2	2	Word

TABLE 3
CusID	Name
1	Fred
2	Daphne
3	Scooby

	What I want is to select a customer from table 3 and get a list of
all items from table 1 where Req is Yes, along with any matching comment
from table 2 for that customer (if there are any), like the following
examples:

ConID	Desc	Req	CusID	Comment
1	A1	Yes	1	
2	A2	Yes	1	Yes
5	A5	Yes	1	

ConID	Desc	Req	CusID	Comment
1	A1	Yes	2	Me
2	A2	Yes	2	Word
5	A5	Yes	2	

ConID	Desc	Req	CusID	Comment
1	A1	Yes	3	
2	A2	Yes	3	
5	A5	Yes	3	

	What I can't do is get a SQL join that will bring up the results
like that. I've tried the following:

SELECT Table1.ConID, Table1.Desc, Table1.Req, Table2.CusID, Table2.Comment
FROM Table2 RIGHT JOIN Table1 ON Table2.ConID = Table1.ConID
WHERE (((Table1.Req)<>0) AND ((Table2.CusID)=1))
ORDER BY Table1.ConID, Table2.CusID;

	But that just produces (for customer 1) the following:

ConID	Desc	Req	CusID	Comment
2	A2	Yes	1	Yes

	How can I construct the SQL to produce the result as required?

Pete