Solved

Joint Two Table Using Query

Posted on 2011-03-19
10
644 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

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

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

803 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