468,316 Members | 1,522 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

Counting text fields and displaying percentages in reports

114 100+
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
3 1870
MMcCarthy
14,534 Expert Mod 8TB
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
martin DH
114 100+
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
14,534 Expert Mod 8TB
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.

Similar topics

8 posts views Thread by BiNZGi | last post: by
3 posts views Thread by Megan | last post: by
18 posts views Thread by ChadDiesel | last post: by
4 posts views Thread by Dado | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by howard w | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.