Solved Percentage Calculation Issue On Report (1 Viewer)

[Ashfaque]

Hi,

I have attached my db with least records. I was trying the show the percentage of employees different nationalities working in each dept which will show on click of the btn 'Show %age' on GRAPH tab of form.

All unbound small text boxes shows percentage vertically under the each dept in graph. Pls run the db and click 'Show %age' btn to see.

The same percentages I would like to see in my report by clicking mail btn. I tried all the ways, but it producing attached error.

May be someone could help me to display the percentage figures in report graph as well (same as displayed on form graph)

 

[Ashfaque]

Finally I did it. I just placed Dlookup and counted records in each unbound text boxes in my report that represents percentages of Bars.

 

[Pat Hartman]

Sorry I didn't see this earlier. You did it the hard way though. Please look at this to see how easy the problem really was. I didn't take it to its completion since you "solved" the problem but here's the base query

SELECT T_JobOffer.CDept, IIf(T_JobOffer.CNationality="Saudi","Saudi","Other") AS Nat, Count(T_JobOffer.LetterRefNumber) AS CountOfLetterRefNumber
FROM T_JobOffer
WHERE (((T_JobOffer.CLastWorkingDate) Is Null) AND ((T_JobOffer.CNo) Is Not Null) AND ((T_JobOffer.CCrNumber)=2051223412))
GROUP BY T_JobOffer.CDept, IIf(T_JobOffer.CNationality="Saudi","Saudi","Other")
ORDER BY T_JobOffer.CDept, IIf(T_JobOffer.CNationality="Saudi","Saudi","Other");


The base query gives you the counts you need. To get the percentage, you need two more queries.
One to count the total population.
The second to join the total count to this query where you can calculate the percentage.

If you don't want the departments to be in alpha sequence, you need to add a deptCD that will sort them in the order you want them to dispay.