Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Joint Two Table Using Query

Posted on 2011-03-19
10
Medium Priority
?
661 Views
Last Modified: 2012-05-11
How to get data from two table which are totally different fields

i am using following code but its not giving me desire result,

    Rsldgr.Open "Select Bilty_Detail.CST, Bilty_Detail.GRDate, Bilty_Detail.Total, Bilty_Detail.BiltyNo, Receipt_Tport.G_Name, Receipt_Tport.RcptNo, Receipt_Tport.Rcpt_Date, Receipt_Tport.Amt_Rcvd from Bilty_Detail, Receipt_Tport Where Bilty_Detail.G_Name = Receipt_Tport.G_Name and Receipt_Tport.G_Name = '" & Text1.text & "'", Con, adOpenKeyset, adLockOptimistic

Result is

CST  GRDate    Total       BiltyNo    G_Name            RcptNo  Rcpt_Date  Amt_Rcvd
1       09-03-11  275.00   223334   Wester Trans      1         10-03-11     200.00
2       15-03-11  325.00   223335   Wester Trans      2         17-03-11     100.00
3       16-03-11  125.00   223336   Wester Trans      1         10-03-11     200.00
4       20-03-11  750.00   223337   Wester Trans      2         17-03-11     100.00


Result I want like this as per date and G_Name

CST  GRDate    Total       BiltyNo    G_Name            RcptNo        Rcpt_Date       Amt_Rcvd

1       09-03-11  275.00   223334   Wester Trans      
                                                                                                    1         10-03-11     200.00
2       15-03-11  325.00   223335   Wester Trans      
3       16-03-11  125.00   223336   Wester Trans      
                                                                                                    2         17-03-11     100.00
4       20-03-11  750.00   223337   Wester Trans      


How can I do this
0
Comment
Question by:crystal_Tech
  • 4
  • 4
  • 2
10 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 35173124
try

Select
        D.CST,
        D.GRDate,
        D.Total,
        D.BiltyNo,
        R.G_Name,
        R.RcptNo,
        R.Rcpt_Date,
        R.Amt_Rcvd
From Bilty_Detail D Left Join Receipt_Tport R On D.G_Name = R.RcptNo
Where R.G_Name = '" & Text1.text & "'",
Con, adOpenKeyset, adLockOptimistic
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 35173161
From Bilty_Detail D Left Join Receipt_Tport R On D.G_Name = R.RcptNo

forces the all records show from D table and from R table show the matching ones. Please note that you had it

 D.G_Name = R.G_Name  which I changed it to D.G_Name = R.RcptNo because R table doesn't have field named G_Name.

Also, try firts without Where clause as shown below to make sure the joint woorks okay. We can add the Where portion later.

Rsldgr.Open "Select D.CST, D.GRDate, D.Total, D.BiltyNo, R.G_Name, R.RcptNo, R.Rcpt_Date, R.Amt_Rcvd From Bilty_Detail D Left Join Receipt_Tport R On D.G_Name = R.RcptNo", Con, adOpenKeyset, adLockOptimistic
 
Mike
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35173171
When you join tables there needs to be a record relationship between the records of the tables.

In this case there is but it is based on the "owner" of the transaction.
There is no real relationship between the tables that relates records to each other.

This appears to be some kind of Purchase/charge in the first table and a payment in the second.

Tables with relationship example would be a Customer table with CustomerId, Name, Address, Phone etc and an Order table with customer orders that has CustomerId, Order Number, Product List, Amount, Payment type etc.

You query does a JOIN on the Name of the owner thus all records for that owner are linked or joined to every record in the second table.

What you really need is a union of the records with a field added so you can tell which record is from each table.  Something like this

Rsldgr.Open "Select Bilty_Detail.CST, Bilty_Detail.GRDate, Bilty_Detail.Total, Bilty_Detail.BiltyNo, Bilty_Detail.G_Name, 'B' as RecType  from Bilty_Detail Where Bilty_Detail.G_Name = '" & Text1.text & "'" &
"UNION ALL"
"SELECT Receipt_Tport.RcptNo, Receipt_Tport.Rcpt_Date, Receipt_Tport.Amt_Rcvd, 0 as ReceiptNo,  Receipt_Tport.G_Name from Receipt_Tport, 'R' as RecType Where Receipt_Tport.G_Name = '" & Text1.text & "'", Con, adOpenKeyset, adLockOptimistic

mlmcc
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:crystal_Tech
ID: 35175059
>> eghtebas: D.G_Name = R.RcptNo
G_Name is character and RcptNo is Number they cant match...

-----------------------------------------------------------------------------------------

 >>>>> mlmcc:
Suggested query i tried but its displayed an Run Time Error " Syntex error in query, Incomplete query clause.

Rsldgr.Open "Select Bilty_Detail.CST, Bilty_Detail.GRDate, Bilty_Detail.Total, Bilty_Detail.BiltyNo, Bilty_Detail.G_Name, 'B' as RecType  from Bilty_Detail Where Bilty_Detail.G_Name = '" & Text1.text & "' UNION ALL SELECT Receipt_Tport.RcptNo, Receipt_Tport.Rcpt_Date, Receipt_Tport.Amt_Rcvd, 0 as ReceiptNo,  Receipt_Tport.G_Name from Receipt_Tport, 'R' as RecType Where Receipt_Tport.G_Name = '" & Text1.text & "'", con, adOpenKeyset, adLockOptimistic
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35175466
Sorry.  Part of the select is after the where

Rsldgr.Open "Select Bilty_Detail.CST, Bilty_Detail.GRDate, Bilty_Detail.Total, Bilty_Detail.BiltyNo, Bilty_Detail.G_Name, 'B' as RecType  from Bilty_Detail Where Bilty_Detail.G_Name = '" & Text1.text & "' UNION ALL SELECT Receipt_Tport.RcptNo, Receipt_Tport.Rcpt_Date, Receipt_Tport.Amt_Rcvd, 0 as ReceiptNo,  Receipt_Tport.G_Name, 'R' as RecType  from Receipt_Tport  Where Receipt_Tport.G_Name = '" & Text1.text & "'", con, adOpenKeyset, adLockOptimistic

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35179800

sir
Yes i am getting result but i want to change something in query,

Getting result like this
CST   GRDate          Total       BiltyNo    G_Name        Rec_Type
2        01-01-2011   275.00    295743   West Plant     B
1        01-02-2011   100.00    0             West Plant     R
4        09-03-2011   275.00    294367   West Plant     B
2        18-03-2011   200.00    0             West Plant     R


Desire Result like this             (New)                 (New)    (New (Total - pf))  (New)  

CST   GRDate          BiltyNo    GRNo    Total       pf           Balance                Amt_Rcvd      G_Name         Rec_Type
2        01-01-2011   295743   7427      275.00   75.00    200.00                    0                     West Plant     B
0        01-02-2011   0             1            0            0             0                           100.00            West Plant     R
4        09-03-2011   294367   7637      275.00   100.00    175.00                  0                     West Plant     B
0        18-03-2011   0             2            0            0             0                           200.00            West Plant     R

Balance Columns is not in the field but its ( Total - pf = Balance)
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35181090
Is pf a field in the database?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35181171
yes

all this fields in the Bilty_Detail table
CST  
GRDate        
BiltyNo    
GRNo    
Total      
pf
G_Name


and Second table Receipt_Tport has following fields
Rcpt_Date
RcptNo
Amt_Rcvd
G_Name
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 35181331
You can add pf to the Bilty part of the query and add 0 as pf to the second part.

Just make sure they are in the same position.

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35181996


Thanks its working now

Please Solved my second question
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_26893750.html


0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

877 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