How do I create a subquery with three linking tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have three tables tbl_Payments with pmt_Payment_Cust_Id relating to
tbl_Subscriptions with sub_Cust_ID
and the third table, tlkp_Subscription_Names using sn_Abbrev as a join to tbl_Subscriptions sub_Newsletter_Code field.

When I run a query asking for
sub_Cust_Id from tbl_Subscriptions, sub_Newsletter_Code from tbl_subscriptions (using criteria Not like "F*"), sub_Subscr_Status from tbl_Subscriptions (using "A" as a criteria), sub_Billing_Frequency from tbl_Subscriptions (with select criteria "A" or "Q" or "M" or "S"), sn_Billing_Type from tlkp_Subscription_Names (using "R" criteria), sub_Subscr_End_Date from tbl_Subscriptions (using criteria >=#1/5/2004#) and lastly pmt_Payment_method from tbl_Payments I get the Cartesian product result in my query. Does this mean that my relations to the tables are incorrect and that I have to do a subquery or Union select statement? The tlkp_Subscription_Names table has no way to relate customer_id which I believe is the problem-but I have to have this table so that I can get the pmt_Payment_Method field in the results. How can I do this? Unfortunately I am not that well versed in SQL, Select statements and Union queries and the like.

Any information you can give me is greatly appreciated! Thanks in advance.
 
First, post the actual SQL statement (copy and paste from SQL view of the
query) into your message. It's too difficult to try to figure out what the
actual SQL statement is from your text.

Second, from what you've posted, it appears that your SQL does not contain
any joins between the tables, thus you would get a cartesian product as the
result.

Let's start with the actual SQL that you have and then we'll go from there.

--
Ken Snell
<MS ACCESS MVP>

CLH said:
I have three tables tbl_Payments with pmt_Payment_Cust_Id relating to
tbl_Subscriptions with sub_Cust_ID
and the third table, tlkp_Subscription_Names using sn_Abbrev as a join to
Click to expand...
tbl_Subscriptions sub_Newsletter_Code field.
When I run a query asking for
sub_Cust_Id from tbl_Subscriptions, sub_Newsletter_Code from
Click to expand...
tbl_subscriptions (using criteria Not like "F*"), sub_Subscr_Status from
tbl_Subscriptions (using "A" as a criteria), sub_Billing_Frequency from
tbl_Subscriptions (with select criteria "A" or "Q" or "M" or "S"),
sn_Billing_Type from tlkp_Subscription_Names (using "R" criteria),
sub_Subscr_End_Date from tbl_Subscriptions (using criteria >=#1/5/2004#) and
lastly pmt_Payment_method from tbl_Payments I get the Cartesian product
result in my query. Does this mean that my relations to the tables are
incorrect and that I have to do a subquery or Union select statement? The
tlkp_Subscription_Names table has no way to relate customer_id which I
believe is the problem-but I have to have this table so that I can get the
pmt_Payment_Method field in the results. How can I do this? Unfortunately
I am not that well versed in SQL, Select statements and Union queries and
the like.
Any information you can give me is greatly appreciated! Thanks in
Click to expand...
advance.
 
Could be that you got a cartesian product ( * in SQL code ) iby not joining
the multiple tables in your select query.
 
Build your select query a step at a time. Join the tables with your select
criteria in the WHERE clause.
 
That is my problem-I am not that familiar with creating an SQL statement. I am trying to do this in the cloumn line in design view of Access as an Expression. Here is what I have come up with but it is not working. I have to say I am real "NEW" to this area and apologize for asking for so much-but any help you can give is greatly appreciated!


(SELECT pmt_Payment_Cust_ID
FROM tbl_payments LEFT JOIN tbl_Sunscriptions ON (tbl_payments.pmt_Payment_Cust_ID = tbl_subscriptions.sub_Cust_ID))

I guess I need to join the cust_id's of both tables but then what happens with the relation of the payment table? The fact that the sub_Newsletter_Code and sn_abbreviation are joined would that then allow the query to work after executing a proper SQL statement?

Thanks again.
 
That is my problem I don't know where to begin other than where I did on the SELECT statement I posted a moment ago. I am totally lost. At this point I am considering some relationship searching with the other tables to try and join the Subsrciptions name in to the other two without using a subquery. Would that work versus using a Join and the Where clause?
 
All right. Let's back up.

What do you want the query to show? Let's start with that and then we'll
figure out how to create that query.

--
Ken Snell
<MS ACCESS MVP>

CLH said:
That is my problem-I am not that familiar with creating an SQL statement.
Click to expand...
I am trying to do this in the cloumn line in design view of Access as an
Expression. Here is what I have come up with but it is not working. I have
to say I am real "NEW" to this area and apologize for asking for so much-but
any help you can give is greatly appreciated!
(SELECT pmt_Payment_Cust_ID
FROM tbl_payments LEFT JOIN tbl_Sunscriptions ON
Click to expand...
(tbl_payments.pmt_Payment_Cust_ID = tbl_subscriptions.sub_Cust_ID))
I guess I need to join the cust_id's of both tables but then what happens
Click to expand...
with the relation of the payment table? The fact that the
sub_Newsletter_Code and sn_abbreviation are joined would that then allow the
query to work after executing a proper SQL statement?
 
I want the query to show all customer's who get certain newsletters by quarterly, annually and monthly and are of a Retail type subscription who pay by credit card as a recurring charge.
 
To create a join, in the QBE window add two tables you want to join. Drag
the common field from one table to the similar table in the other table
creating the join (line forms connecting two fields). Two tables must
contain a common field in order to be joined.

On the main menu select SQL View to see the resulting query. Copy/paste to
your posting if you want more help.
 
I have done the joins and still get the duplicate results-
the joins were done first. I even went in and looked at
the join properties and changed from 1 to 2 to 3 and
still the same results:
sub_Cust_ID sub_Subscr_End_Date
sub_Newsletter_Code pmt_Payment_Method
2 3/22/2004 X1X VISA
2 1/27/2004 X2X AMEX
2 3/22/2004 OOO VISA
2 3/22/2004 OOO MC
2 3/22/2004 OOO AMEX
2 3/22/2004 MMM MC
2 3/22/2004 MMM Check
2 3/22/2004 MMM AMEX

As you can see Cust2 has various subs but some of the
same subs have three different payment types. I have
verified that cust_id and pymnt_cust_id are both the same
type field in the table. I have tried SELECT statements
and join statements both getting errors of, "expression
entered contatins invalid syntax.

Hope this paste data helps (I have changed some of the
data in it so I didn't include any real data from my
tables)

Thanks,
CLH
 
It would be helpful to see your SQL string. Change from Design View to SQL
view. Copy/paste the SLQ string for this query to your post.
 
Also, there can be no duplicate index values in either table that you want
to join. If you have duplicate keys you will get a cartesian product. The
ID field must always contain unique values. No duplicates. These must
correspond to the ID value in the table being joined.
 
SELECT [tbl_Payments]![pmt_Payment_Cust_ID] WHERE
[sub_Cust_ID] = [tbl_Payments]![pmt_Payment_Cust_ID]
 
You appear to have several problems. First you need to have a common key
field with unique values for each record on two tables. This might be a
customer ID number, for example. The data you posted is missing this
requirement.

Add two tables to the query. Join the key fields. A type 1 join will only
include records where the same key field exists in one record in both
tables. Types 2 & 3 joins allow you to join all records in one table to any
records in the other if there is a match.

Once you produce a "join" query correctly and can see results when you run
it, you then will be able to modify the query to limit the query result to a
subset based on criteria contained in one or more fields. This may be "and"
or "or" logic for multiple qriteria.

To build a Where clause change the "Group By" box for the field to "Where".
On the criteria line type in a data field such as "AMEX" would select only
data records for that field equal to the criteria. Wildcards are allowed.
"Like A*" would return all records beginning with "A". "Like ?B??" returns
all 4 character strings containing "B" in the second character position.
 
SELECT CustomerID
FROM tblCustomers INNER JOIN (tblSubscriptions
ON tblCustomers.CustomerID = tblSubscriptions.CustomerID)
INNER JOIN tblPayments ON tblCustomers.CustomerID = tblPayments.CustomerID
WHERE tblSubscriptions.Frequency = "Quarterly" AND
tblSubscriptions.SubType = "Retail" AND tblPayments.PaymentMethod =
"CreditCard";

Something such as the above should get you started.

--
Ken Snell
<MS ACCESS MVP>


CLH said:
I want the query to show all customer's who get certain newsletters by
Click to expand...
quarterly, annually and monthly and are of a Retail type subscription who
pay by credit card as a recurring charge.
 
Thank you-I will try the join. I appreciate all your help and hope you had a good weekend

Regards
CLH
 
Back
Top