Im trying to use two fields to give me a percentage in a Sum field,

[KaayJaay]

Im trying to use two fields to give me a percentage in a Sum field, one field is a Count() field and the other is a regular field(total). I am trying to get the percentage using Sum([Count]/[total]), but it keeps saying that i cannot use a subquery, but i didnt think that was a subquery.

Also I am using Microsoft Access.

KaayJaay

 

[rac2]

Here are some ideas.

SELECT widget, (COUNT(*)/SUM(amount)) AS Percentage
FROM Shipments
GROUP BY widget

This will count the numbers of each widget shipped, add up the amounts of the shipments and display the ratio. In no way is this a percentage but we can call it Percentage if we like.

You may be looking for a percentage as in what percentage of total widgets shipped were Hot Wheels. That percentage requires two queries, one to count the total number over all widgets, a second one to breakdown the count for each widget. The grand total might be obtained by using a subquery. This might work.

SELECT widget, COUNT(*)/(SELECT COUNT(*) FROM Shipments) AS Percentage
FROM Shipments
GROUP BY widget

Access may have reporting facilities to do this, look into crosstab queries.

 

[KaayJaay]

Thank You very much this is exactly what it is i was looking for.

KaayJaay