Joint Two Table Using Query

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
LVL 1
crystal_TechAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mlmccCommented:
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
 
crystal_TechAuthor Commented:
>> 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
 
mlmccCommented:
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
 
crystal_TechAuthor Commented:

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
 
mlmccCommented:
Is pf a field in the database?

mlmcc
0
 
crystal_TechAuthor Commented:
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
 
crystal_TechAuthor Commented:


Thanks its working now

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


0
All Courses

From novice to tech pro — start learning today.