[AccessD] SQL help

Pete,
 
I might have misunderstood...if so...please let me know...but I think your  criteria is on the wrong table...
 
Try replacing ((Table2.CusID)=1)) with ((Table1.CusID)=1))
 
Let  me know...and I'll read closer...lol
 
Mark A. Matte
 
 

> From: pete at vftt.co.uk
> To: accessd at databaseadvisors.com
> Date: Sat, 13 Dec 2008 19:50:30 +0000
> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
_________________________________________________________________
You live life online. So we put Windows on the web. 
http://clk.atdmt.com/MRT/go/127032869/direct/01/