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

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)

---

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.