Solved

Joint Two Table Using Query

Posted on 2011-03-19
10
643 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 33

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 33

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 100

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
 
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 100

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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 100

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 100

Accepted Solution

by:
mlmcc earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now