Calculate percentage

[LMB]

Hello Everyone,

I posted a question related to this in the getting started group because I
thought I needed to make a calculation in the query to base reports on but I
have been playing around in the reports and hope I can get my task
accomplished in the report. It took me about 2 hours to get a simple query
made and about 10 minutes to get this report made to look exactly like what
I want except for the calculation.

My report gets it's data from a query. The sql from the query is below,
just in case it helps as I am not very good at explaining my databases yet.

SELECT qryEmpNameLastFirst.Employee, tblAudits.AuditName,
tblAuditItems.AItemName, tblEmpAuditDetails.EADetailFilledOut,
tblEmpAuditDetails.EADetailComments
FROM (qryEmpNameLastFirst INNER JOIN tblEmployeeAudits ON
qryEmpNameLastFirst.strEmployeeID = tblEmployeeAudits.EmpAudit_fkEmpID)
INNER JOIN (tblAudits INNER JOIN (tblAuditItems INNER JOIN
tblEmpAuditDetails ON tblAuditItems.AItemID =
tblEmpAuditDetails.EADetail_fkAItemID) ON tblAudits.AuditID =
tblAuditItems.AItem_fkAuditID) ON tblEmployeeAudits.EmpAuditID =
tblEmpAuditDetails.EADetail_fkEmpAuditID
ORDER BY qryEmpNameLastFirst.Employee, tblAudits.AuditName,
tblAuditItems.AItemName;

I have grouped my report by EmployeeName, then AuditName, then AuditItem.
On the report, everything is looking great except my EADetailFilledOut.
Under each AuditItem I have 4 numbers because 4 items have been documented.
The numbers are -1 which = Yes, 0 =No, and 2 = N/A. I would like to report
the percent of time that the employee got a Yes, or No...The N/As I don't
want to count for or against in the percentage calculation. Can I add a
control that can take the totals from each item and give me a percentage of
Y vs N?

So if the employee has this set of numbers under an item

-1
-1
0
0
2

The control I make will count all the numbers except the 2 and the returned
number will be 50%.

Thanks,
Linda

 

[Duane Hookom]

If I understand correctly, you can add text boxes in a group or report
footer with a control sources of:
=Sum( Abs([YourField]=-1) ) / Sum(Abs([YourField]<>2))
and
=Sum( Abs([YourField]=0) ) / Sum(Abs([YourField]<>2))

 

[LMB]

Hi Duane, and thanks. I am not sure how to explain my plan but I'll try...

I want to get a % for each item. This is what my report looks like so far.

Name
AuditName
ItemName
DetailFilledOut

Actual Data

Romulan Queen
Vent Sheet
Date Filled Out
-1
-1
0
0
2

ETT Placement Filled Out
0
0
0
0
0

I would like to be able to hide the 5 numbers and just show a percent of
times the item was filled out correctly. The answer to the Date Filled Out
would be 50%. The -1 means yes, the 0 means no and the 2 means N/A but the
N/A shouldn't count for or against the person. Under ETT Placement the
employee filled this out 100% of the time.

I did try your expression below but when I click on view, it show up in the
text box like this. I moved it up to the detail section where the text box
with the control source EADetailFilledOut is and then there were 5 text
boxes with this expression in it.

Sum( Abs([EADetailFilledOut]=-1) ) / Sum(Abs([EADetailFilledOut]<>2))


Thanks,
Linda

If I understand correctly, you can add text boxes in a group or report
footer with a control sources of:
=Sum( Abs([YourField]=-1) ) / Sum(Abs([YourField]<>2))
and
=Sum( Abs([YourField]=0) ) / Sum(Abs([YourField]<>2))

--
Duane Hookom
MS Access MVP
--

Hello Everyone,

I posted a question related to this in the getting started group because
I thought I needed to make a calculation in the query to base reports on
but I have been playing around in the reports and hope I can get my task
accomplished in the report. It took me about 2 hours to get a simple
query made and about 10 minutes to get this report made to look exactly
like what I want except for the calculation.

My report gets it's data from a query. The sql from the query is below,
just in case it helps as I am not very good at explaining my databases
yet.

SELECT qryEmpNameLastFirst.Employee, tblAudits.AuditName,
tblAuditItems.AItemName, tblEmpAuditDetails.EADetailFilledOut,
tblEmpAuditDetails.EADetailComments
FROM (qryEmpNameLastFirst INNER JOIN tblEmployeeAudits ON
qryEmpNameLastFirst.strEmployeeID = tblEmployeeAudits.EmpAudit_fkEmpID)
INNER JOIN (tblAudits INNER JOIN (tblAuditItems INNER JOIN
tblEmpAuditDetails ON tblAuditItems.AItemID =
tblEmpAuditDetails.EADetail_fkAItemID) ON tblAudits.AuditID =
tblAuditItems.AItem_fkAuditID) ON tblEmployeeAudits.EmpAuditID =
tblEmpAuditDetails.EADetail_fkEmpAuditID
ORDER BY qryEmpNameLastFirst.Employee, tblAudits.AuditName,
tblAuditItems.AItemName;

I have grouped my report by EmployeeName, then AuditName, then AuditItem.
On the report, everything is looking great except my EADetailFilledOut.
Under each AuditItem I have 4 numbers because 4 items have been
documented. The numbers are -1 which = Yes, 0 =No, and 2 = N/A. I would
like to report the percent of time that the employee got a Yes, or
No...The N/As I don't want to count for or against in the percentage
calculation. Can I add a control that can take the totals from each item
and give me a percentage of Y vs N?

So if the employee has this set of numbers under an item

-1
-1
0
0
2

The control I make will count all the numbers except the 2 and the
returned number will be 50%.

Thanks,
Linda

 

[Duane Hookom]

Shows up in the text box like what? Did you remember the equal sign?
Which 5 numbers do you want to hide?

--
Duane Hookom
MS Access MVP

Hi Duane, and thanks. I am not sure how to explain my plan but I'll
try...

I want to get a % for each item. This is what my report looks like so
far.

Name
AuditName
ItemName
DetailFilledOut

Actual Data

Romulan Queen
Vent Sheet
Date Filled Out
-1
-1
0
0
2

ETT Placement Filled Out
0
0
0
0
0

I would like to be able to hide the 5 numbers and just show a percent of
times the item was filled out correctly. The answer to the Date Filled
Out would be 50%. The -1 means yes, the 0 means no and the 2 means N/A
but the N/A shouldn't count for or against the person. Under ETT
Placement the employee filled this out 100% of the time.

I did try your expression below but when I click on view, it show up in
the text box like this. I moved it up to the detail section where the
text box with the control source EADetailFilledOut is and then there were
5 text boxes with this expression in it.

Sum( Abs([EADetailFilledOut]=-1) ) / Sum(Abs([EADetailFilledOut]<>2))


Thanks,
Linda

If I understand correctly, you can add text boxes in a group or report
footer with a control sources of:
=Sum( Abs([YourField]=-1) ) / Sum(Abs([YourField]<>2))
and
=Sum( Abs([YourField]=0) ) / Sum(Abs([YourField]<>2))

--
Duane Hookom
MS Access MVP
--

Hello Everyone,

I posted a question related to this in the getting started group because
I thought I needed to make a calculation in the query to base reports on
but I have been playing around in the reports and hope I can get my task
accomplished in the report. It took me about 2 hours to get a simple
query made and about 10 minutes to get this report made to look exactly
like what I want except for the calculation.

My report gets it's data from a query. The sql from the query is below,
just in case it helps as I am not very good at explaining my databases
yet.

SELECT qryEmpNameLastFirst.Employee, tblAudits.AuditName,
tblAuditItems.AItemName, tblEmpAuditDetails.EADetailFilledOut,
tblEmpAuditDetails.EADetailComments
FROM (qryEmpNameLastFirst INNER JOIN tblEmployeeAudits ON
qryEmpNameLastFirst.strEmployeeID = tblEmployeeAudits.EmpAudit_fkEmpID)
INNER JOIN (tblAudits INNER JOIN (tblAuditItems INNER JOIN
tblEmpAuditDetails ON tblAuditItems.AItemID =
tblEmpAuditDetails.EADetail_fkAItemID) ON tblAudits.AuditID =
tblAuditItems.AItem_fkAuditID) ON tblEmployeeAudits.EmpAuditID =
tblEmpAuditDetails.EADetail_fkEmpAuditID
ORDER BY qryEmpNameLastFirst.Employee, tblAudits.AuditName,
tblAuditItems.AItemName;

I have grouped my report by EmployeeName, then AuditName, then
AuditItem. On the report, everything is looking great except my
EADetailFilledOut. Under each AuditItem I have 4 numbers because 4 items
have been documented. The numbers are -1 which = Yes, 0 =No, and 2 =
N/A. I would like to report the percent of time that the employee got a
Yes, or No...The N/As I don't want to count for or against in the
percentage calculation. Can I add a control that can take the totals
from each item and give me a percentage of Y vs N?

So if the employee has this set of numbers under an item

-1
-1
0
0
2

The control I make will count all the numbers except the 2 and the
returned number will be 50%.

Thanks,
Linda

 

[LMB]

Yes, I forgot the = sign apparently. Now the return number is
0.803571428571429 but it's calculating each number so it looks like this


Date Filled Out
0.803571428571429 -1
0.803571428571429 -1
0.803571428571429 0
0.803571428571429 0
0.803571428571429

ETT Placement Filled Out
0.803571428571429 0
0.803571428571429 0
0.803571428571429 0
0.803571428571429 0
0.803571428571429 0

 

[LMB]

Whoops, I forgot to answer about the 5 numbers. Some people only have 4
audits done, some only 3. The numbers I would like to hide are the -1, 0
numbers that represent yes or no for each audit. I want to show that the
date was filled out 50% of the time correctly and ETT placement was filled
out 0% of the time correctly.

Thanks,
Linda

 

[LMB]

Hi Duane,

I am happy to say I finally got it. I had to make a few queries to base my
report on.

If you are curious to see what I had and what I finally ended up with, I can
export as an rtf and send it to you or send you screen shots. Otherwise I
don't think I am at a point that I could explain it all.

Linda

Shows up in the text box like what? Did you remember the equal sign?
Which 5 numbers do you want to hide?

--
Duane Hookom
MS Access MVP

Hi Duane, and thanks. I am not sure how to explain my plan but I'll
try...

I want to get a % for each item. This is what my report looks like so
far.

Name
AuditName
ItemName
DetailFilledOut

Actual Data

Romulan Queen
Vent Sheet
Date Filled Out
-1
-1
0
0
2

ETT Placement Filled Out
0
0
0
0
0

I would like to be able to hide the 5 numbers and just show a percent of
times the item was filled out correctly. The answer to the Date Filled
Out would be 50%. The -1 means yes, the 0 means no and the 2 means N/A
but the N/A shouldn't count for or against the person. Under ETT
Placement the employee filled this out 100% of the time.

I did try your expression below but when I click on view, it show up in
the text box like this. I moved it up to the detail section where the
text box with the control source EADetailFilledOut is and then there were
5 text boxes with this expression in it.

Sum( Abs([EADetailFilledOut]=-1) ) / Sum(Abs([EADetailFilledOut]<>2))


Thanks,
Linda

If I understand correctly, you can add text boxes in a group or report
footer with a control sources of:
=Sum( Abs([YourField]=-1) ) / Sum(Abs([YourField]<>2))
and
=Sum( Abs([YourField]=0) ) / Sum(Abs([YourField]<>2))

--
Duane Hookom
MS Access MVP
--

Hello Everyone,

I posted a question related to this in the getting started group
because I thought I needed to make a calculation in the query to base
reports on but I have been playing around in the reports and hope I can
get my task accomplished in the report. It took me about 2 hours to
get a simple query made and about 10 minutes to get this report made to
look exactly like what I want except for the calculation.

My report gets it's data from a query. The sql from the query is
below, just in case it helps as I am not very good at explaining my
databases yet.

SELECT qryEmpNameLastFirst.Employee, tblAudits.AuditName,
tblAuditItems.AItemName, tblEmpAuditDetails.EADetailFilledOut,
tblEmpAuditDetails.EADetailComments
FROM (qryEmpNameLastFirst INNER JOIN tblEmployeeAudits ON
qryEmpNameLastFirst.strEmployeeID = tblEmployeeAudits.EmpAudit_fkEmpID)
INNER JOIN (tblAudits INNER JOIN (tblAuditItems INNER JOIN
tblEmpAuditDetails ON tblAuditItems.AItemID =
tblEmpAuditDetails.EADetail_fkAItemID) ON tblAudits.AuditID =
tblAuditItems.AItem_fkAuditID) ON tblEmployeeAudits.EmpAuditID =
tblEmpAuditDetails.EADetail_fkEmpAuditID
ORDER BY qryEmpNameLastFirst.Employee, tblAudits.AuditName,
tblAuditItems.AItemName;

I have grouped my report by EmployeeName, then AuditName, then
AuditItem. On the report, everything is looking great except my
EADetailFilledOut. Under each AuditItem I have 4 numbers because 4
items have been documented. The numbers are -1 which = Yes, 0 =No, and
2 = N/A. I would like to report the percent of time that the employee
got a Yes, or No...The N/As I don't want to count for or against in the
percentage calculation. Can I add a control that can take the totals
from each item and give me a percentage of Y vs N?

So if the employee has this set of numbers under an item

-1
-1
0
0
2

The control I make will count all the numbers except the 2 and the
returned number will be 50%.

Thanks,
Linda

 

[Duane Hookom]

glad to hear you finally got this working.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I am happy to say I finally got it. I had to make a few queries to base
my report on.

If you are curious to see what I had and what I finally ended up with, I
can export as an rtf and send it to you or send you screen shots.
Otherwise I don't think I am at a point that I could explain it all.

Linda

Shows up in the text box like what? Did you remember the equal sign?
Which 5 numbers do you want to hide?

--
Duane Hookom
MS Access MVP

Hi Duane, and thanks. I am not sure how to explain my plan but I'll
try...

I want to get a % for each item. This is what my report looks like so
far.

Name
AuditName
ItemName
DetailFilledOut

Actual Data

Romulan Queen
Vent Sheet
Date Filled Out
-1
-1
0
0
2

ETT Placement Filled Out
0
0
0
0
0

I would like to be able to hide the 5 numbers and just show a percent of
times the item was filled out correctly. The answer to the Date Filled
Out would be 50%. The -1 means yes, the 0 means no and the 2 means N/A
but the N/A shouldn't count for or against the person. Under ETT
Placement the employee filled this out 100% of the time.

I did try your expression below but when I click on view, it show up in
the text box like this. I moved it up to the detail section where the
text box with the control source EADetailFilledOut is and then there
were 5 text boxes with this expression in it.

Sum( Abs([EADetailFilledOut]=-1) ) / Sum(Abs([EADetailFilledOut]<>2))


Thanks,
Linda

If I understand correctly, you can add text boxes in a group or report
footer with a control sources of:
=Sum( Abs([YourField]=-1) ) / Sum(Abs([YourField]<>2))
and
=Sum( Abs([YourField]=0) ) / Sum(Abs([YourField]<>2))

--
Duane Hookom
MS Access MVP
--

Hello Everyone,

I posted a question related to this in the getting started group
because I thought I needed to make a calculation in the query to base
reports on but I have been playing around in the reports and hope I
can get my task accomplished in the report. It took me about 2 hours
to get a simple query made and about 10 minutes to get this report
made to look exactly like what I want except for the calculation.

My report gets it's data from a query. The sql from the query is
below, just in case it helps as I am not very good at explaining my
databases yet.

SELECT qryEmpNameLastFirst.Employee, tblAudits.AuditName,
tblAuditItems.AItemName, tblEmpAuditDetails.EADetailFilledOut,
tblEmpAuditDetails.EADetailComments
FROM (qryEmpNameLastFirst INNER JOIN tblEmployeeAudits ON
qryEmpNameLastFirst.strEmployeeID =
tblEmployeeAudits.EmpAudit_fkEmpID) INNER JOIN (tblAudits INNER JOIN
(tblAuditItems INNER JOIN tblEmpAuditDetails ON tblAuditItems.AItemID
= tblEmpAuditDetails.EADetail_fkAItemID) ON tblAudits.AuditID =
tblAuditItems.AItem_fkAuditID) ON tblEmployeeAudits.EmpAuditID =
tblEmpAuditDetails.EADetail_fkEmpAuditID
ORDER BY qryEmpNameLastFirst.Employee, tblAudits.AuditName,
tblAuditItems.AItemName;

I have grouped my report by EmployeeName, then AuditName, then
AuditItem. On the report, everything is looking great except my
EADetailFilledOut. Under each AuditItem I have 4 numbers because 4
items have been documented. The numbers are -1 which = Yes, 0 =No, and
2 = N/A. I would like to report the percent of time that the employee
got a Yes, or No...The N/As I don't want to count for or against in
the percentage calculation. Can I add a control that can take the
totals from each item and give me a percentage of Y vs N?

So if the employee has this set of numbers under an item

-1
-1
0
0
2

The control I make will count all the numbers except the 2 and the
returned number will be 50%.

Thanks,
Linda