MS ACCESS Query to show % of Records that are of a "Build" Status selection

I have a report that successfully shows the degree of success of specific Performance Measures with the use of a color coded, “Status”.   Status is the result of a Query “Build”, calculating the input data against the desired Target:   “Red”: More that 10% below target, “Yellow” within 10% of Target, “Green”, At or Exceeds Target.
There is a series of similar reports for each category of the Dept.
Now, the user wants me to include on these same reports–probably on the footer, the % of the total record list for the given Dept that are “Green”.
For example, for one Dept, there are a total of 17 records, and of those, 5 are “Green”.
Consequently, on the footer of this particular report, it should read:    29.4% are “Green”.
I need to know the most efficient use of Query(s) to allow this note to be put on the Report.
willjxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
You did not post any of the field names or the datatypes, ...nor did you specify how this query determines the "Color", ...etc, ... so I can only post a generic example.
You can probably use a dcount()

=Dcount("*","YourTable","YourField='Red'" & " And " & "Dept=" & YourDept)

So in this situation you are getting a count of all the "Reds" for the current Department

So then you could do something like this in a control in the group footer, to display  the count, then add the text: "are Red"

=Dcount("*","YourTable","YourField='Red'" & " And " & "Dept=" & YourDept) & " are Red."


But I would need a simple copy of your database for the exact syntax though .

JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
see here:
Database144.mdb
0
Jeffrey CoachmanMIS LiasonCommented:
looks like this:
look like this:
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jeffrey CoachmanMIS LiasonCommented:
Come to think of it, ...you may want to open the Sorting and Grouping options (for the report) and sort the report by "InputValue"
0
willjxAuthor Commented:
Thanks.
However, I can't seem to get it to work.
I really like the idea of just putting it into the Report 'group' footer.  But maybe I have some syntax error?
I tried various versions of the 2 examples below, and i just got:
# error at the bottom of my report.

The 3rd example I tried on another report, and the bottom of the report just displayed the script as shown.

=DCount("*","QpmStatusProgram","Program=" & [Program] & " AND " & "Status='Red'") & " are Red."

=DCount("*","QpmStatusProgram","Level=" & [Program] & " AND " & "Status='Green'") & " are Green."

=DCount("*","QpmStatusDivision","Division=" & [Division] & " AND " & "Status='Red'") & " are Red."
0
Jeffrey CoachmanMIS LiasonCommented:
1. Always try one expression first to see if that works.
Then add the others

2. Why are you changing the criteria field for each of the expressions?
See the sample db I posted...
It is always:  Dept= ...

3. As I mentioned in my earlier post, ...you did not specify the datatype of your fields.
So if Dept is a string, the syntax would be:
"Dept=" & "'" & [Program]  & "'" &...
0
willjxAuthor Commented:
1.  I understand.  I am using MS ACCESS 2007.  
2. I see that it works for you DB.  I need for it to work in my environment.
          In Report design mode there is a warning about the "&".  ??

3.  Yes.  I notice that your "Dept" is NUMERIC.  Mine is text -string.  So, I will try it with the quotes.

Also remember in the final solution I need the script to have the result:

 29.4% are “Green”- "Percent of performance measures that meet the target".

Thanks.
0
willjxAuthor Commented:
ok.
I got the script you provided to replicate what happened in your DB.
"5 are Green" -Good.

Now.  apparently needed is for  the script to count the # records, and divide the "5" by the # of records to get the requested result:

 29.4% are “Green”- "Percent of performance measures that meet the target".
0
Jeffrey CoachmanMIS LiasonCommented:
Then it would be roughly something like this:

=Format((Dcount("*","YourTable","YourField='Red'" & " And " & "Dept=" & YourDept))/dcount("*","YourTable"),"Percent") & " are Red -Percent of performance measures that meet the target."
Basically this:
(YourSpecifiCount)/(TotalCount)   ...formatted as a Percent:
Format(YourSpecifiCount/TotalCount,"Percent")

But I am sure you can get the correct syntax...
;-)

JeffCoachman
0
willjxAuthor Commented:
This is what I typed:

=Format((Dcount("*","QpmStatusProgram","Status='Red'" & " And " & "Program=" & “Program”))/dcount("*","QpmStatusProgram"),"Percent") & " are Red -Percent of performance measures that meet the target."

The report runs and just displays the above script..at the bottom..???
0
willjxAuthor Commented:
ok
At the bottom of the same report that from the earlier script shows:

5 are Green

the below script:
 
=Format((DCount("*","QpmStatusProgram","Programs='Green'  " & " And " & "Programs=" & "Programs"))/DCount("*","QpmStatusProgram"),"Percent") & "are Green-Percent of performance measures that meet the target."

shows:

0.00%are Green-Percent of performance measures that meet the target.
It should show 29%.
0
Jeffrey CoachmanMIS LiasonCommented:
Then it sounds like:
    DCount("*","QpmStatusProgram")
...is returning Zero,   ...because any number divide by zero, is zero.

<At the bottom of the same report>
Can you clearly define what you are calling "the bottom" of the report?
Group Footer?, page Footer?, Report Footer?

Your original question was for these summaries to appear in the Group footer.
Is that now working? Yes or No?

If yes, then your original issue is resolved..., ...that is what my sample displayed.
If not, ...then you will have to do some troubleshooting:
Note:
This is what I typed:
=Format((Dcount...

<just displays the above script..at the bottom..??? >
...Typed where?
Anytime you see an expression starting with an equal sign ( = ), then that expression will typically be used as the ControlSource of a textbox.

Put 4 texboxes in the group footer:
txt1
=Dcount("*","QpmStatusProgram","Status='Red'" & " And " & "Program=" & “Program”)

txt2
=Dcount("*","QpmStatusProgram")

txt2
=txt1/txt2

txt4
=Format( txt2,"Percent")

txt5
=txt4 & " Your ending text here."


If your question now is how to do this for the "entire" report, ...then do something like this in the Report Footer (in a textbox, txt100):
=Dcount("*","QpmStatusProgram","Status='Red'")
Then do this is in another textbox (txt101):
=Dcount("*","QpmStatusProgram")
Then this in another(txt102)
=txt100/txt101
Then this (txt103)
=Format(txt102,"Percent")
Then finally this:
=txt103 * " your ending text


Then tell me what you get for each box...

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
willjxAuthor Commented:
Excellent!
Easily worth 700 points
0
willjxAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for willjx's comment #a39513882

for the following reason:

I thought I was going  to have to create a couple of extra sub queries to achieve the solution.
The solution. all done in Report Design, lends it to easily be copy pasted into all the similar sub division reports.
Thank you.
0
Jeffrey CoachmanMIS LiasonCommented:
I am confused here...

You are accepting your own post as the solution?
0
willjxAuthor Commented:
No.
Being new to this process.
I inadvertently posted my acknoweldgement of YOUR solution, and then realized I had to click on "Accept as Solution".
My error.  My appologies.
willjx
0
Jeffrey CoachmanMIS LiasonCommented:
Ok great,
;-)

Glad I could help.
;-)

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.