Report on percentages of "(not)approved" proposals

[ascendantP]

Hi All,
I tried looking online for something related to this but I couldn't find anything. I'm creating a report that list multiple users providing an input of "approved" or "not approved" for a plurality of proposals. I'm trying to create a report that lists the percentage for each user that calculates the number of times the user inputs "not approved" over the total number of proposals that particular user reviewed.

For example,

Steve reviews 50 proposals, and of the 50 proposals Steve inputs 10 of those proposals to be "not approved". I need a calculated field that counts the number of times that Steve inputs "not approved" and calculates that 20% of proposals reviewed are not approved, of all the proposals he reviewed. The number of proposals are continuously being added so the number 50 will not always be the denominator.

There are at least 10 other users that I have to do the same calculation but if I'm able to do the example above, then I should be able to do the others (with everyone's expertise of course )

 

[plog]

Since you didn't post your specific table and field names, I can only tell you how to do this in general.

First, create a query to group your data by users, counting the number of total proposals they have and the number approved. The sql should look something like this:

SELECT UserField, COUNT([Proposals]) AS TotalProposals, SUM(Iif([Approved], 1,0)) AS TotalApprovals
FROM YourTableNameHere
GROUP BY UserField;

That should get you over the mental hump (the TotalApprovals field is probably the biggest hurdle you are having) . Next, you create a query based on that query and do your calculation (TotalApprovals/TotalProposals).

If you need more help, post back with your specifics.

 

[ascendantP]

Since you didn't post your specific table and field names, I can only tell you how to do this in general.

First, create a query to group your data by users, counting the number of total proposals they have and the number approved. The sql should look something like this:

SELECT UserField, COUNT([Proposals]) AS TotalProposals, SUM(Iif([Approved], 1,0)) AS TotalApprovals
FROM YourTableNameHere
GROUP BY UserField;

That should get you over the mental hump (the TotalApprovals field is probably the biggest hurdle you are having) . Next, you create a query based on that query and do your calculation (TotalApprovals/TotalProposals).

If you need more help, post back with your specifics.

Thank you so much on this plog. You are exactly right that the totalapprovals are my biggest road block. I couldn't adapt your code to work with my query because I didn't provide enough details as you stated above.

This is the structure and sample data in my table that I am trying to make a query for in order to generate a report.

Proposal ID SteveDecision FrankDecision AbbyDecision
1 Approved Not Approved Approved
2 Not Approved Approved Not Approved
3 Approved Not Approved

(I appologize for the poor formating, I could get the indents to stick in my post.)

So the report should generate: Steve's non-approval percentage: 33%, Frank's non-approval percentage: 66%, and Abby's non-approval percentage: 50% (since she did not provide input on the third proposal).

I found another way to solve this problem by using the code:

SELECT Count(tblQRDecisions.[SteveDecision]) AS [CountOfSteveDecision]
FROM tblQRDecisions
WHERE (((tblQRDecisions.[SteveDecision])="Not Approved"));

This would give me the total count of non approvals from Steve. And I could make another query for Steve to give me the Total number of approval decisions on Proposals (simply changing the WHERE clause to include Or (tblQRDecisions.[SteveDecision])="Approved"); ) and then make another query to join the two queries to give me the two numbers I need for my stat. Obviously, it is a highly inefficient way to solve this problem but that may be my solution since I have novice Access skills.

But can you clarify on what the proper syntax is for that if statement you have above to sum the total number of approvals? Would it be something like: SUM(Iif(tblQRDecisions.[SteveDecision])="Not Approved")), 1,0)) AS TotalApprovals? This way I can make one query instead of 3 separate queries for my percentage.

Thanks again

 

[ascendantP]

Nevermind, I answered my own question in my reply. In combination with the where clause statement, I was able to get all the numbers I needed for my percentage in one query. Thanks again plog! You were able to get me going on the right track.

 

[plog]

I'm glad I got you on the right track for this issue, but you're off track on the bigger one. Your table structure is improper--you shouldn't have field nor table names that hold actual information. That means your fields with people's names shouldn't be named that.

Try to be gentle when breaking this to Steve, Frank and Abby: they aren't going to be on this earth or even with your organization forever. What happens then? Rename your fields, redo your queries to use the new peoples' names? So much avoidable work.

The proper structure for this data (at least what I've seen, it may be worse) would have these fields:

tblDecisions
ProposalID, Approved, PersonId
1, True, 3
1, False, 7
1, True, 8
3, True, 3
3, False, 7

PersonID would link to your table of decision makers and not hold their names as text.

 

[ascendantP]

You're right plog. Actually, I changed the field names to be people in my example. But the actual field names are department names in my organization, I changed the department names to be people's name in my example to protect any privacy concerns but thanks for the heads up.

 

[plog]

My advice still applies. People or departments--data should not be stored in field names.