Average Percent

[Linda RQ]

Hi Everyone,

Using Access 2003 and I use the query grid but post the sql so it helps you
guys. I have an audits database that we use to keep track of how correctly
our paper work is being filled out. I have many Audit Types and in each
Audit type there are Audit Items. For each audit item we select Yes, No,
or NA in the EADetailFilledOut field.

I need to report the average number of items marked yes in the
EADetailFilledOut field for one specific Audit Item from one Audit type.

I created a query and I get the correct data showing but I'm not sure how to
get the average. In my EADetailFilledOut I get both -1 (yes) and 0 (no)
returned. Can I calculate from this and if so how? It occured to me just
not that I have to make 3 queries? One that shows only -1, One that shows 0
and a final to calculate the average percent?

SELECT tblAuditItems.AItemID, tblEmpAuditDetails.EADetailFilledOut,
tblAudits.AuditName
FROM (tblAudits INNER JOIN (tblAuditItems INNER JOIN tblEmpAuditDetails ON
tblAuditItems.AItemID = tblEmpAuditDetails.EADetail_fkAItemID) ON
tblAudits.AuditID = tblAuditItems.AItem_fkAuditID) INNER JOIN
tblEmployeeAudits ON (tblEmployeeAudits.EmpAuditID =
tblEmpAuditDetails.EADetail_fkEmpAuditID) AND (tblAudits.AuditID =
tblEmployeeAudits.EmpAudit_fkAuditID)
WHERE (((tblAuditItems.AItemID)=106));

Thanks,
Linda

 

[Duane Hookom]

I'm not sure why you can't use
Avg([YourYesNoField])

 

[Linda RQ]

I did try to Average that column but I got a negative number for an answer
so I thought it was not the thing I needed to do.

Linda

I'm not sure why you can't use
Avg([YourYesNoField])

--
Duane Hookom
Microsoft Access MVP

Hi Everyone,

Using Access 2003 and I use the query grid but post the sql so it helps
you
guys. I have an audits database that we use to keep track of how
correctly
our paper work is being filled out. I have many Audit Types and in each
Audit type there are Audit Items. For each audit item we select Yes,
No,
or NA in the EADetailFilledOut field.

I need to report the average number of items marked yes in the
EADetailFilledOut field for one specific Audit Item from one Audit type.

I created a query and I get the correct data showing but I'm not sure how
to
get the average. In my EADetailFilledOut I get both -1 (yes) and 0 (no)
returned. Can I calculate from this and if so how? It occured to me
just
not that I have to make 3 queries? One that shows only -1, One that
shows 0
and a final to calculate the average percent?

SELECT tblAuditItems.AItemID, tblEmpAuditDetails.EADetailFilledOut,
tblAudits.AuditName
FROM (tblAudits INNER JOIN (tblAuditItems INNER JOIN tblEmpAuditDetails
ON
tblAuditItems.AItemID = tblEmpAuditDetails.EADetail_fkAItemID) ON
tblAudits.AuditID = tblAuditItems.AItem_fkAuditID) INNER JOIN
tblEmployeeAudits ON (tblEmployeeAudits.EmpAuditID =
tblEmpAuditDetails.EADetail_fkEmpAuditID) AND (tblAudits.AuditID =
tblEmployeeAudits.EmpAudit_fkAuditID)
WHERE (((tblAuditItems.AItemID)=106));

Thanks,
Linda

 

[John Spencer]

Since you are average a negative number, you are going to get a negative
value for the average. You can strip off the negative sign by using the
abs function.

Abs(Avg([Your Field]))



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I did try to Average that column but I got a negative number for an answer
so I thought it was not the thing I needed to do.

Linda

I'm not sure why you can't use
Avg([YourYesNoField])

--
Duane Hookom
Microsoft Access MVP

Hi Everyone,

Using Access 2003 and I use the query grid but post the sql so it helps
you
guys. I have an audits database that we use to keep track of how
correctly
our paper work is being filled out. I have many Audit Types and in each
Audit type there are Audit Items. For each audit item we select Yes,
No,
or NA in the EADetailFilledOut field.

I need to report the average number of items marked yes in the
EADetailFilledOut field for one specific Audit Item from one Audit type.

I created a query and I get the correct data showing but I'm not sure how
to
get the average. In my EADetailFilledOut I get both -1 (yes) and 0 (no)
returned. Can I calculate from this and if so how? It occured to me
just
not that I have to make 3 queries? One that shows only -1, One that
shows 0
and a final to calculate the average percent?

SELECT tblAuditItems.AItemID, tblEmpAuditDetails.EADetailFilledOut,
tblAudits.AuditName
FROM (tblAudits INNER JOIN (tblAuditItems INNER JOIN tblEmpAuditDetails
ON
tblAuditItems.AItemID = tblEmpAuditDetails.EADetail_fkAItemID) ON
tblAudits.AuditID = tblAuditItems.AItem_fkAuditID) INNER JOIN
tblEmployeeAudits ON (tblEmployeeAudits.EmpAuditID =
tblEmpAuditDetails.EADetail_fkEmpAuditID) AND (tblAudits.AuditID =
tblEmployeeAudits.EmpAudit_fkAuditID)
WHERE (((tblAuditItems.AItemID)=106));

Thanks,
Linda

 

[Linda RQ]

Hi John,

1. Thank-you! It worked.

2. Ok...I just deleted a big long explaination of an error I got but in
typing it all out, I figured it out. LOL
I needed to make a new query based on the original one with just this one
function Abs(Avg([Your Field])).........(is that called a function?). I
also had to show my totals row and select "expression". I did get the
correct answer and it was a positive number so I hope it was ok that I am
showing the totals row. I often get in trouble in these groups for clicking
that open.

3. I didn't mean for the number to be a negative. Until today, I thought it
was a "yes". I guess access shows you one thing and does something else
behind your back. <g>

Linda

Since you are average a negative number, you are going to get a negative
value for the average. You can strip off the negative sign by using the
abs function.

Abs(Avg([Your Field]))



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I did try to Average that column but I got a negative number for an
answer
so I thought it was not the thing I needed to do.

Linda

I'm not sure why you can't use
Avg([YourYesNoField])

--
Duane Hookom
Microsoft Access MVP


:

Hi Everyone,

Using Access 2003 and I use the query grid but post the sql so it helps
you
guys. I have an audits database that we use to keep track of how
correctly
our paper work is being filled out. I have many Audit Types and in
each
Audit type there are Audit Items. For each audit item we select Yes,
No,
or NA in the EADetailFilledOut field.

I need to report the average number of items marked yes in the
EADetailFilledOut field for one specific Audit Item from one Audit
type.

I created a query and I get the correct data showing but I'm not sure
how
to
get the average. In my EADetailFilledOut I get both -1 (yes) and 0
(no)
returned. Can I calculate from this and if so how? It occured to me
just
not that I have to make 3 queries? One that shows only -1, One that
shows 0
and a final to calculate the average percent?

SELECT tblAuditItems.AItemID, tblEmpAuditDetails.EADetailFilledOut,
tblAudits.AuditName
FROM (tblAudits INNER JOIN (tblAuditItems INNER JOIN tblEmpAuditDetails
ON
tblAuditItems.AItemID = tblEmpAuditDetails.EADetail_fkAItemID) ON
tblAudits.AuditID = tblAuditItems.AItem_fkAuditID) INNER JOIN
tblEmployeeAudits ON (tblEmployeeAudits.EmpAuditID =
tblEmpAuditDetails.EADetail_fkEmpAuditID) AND (tblAudits.AuditID =
tblEmployeeAudits.EmpAudit_fkAuditID)
WHERE (((tblAuditItems.AItemID)=106));

Thanks,
Linda