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](https://www.accessforums.net/attachment.php?s=0b3e59ce0821c79aff0ceb0cbf9d1568&attachmentid=30363&d=1505853820&thumb=1)
Thanks.