asked on

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.

Jeffrey Coachman

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

Jeffrey Coachman

see here:
Database144.mdb

Jeffrey Coachman

looks like this:

Jeffrey Coachman

Come to think of it, ...you may want to open the Sorting and Grouping options (for the report) and sort the report by "InputValue"

willjx

ASKER

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."

Jeffrey Coachman

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] & "'" &...

willjx

ASKER

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.

willjx

ASKER

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".

Jeffrey Coachman

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

willjx

ASKER

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..???

willjx

ASKER

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%.

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

willjx

ASKER

Excellent!
Easily worth 700 points

willjx

ASKER

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.

Jeffrey Coachman

I am confused here...

You are accepting your own post as the solution?

willjx

ASKER

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

Jeffrey Coachman

Ok great,
;-)

Glad I could help.
;-)

JeffCoachman

MS ACCESS Query to show % of Records that are of a &quot;Build&quot; Status selection

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