Solved

Joining Three table using SQL

Posted on 2000-03-28
9
208 Views
Last Modified: 2010-05-02
How to join three tables using SQL

joining loan and customer tables using customer_No
 
joining loan and payment tables using
loan_No
 
 Text1.text = Payment_No
 text2.text =loan_No
 Text3.text = Customer_No


 Private sub find_click()
 
  dtpayment.recordsource = "select * from payment where Payment_No = '" & text1.text & "'"
 
  dtpayment.refresh

  dtloan.recordsource = "select* from loan where Loan_No= '" & text2.text & "'"

  dtloan.refresh

 dtcustomer.recordsource = "select * from customer where customer_No = '" & text3.text & "'"

   dtcustomer.refresh

  end sub

 How this doesn't work

  please help

  abraham
0
Comment
Question by:abrahamling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 10

Expert Comment

by:caraf_g
ID: 2663819
SELECT
FROM Payment INNER JOIN (Loan INNER JOIN CustomerTable ON Loan.[Customer No] = CustomerTable.[Customer No]) ON Payment.[Customer No] = Loan.[Customer No];
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2663836
What is your exact problem?
The only thing i can see is that you are querying 3 times the database and this could be done in 1 throw (see caraf_g's statement), and that in your 2nd statement there is no space between SELECT and *.
0
 
LVL 10

Accepted Solution

by:
caraf_g earned 100 total points
ID: 2663929
Tip: whenever you need to do complicated stuff like this in SQL you can go into Access, create a database with tables similar to your own and create a query with the handy drag & drop facilities provided. Finally, look at the sql view.

I made a small mistake in the query above in that I joined everything up on customer no.

Better:

SELECT
FROM (CustomerTable INNER JOIN Loan ON CustomerTable.[Customer No] = Loan.[Customer No]) INNER JOIN Payment ON Loan.[Loan No] = Payment.[Loan No];


And just use this as the record source for a single query.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:prax_n
ID: 2664859
If u could explain u r problem properly ...I could explain something .. i did not upderstand u r prob..
0
 
LVL 1

Expert Comment

by:simonstre
ID: 2665046
the inner joins are the best solution, but here's another one :-)


SELECT * FROM LOAN, PAYMENT, CUSTOMER WHERE [LOAN].LOAN_NO = [PAYMENT].LOAN_NO
  AND [CUSTOMER].CUSTOMER_NO =   [LOAN].CUSTOMER_NO
0
 

Author Comment

by:abrahamling
ID: 2665443
Thanks you caraf_g's

 
0
 
LVL 10

Expert Comment

by:caraf_g
ID: 2668176
Thank you? So why did you give me a "C"?

If my answer was in any way unsatisfactory, I would have preferred it if you had asked further questions, and I would have been happy to resolve outstanding problems with you. A "C" looks bad in my profile... :-(
0
 

Author Comment

by:abrahamling
ID: 2672538
Caraf_q, thanks you for your help,

  I really mean it. sorry for giving u

  a "C". but i promise, next time, i

 will definitely give a excellent Grade.
0
 
LVL 10

Expert Comment

by:caraf_g
ID: 2672567
Hey, no problem! Thanks for clearing that up ;-)
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question