Field which calculates a percentage between 2 numbers from other fields

[anthgav]

Hi



I'm trying to calculate a percentage between to other fields in an Access query.

Here is the query


SQL view
SELECT tblBinType.bin_type, Count(tblAllocatedBin.allocated_bin_type) AS CountOfallocated_bin_type, Count(tblProduct.allocated_bin) AS CountOfallocated_bin, Sum([tblProduct.allocated_bin]/[tblAllocatedBin.allocated_bin]*100) AS [Capacity %]
FROM tblBinType RIGHT JOIN (tblAllocatedBin LEFT JOIN tblProduct ON tblAllocatedBin.allocated_bin_id = tblProduct.allocated_bin) ON tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type
GROUP BY tblBinType.bin_type;




Here is the result





As you can see in the result the "Capacity %" field does not calculate the percentage between fields 2 & 3 correctly. Can have some assistance in working this out please?

Thanks

[pbaldy]

Try this untested shot-in-the-dark:

SELECT tblBinType.bin_type, Count(tblAllocatedBin.allocated_bin_type) AS CountOfallocated_bin_type, Count(tblProduct.allocated_bin) AS CountOfallocated_bin, Count([tblProduct.allocated_bin])/Count[tblAllocatedBin.allocated_bin])*100 AS [Capacity %]
FROM tblBinType RIGHT JOIN (tblAllocatedBin LEFT JOIN tblProduct ON tblAllocatedBin.allocated_bin_id = tblProduct.allocated_bin) ON tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type
GROUP BY tblBinType.bin_type

[anthgav]

Works perfectly, thank you Sir.

Just one missing opening bracket in your query...
Count([tblProduct.allocated_bin])/Count[tblAllocatedBin.allocated_bin])*100

Should be
Count([tblProduct.allocated_bin])/Count([tblAllocatedBin.allocated_bin])*100

The result...




Just one more thing, how to I round the result to a whole number please?

[June7]

Use Round() function or DecimalPlaces property of textbox.

[pbaldy]

I'll get out of the way.

[anthgav]

I tried the Round() function like this highlighted in red but I'm getting a "Syntax error (missing operator) in query expression" message.

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

[June7]

AS [Capacity %] goes outside the function.

[anthgav]

Thanks guys, much appreciated.