Merging 2 queries

[anthgav]

Hi

I have 2 queries that count items from 2 fields in the same table.

My first query is called qryBinCapacityMasterBin

SELECT tblBinType.bin_type, Count(tblAllocatedBin.allocated_bin_type) AS CountOfallocated_bin_type, Count(tblProduct.allocated_bin_1) AS
CountOfallocated_bin_1, Round(Count([tblProduct.allocated_bin_1])/Count([tblAllocatedBin.allocated_bin])*100,1) AS [Capacity%]FROM (tblAllocatedBin LEFT JOIN
tblProduct ON
tblAllocatedBin.allocated_bin_id = tblProduct.[allocated_bin_1]) LEFT JOIN tblBinType ON
tblAllocatedBin.allocated_bin_type = tblBinType.bin_type_idGROUP BY tblBinType.bin_type;

and produces this result

My second query is called qryBinCapacitySecondBin

SELECT tblBinType.bin_type, Count(tblAllocatedBin.allocated_bin_type) AS CountOfallocated_bin_type, Count(tblProduct.allocated_bin_2) AS
CountOfallocated_bin_2, Round(Count([tblProduct.allocated_bin_2])/Count([tblAllocatedBin.allocated_bin])*100,1) AS [Capacity%]FROM (tblAllocatedBin LEFT JOIN
tblBinType ON
tblAllocatedBin.allocated_bin_type = tblBinType.bin_type_id) LEFT JOIN tblProduct ON
tblAllocatedBin.allocated_bin_id = tblProduct.allocated_bin_2GROUP BY tblBinType.bin_type;

and produces this result


The 2 queries are identical except they are looking from 2 different fields. I would like to merge the results into one total. I've been trying UNION queries but have not been able to produce the desired result. What is the best way of acheiving this please?

[ranman256]

make a union query to gather both queries,
then make a final query to sum the union.
qnUnion:


select* from qryBinCapacityMasterBin
union
select* from qryBinCapacitySecondBin



then
select bin_type, sum(countofAllocated_bin_type),sum(countofAllocate d_bin1) from qnUnion.

[anthgav]

I've created this union but added UNION ALL instead of just UNION to separate lines as UNION by itself merges the 2 QCEBIN lines but does not add them together.

SELECT bin_type, CountOfallocated_bin_type, CountOfallocated_bin_1 AS allocated_bin
FROM qryBinCapacityMasterBin
UNION ALL
SELECT bin_type, CountOfallocated_bin_type, CountOfallocated_bin_2 AS allocated_bin
FROM qryBinCapacitySecondaryBin;

The result is...


I need to be able to merge all fields but allocated_bin_1 & allocated_bin_2 need to be summed.

I'm struggling to work out a query to achieve this, what is the best way to achieve this?

[aytee111]

Create a totals query from the UNION, group by bin type and sum your totals.

[anthgav]

Thanks guys I finally figured it out with your help, my final query from the union is...

SELECT qryCapacityOfMasterAndSecondaryBins.bin_type, qryCapacityOfMasterAndSecondaryBins.CountOfallocated_bin_type, Sum(qryCapacityOfMasterAndSecondaryBins.allocated_bin) AS SumOfallocated_bin
FROM qryCapacityOfMasterAndSecondaryBins
GROUP BY qryCapacityOfMasterAndSecondaryBins.bin_type, qryCapacityOfMasterAndSecondaryBins.CountOfallocated_bin_type;