Results 1 to 3 of 3

Thread: [RESOLVED] SQL results in repeating data when another table is included

  1. #1

    Thread Starter
    Lively Member Phantom1's Avatar
    Join Date
    Nov 2011
    Posts
    64

    Resolved [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...

  2. #2
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    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)

  3. #3

    Thread Starter
    Lively Member Phantom1's Avatar
    Join Date
    Nov 2011
    Posts
    64

    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
  •  



Click Here to Expand Forum to Full Width