data duplication in query

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • heart01
    New Member
    • Jun 2010
    • 22
    #1

    data duplication in query

    Hi again all, Im starting to feel like i live here...I have an issue with data duplicating in a query. I have been rolling around now for two days and about to throw the computer out. Here goes my issue following.

    I have two tables
    Product
    Outgoing     (I have not made a relationship) the only fields the same in these two tables are type and date - further any way i do make a relationship no info displays in the query)

    I have made a query pulling down from both product table and outgoing table. with the intent to show how many product items are left in stock.
    Code:
    [Qty]-[Qty out]
    based on selecting the type in outgoing and Qty out with the Qty coming from the Product table. Now the query seems to be working and calculating correctly but I find that everything is duplicating - I have checked that it is doing so by the ID number can someone please give me a heads up on how i sort this prob...

    the SQL for the outgoing query is as follows
    Code:
    SELECT OutgoingTBL.ID, OutgoingTBL.Date, OutgoingTBL.Type, OutgoingTBL.Size, OutgoingTBL.[Qty Out], [Qty]-[Qty Out] AS [Current Tyre Type in stock], OutgoingTBL.Freight, OutgoingTBL.[Cost Price], Products.QTY
FROM OutgoingTBL, Products
GROUP BY OutgoingTBL.ID, OutgoingTBL.Date, OutgoingTBL.Type, OutgoingTBL.Size, OutgoingTBL.[Qty Out], OutgoingTBL.Freight, OutgoingTBL.[Cost Price], Products.QTY;
    thanking you in advance
    Last edited by heart01; Jun 30 '10, 11:55 PM. Reason: incorrect SQL entered
    Tags: None
    • heart01
      New Member
      • Jun 2010
      • 22
      #2
      Originally posted by heart01
      Hi again all, Im starting to feel like i live here...I have an issue with data duplicating in a query. I have been rolling around now for two days and about to throw the computer out. Here goes my issue following.

      I have two tables
      Product
      Outgoing       (I have not made a relationship) the only fields the same in these two tables are type and date - further any way i do make a relationship no info displays in the query)

      I have made a query pulling down from both product table and outgoing table. with the intent to show how many product items are left in stock.
      Code:
      [Qty]-[Qty out]
      based on selecting the type in outgoing and Qty out with the Qty coming from the Product table. Now the query seems to be working and calculating correctly but I find that everything is duplicating - I have checked that it is doing so by the ID number can someone please give me a heads up on how i sort this prob...

      the SQL for the outgoing query is as follows
      Code:
      SELECT OutgoingTBL.ID, OutgoingTBL.Date, OutgoingTBL.Type, OutgoingTBL.Size, OutgoingTBL.[Qty Out], [Qty]-[Qty Out] AS [Current Tyre Type in stock], OutgoingTBL.Freight, OutgoingTBL.[Cost Price], Products.QTY
FROM OutgoingTBL, Products
GROUP BY OutgoingTBL.ID, OutgoingTBL.Date, OutgoingTBL.Type, OutgoingTBL.Size, OutgoingTBL.[Qty Out], OutgoingTBL.Freight, OutgoingTBL.[Cost Price], Products.QTY;
      thanking you in advance
      Well it looks like I have the above issue working now. Perhaps I was too quick to jump into asking...what I did was made a relationship to the product and outgoing tables based on type as this is the field they both have in common. I tried all joins and found one that works. So i dont get duplication now and can select on type which in turn pulls down each outgoing entry for that type and provides a total remaining stock.

        Comment

        Previous template Next
        Working...