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
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
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
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
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
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
ASKER
>> 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
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
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
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
ASKER
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)
Is pf a field in the database?
mlmcc
mlmcc
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks its working now
Please Solved my second question
https://www.experts-exchange.com/questions/26893750/Crystal-Report-Field-Problem.html
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