[AccessD] SQL help

Pete

...there is NO SQL that will produce ALL THREE Req = Yes data entries using 
a single CusID filter parameter from your given data ...if you actually want 
that then you must remove the CusID filter.

...if Req is a Yes/No field then the Access SQL WITH a CusID filter 
parameter would be:

SELECT Table1.ConID, Table2.CusID, Table1.Req, Table1.Desc, Table2.Comment
FROM Table1 INNER JOIN Table2 ON Table1.ConID = Table2.ConID
WHERE (((Table2.CusID)=[Enter CusID]) AND ((Table1.Req)=True))
ORDER BY Table1.ConID, Table2.CusID;

...you would replace the [Enter CusID] parameter I used with your form 
parameter ...hth.

William

--------------------------------------------------
From: "Pete Phillipps" <pete at vftt.co.uk>
Sent: Saturday, December 13, 2008 7:15 PM
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] SQL help

> Hi Bobby,
>
> <<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.>>
> The problem is that I need all three rows from table 1 where Req =
> Yes even if there are no matching entries in table 2, so that you can see
> which lines have a comment and which lines don't.
> Table 3 isn't actually needed as the query gets the customer ID from
> a text box on the main form (which displays their details) - I only 
> included
> it to show that there can be customers in table 3 and no related records 
> in
> table 2.
>
> Pete
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
> Sent: 13 December 2008 22:05
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com No virus found in this incoming
> message.
> Checked by AVG - http://www.avg.com
> Version: 8.0.176 / Virus Database: 270.9.17/1846 - Release Date: 
> 12/12/2008
> 18:59
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>