-
Apr 26th, 2012, 01:35 PM
#1
Thread Starter
Lively Member
[RESOLVED] SQL results in repeating data when another table is included
Code:
"SELECT TransactnID, ProductID, QuantityBought, LineTotal " & _
"FROM tblProductTransaction " & _
"WHERE TransactnID = " & CInt(lblTransactionID.Caption)
The SQL statement is to take the different ProductIDs involved in a transaction and the quantity bought and line total of each item.
It takes TransactnID from tblProductTransaction where TransactnID is the value displayed in lblTransactionID. It works.
Now, I want to take the CustomrID also. CustomrID is found in tblTransaction where the primary key is TransactionID (which is foreign key in tblProductTransaction as TransactnID - note the missing "io"). If I am correct (which I am not, since the SQL does not give the expected result), the SQL must be
Code:
"SELECT TransactnID, CustomrID, ProductID, QuantityBought, LineTotal " & _
"FROM tblProductTransaction, tblTransaction " & _
"WHERE TransactnID = " & CInt(lblTransactionID.Caption)
CustomrID is fetched and the ProductIDs that have to be fetched get repeated many times over the report.
E.g. ProductID A123, B456 and C789 are selected, in quantities of 1, 2 and 3 respectively. The report where the data is to be displayed shows up as:
ID;Quantity
A123;1
B456;2
C789;3
A123;1
B456;2
C789;3
A123;1
B456;2
C789;3
A123;1
B456;2
C789;3
A123;1
B456;2
C789;3
etc
What is wrong with the second SQL statement?
Learning to Program on Earth until I go into Outer Space...
-
Apr 26th, 2012, 02:26 PM
#2
Re: SQL results in repeating data when another table is included
Well, you did not properly join the 2 tables, try this:
Code:
"SELECT TransactnID, CustomrID, ProductID, QuantityBought, LineTotal " & _
"FROM tblProductTransaction, tblTransaction " & _
"WHERE tblProductTransaction.TransactnID = tblTransaction.TransactnID " & _
"AND TransactnID = " & CInt(lblTransactionID.Caption)
-
Apr 26th, 2012, 02:46 PM
#3
Thread Starter
Lively Member
Re: SQL results in repeating data when another table is included
Thank You
Can You also give an explanation why tblProductTransaction.TransactnID = tblTransaction.TransactnID is also necessary? I thought TransactnID = lblTransactionID.Caption itself would point to the required record.
Learning to Program on Earth until I go into Outer Space...
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|