Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2014
    Posts
    3

    Unanswered: Duplicate row/key problem

    Hey,

    I'm creating a simple database for a construction company to keep track of their items.

    Is there a way to combine duplicate field items (refer to attached picture) and add the different quantity values?

    Here's a simplified example of what I've got:-

    2 Tables: T1 (stock in) and T2(stock out)

    T1 Fields: ID (primary), Item name, Qty In, etc..
    T2 Fields: ID_Out (primary), ID, Qty Out, etc..

    ID in T1 is linked to ID in T2 as a 1 to many relationship.

    Main Query:
    contains all fields from T1 and 'Qty Out' from T2

    query works fine except for the duplicate ID..refer to the attached picture (IDs 6 and 11 are repeated). Is there anyway to combine the repeated bit as a single entry and sum up related Qty Out field?

    Also as an add on: I display the values in 'Total' field by expression builder (Qty In - Qty Out)..it only shows a value if there is an extra corresponding to ID in my T2..Is there an added expression I can use to show Qty In values if there are no Qty Out present?

    Any help would be much appreciated
    Attached Thumbnails Attached Thumbnails sample.jpg  

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since your question appears to be about Microsoft Access, I've moved your post from the New Users forum to the Microsoft Access forum. The odds are good that you'll get more appropriate answers here.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    presumably there can be more than one entry where goods have been delivered (more than one quantity in). the basic problem you have is that you need to SUM the qty in and out for each product id

    however, what I'd expect is a different design
    a table for stock items
    a table for stock/product types
    a table for location

    how you handle stock movements is up to you... you can use a single table for all movements, called say transactions or separate tables for goods in and goods out.

    where you are at present....
    you need to sum the qty in and the qty out for each product

    so to start off
    create a simple query
    Code:
    SELECT Table1.ProductID,
     sum(Table1.[QtyIn]) AS QIn, 
     Sum(Table2.[QtyOut]) AS QOut 
     FROM Table1
     LEFT JOIN Table2 ON Table1.ProductID = Table2.ProductID
    GROUP BY Table1.ProductID;
    the sum() function used in conjunction with the group by means sum the specified column by product id

    that will give you the stock movements
    next you need to cater for the situation where there may not be a matching qty in (or qty out), use the NZ function to supply a repalcement value

    Code:
    SELECT Table1.ProductID,
     Sum(nz(Table1.[QtyIn],0)) AS QIn,
     Sum(nz(Table2.[QtyOut],0)) AS QOut
     FROM Table1
     LEFT JOIN Table2 ON Table1.ProductID = Table2.ProductID
     GROUP BY Table1.ProductID;
    to calculate the balance you withger use the values for QIn and QOut in your program or modify the query further

    Code:
    SELECT Table1.ProductID,
     Sum(nz(Table1.[QtyIn],0)) AS QIn,
     Sum(nz(Table2.[QtyOut],0)) AS QOut,
     Sum(nz(Table1.[QtyIn],0)) - Sum(nz(Table2.[QtyOut],0)) as QShakeItAllAbout
     FROM Table1
     LEFT JOIN Table2 ON Table1.ProductID = Table2.ProductID
     GROUP BY Table1.ProductID;
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2014
    Posts
    3
    Cheers buddy!

    The sum and nz functions were exactly what I needed. Everything works perfectly now!

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •