By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,916 Members | 1,286 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,916 IT Pros & Developers. It's quick & easy.

Counting text fields and displaying percentages in reports

100+
P: 114
Access 2003
I have a table (TASKS) filled with data from an external source. The table lists several tasks for employees to complete and has a text field (STATUS) that identifies where in the completion process the task is by a single alpha character (e.g. C=Complete, N=Not Started, A=Actively Working, W=Waiting, and X=Not Applicable).

I have reports that query the table and provide reports specific to a task or to who is responsible for the task, etc. I want to have a summary page at the end of my reports that explains the status of all tasks on the report with percentages.

e.g. For Task A:
20 tasks are C (complete) 20%
20 tasks are N (not started) 20%
30 tasks are A (actively working) 30%
30 tasks are W (waiting) 30%
0 tasks are X (not applicable) 0%

AND ON ANOTHER REPORT

For Joe Schmo:
0 tasks are C (complete) 0%
15 tasks are N (not started) 30%
5 tasks are A (actively working) 10%
10 tasks are W (waiting) 20%
15 tasks are X (not applicable) 30%

I've played around with the COUNT function but can't seem to get it to work in this regard. Is this sort of summary possible with the text field? Can Access produce percentages to display like this? I really appreciate any feedback anyone can offer! Thanks.
Sep 11 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this out for the first one and then see if you can work the others out.

=Sum(IIf([Status]="C",1,0)) & " tasks are C (Complete) " & (Count([Status])/100) * Sum(IIf([Status]="C",1,0))

should equate to

20 tasks are C (complete) 20%
Sep 13 '07 #2

100+
P: 114
Try this out for the first one and then see if you can work the others out.

=Sum(IIf([Status]="C",1,0)) & " tasks are C (Complete) " & (Count([Status])/100) * Sum(IIf([Status]="C",1,0))

should equate to

20 tasks are C (complete) 20%
Thanks! That displays the totals and percentages perfectly.
Sep 18 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks! That displays the totals and percentages perfectly.
You're welcome.
Sep 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.