Results 1 to 9 of 9
  1. #1
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86

    Sum column values with the same ID and incorporate into a bigger query with grouping

    Hi

    I want to sum some rows that have the same ID and group them. I can successfully do this ok using the following code...

    Code:
    SELECT
    tblBay.bay,
    Sum(tblBin.bin_width_mm) AS SumOfbin_width_mm
    FROM 
    tblBay LEFT JOIN tblBin ON tblBay.[bay_id] = tblBin.[bay]
    GROUP BY tblBay.bay;
    I have another query that pulls into a pivot table in excel and I want to incorporate the above code with the existing query I have. The existing query is below...
    Code:
    SELECT
    tblWarehouse.warehouse,
    tblArea.area,
    tblBay.bay,
    tblBay.bay_width_mm,
    tblBay.bay_depth_mm,
    tblBay.bay_height_mm,
    tblBin.bin,
    tblBin.bin_width_mm,
    tblBinType.priority,
    tblBinType.bin_type,
    tblProduct.product,
    tblProduct.dimension,
    tblProduct.comment
    FROM
    ((((tblWarehouse LEFT JOIN tblArea ON tblWarehouse.[warehouse_id] = tblArea.[warehouse])
    LEFT JOIN tblBay ON tblArea.[area_id] = tblBay.[area])
    LEFT JOIN tblBin ON tblBay.[bay_id] = tblBin.[bay])
    LEFT JOIN (tblBinType RIGHT JOIN tblAllocatedBin ON tblBinType.[bin_type_id] = tblAllocatedBin.[allocated_bin_type]) ON tblBin.[bin_id] = tblAllocatedBin.[allocated_bin])
    LEFT JOIN tblProduct ON tblAllocatedBin.[allocated_bin_id] = tblProduct.[allocated_bin]
    GROUP BY
    tblWarehouse.warehouse,
    tblArea.area, tblBay.bay,
    tblBay.bay_width_mm,
    tblBay.bay_depth_mm,
    tblBay.bay_height_mm,
    tblBin.bin, tblBin.bin_width_mm,
    tblBinType.priority,
    tblBinType.bin_type,
    tblProduct.product,
    tblProduct.dimension,
    tblProduct.comment
    ORDER BY
    tblWarehouse.warehouse,
    tblArea.area,
    tblBay.bay,
    tblBin.bin,
    tblBinType.priority;
    Below is an image of my pivot table. I want the sum of "bin_width_mm" that has a relation to each bay to show up as a column after "bay" and grouped to bay. I am struggling to incorporate this into my existing query, can anyone help please?

    Click image for larger version. 

Name:	Query.PNG 
Views:	7 
Size:	44.6 KB 
ID:	30363



    Thanks.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did you try doing another query that JOINS the two?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    I have added
    Code:
    Sum(tblBin.bin_width_mm) AS SumOfbin_width_mm
    
    into the select query underneath
    Code:
    tblBay.bay,
    The resulting column just mirrors the same information as "bin_width_mm" in the pivot table. I've tried messing around with the joins without any success and is difficult explain. I'm not sure what I need to do with the joins to make this work.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why do you have a GROUP BY clause in the second query when there are no aggregate calcs?

    Seems can remove the GROUP BY and join the two queries on the [bay] fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    I've removed the GROUP BY and the query still works.
    I then add
    Code:
    Sum(tblBin.bin_width_mm) AS SumOfbin_width_mm,
    into the SELECT under
    Code:
    tblBay.bay,
    and I get an error message saying"You query does not include the specified 'warehouse' as part of an aggregate function. I've then tried looking at ways to add
    Code:
    FROM 
    tblBay LEFT JOIN tblBin ON tblBay.[bay_id] = tblBin.[bay]
    into the query. There is already a left join of
    Code:
    tblBin ON tblBay.[bay_id] = tblBin.[bay]
    in the original query so I'm not sure what I do with the joins to make this work.

    You'll have to excuse my ignorance as I'm pretty new to manually creating SQL queries.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I did not suggest you add an expression. I suggest you build another query that JOINS the two queries you posted. Queries can be JOINED same as tables can.

    Where are you 'manually' creating SQL? Use Access query builder to get correct structure. Pull both queries in query designer and set link on the common bay fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    I think I went about this the hard way.

    I didn't realise I could join queries together like that. I've finally sussed it out thanks.

    This has now presented an additional problem I'd like to resolve.

    In my query before combining the two together my result looked like this.
    Click image for larger version. 

Name:	Query.PNG 
Views:	5 
Size:	93.4 KB 
ID:	30382

    After combining the queries my result misses off values from the bottom of 'warehouse' and 'area' columns where the values have nothing linked to them.
    Click image for larger version. 

Name:	Query 2.jpg 
Views:	5 
Size:	175.3 KB 
ID:	30383

    What can cause this?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Is the query using INNER JOIN? Try LEFT or RIGHT.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    Sorted now, it was the join. Thanks for your patience.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-29-2015, 03:25 PM
  2. Replies: 14
    Last Post: 07-13-2015, 12:47 PM
  3. Incorporate UI into a Query??
    By nick404 in forum Queries
    Replies: 6
    Last Post: 06-07-2015, 09:08 PM
  4. Replies: 5
    Last Post: 04-07-2015, 02:20 PM
  5. Replies: 2
    Last Post: 01-29-2014, 01:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums