Yes / Yes + N Responses for percentage

[Sue]

I cannot find my post from yesterday.

We have approximately 1200 employees that we are entering data in a table
for their evaluations. We are entering the data currently by typing "yes" or
"no" or NA" in the following way.

Unit Month Employee Q1 Q2 Q3 Q4 Q5
(etc to Q30)

1 May Gra Yes No Yes No Yes
1 June Gra Yes Yes Yes No Yes
1 May Gra Yes Yes Yes No Yes
1 May Mus No No Yes No Yes
1 May Mus Yes Yes No No Yes

I need it to have a percentage of compliance for each employee for each
question for each month. For example: "Mus" in May for Q1 is 50%. For Q2
she is at 50% as well, but for Q5 she is at 0%.

1) We are not currently using yes/no check boxes: Should we be?
2) How do I tell Access I want to group them by employee? Is this in
report or query?
3) How do I calculate a column to count actual words that say "yes" and
"no" and ask it to add them up and give me a percentage?

I am an experience AA (but after this may need to join AA! lol) and have
been reading alot lately and I know I am close, but I also know that the
experience online is far greater than I can glean from the books I am
currently looking at. Please help me if you can. If you can let me know
exactly where to put my expressions and my sorting by groups if that is in
query or report that would be great.

I just know that once I have one column working... the other 29 will fall
easily into place... it's getting the first one to work that seems to be the
key!

Please help... thank you in advance!

Sue

 

[Sue]

And maybe... I need to get away from yes's and no's because we do have NA's
that we are trying to eliminate. So maybe I need to scratch the yes / no and
go with an a,b,c and count all of the a's and b's and add (a+b) / a (yes)....
my math teacher husband would be so proud.

Let me know if this makes sense to go away from yes no's... thanks! (Along
with my other questions of course.

Sue

 

[John Spencer]

IF you are stuck with that structure you will have a very difficult time
building a very complex query.

Is there any chance you can modify the table structure.

Unit
TheDate (a date field containing a full date) You can change May to 04/01/2010
- the first day of the relevant month)
Employee
QuestionNumber - Text or number field
Response - hold the value of the question.

You can write queries to import the existing data into this structure. You
would need to have 30 queries to do so. They would look something like the
following.

INSERT INTO NewTable
SELECT Unit
, CDate("1 " & [Month] & " 2010") as Thedate
, Employee
, "01"
, Q1
FROM ExistingTable

NOW that the data is in the proper format you can write a query to get the
desired results for any period

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Sue]

Hi John....

Thank you so much for your response. I think we are realizing how difficult
this is at this point and we need our IS department to help us design this
project.

I will give this a try and I will also share it with them because as you
have stated it is a very complex query and I am self taught in Access.

Thank you for your time and I will certainly try what you have suggested.

Have a great day!

Sue

IF you are stuck with that structure you will have a very difficult
building a very complex query.

Is there any chance you can modify the table structure.

Unit
TheDate (a date field containing a full date) You can change May to 04/01/2010
- the first day of the relevant month)
Employee
QuestionNumber - Text or number field
Response - hold the value of the question.

You can write queries to import the existing data into this structure. You
would need to have 30 queries to do so. They would look something like the
following.

INSERT INTO NewTable
SELECT Unit
, CDate("1 " & [Month] & " 2010") as Thedate
, Employee
, "01"
, Q1
FROM ExistingTable

NOW that the data is in the proper format you can write a query to get the
desired results for any period

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I cannot find my post from yesterday.

We have approximately 1200 employees that we are entering data in a table
for their evaluations. We are entering the data currently by typing "yes" or
"no" or NA" in the following way.

Unit Month Employee Q1 Q2 Q3 Q4 Q5
(etc to Q30)

1 May Gra Yes No Yes No Yes
1 June Gra Yes Yes Yes No Yes
1 May Gra Yes Yes Yes No Yes
1 May Mus No No Yes No Yes
1 May Mus Yes Yes No No Yes

I need it to have a percentage of compliance for each employee for each
question for each month. For example: "Mus" in May for Q1 is 50%. For Q2
she is at 50% as well, but for Q5 she is at 0%.

1) We are not currently using yes/no check boxes: Should we be?
2) How do I tell Access I want to group them by employee? Is this in
report or query?
3) How do I calculate a column to count actual words that say "yes" and
"no" and ask it to add them up and give me a percentage?

I am an experience AA (but after this may need to join AA! lol) and have
been reading alot lately and I know I am close, but I also know that the
experience online is far greater than I can glean from the books I am
currently looking at. Please help me if you can. If you can let me know
exactly where to put my expressions and my sorting by groups if that is in
query or report that would be great.

I just know that once I have one column working... the other 29 will fall
easily into place... it's getting the first one to work that seems to be the
key!

Please help... thank you in advance!

Sue

.

 

[Duane Hookom]

Your table isn't normalized. You might want to look at two downloads from
http://www.rogersaccesslibrary.com/forum/forum4&SID=72d456e8797881cc1a53e22258b26251.html.
The employee evaluation and At Your Survey are similar in structure but are
normalized. John Spencer has suggested a method for normalizing.