Yes responses divided by yes + No responses for percentage

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

We have our data in a table for our evaluation of employees. What we need is
group or total the number of Yes and No responses to equal the perscentage of
times the employee is meeting the standard we have established. Please
direct me as to where I need to do this so I can have:

Sue Yes
Sue Yes
Sue Yes
Sue Yes
Sue No


Become one line stating:

Sue 80% compliance.

Is this in query or in a report?

Please help... thank you in advance.
 
Try this --
SELECT Employee, (Abs(Sum([Eval]))/Count([Eval])* 100) AS [% Compliance]
FROM YourTable
GROUP BY Employee;
 
Very sorry for my ignorance, but where would I put this? Does this go in the
report or the query? I have been reading for a few hours on how to do this
and I'm a little confused right now.

I have 30 columns that I need to do this with and 1200 employees. So, I'm
hoping once I get one column to work, I should be good to go!

Name Q1 Q2 Q3.... etc.
1200 of them

Thank you SO much... :)

I'm an AA, not a programmer... :)

Sue

KARL DEWEY said:
Try this --
SELECT Employee, (Abs(Sum([Eval]))/Count([Eval])* 100) AS [% Compliance]
FROM YourTable
GROUP BY Employee;

--
Build a little, test a little.


Sue said:
We have our data in a table for our evaluation of employees. What we need is
group or total the number of Yes and No responses to equal the perscentage of
times the employee is meeting the standard we have established. Please
direct me as to where I need to do this so I can have:

Sue Yes
Sue Yes
Sue Yes
Sue Yes
Sue No


Become one line stating:

Sue 80% compliance.

Is this in query or in a report?

Please help... thank you in advance.
Click to expand...
Click to expand...
 
I have 30 columns that I need to do this
You have a 'Spreadsheet' instead of a relational database.

First to get your data in the proper structure you need a union query. A
union query can only be done in SQL view. Create a select query, open in
design view, click on VIEW - SQL View. The new window has the query in SQL
(Structured Query Language).

It will look something like this --
SELECT Employee, Q1, Q2, Q3, Q4, .......... Q29, Q30
FROM YourTable;

You will need to edit it to look like this --
SELECT Employee, 1 AS [Question], Q1 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 2 AS [Question], Q2 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 3 AS [Question], Q3 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 4 AS [Question], Q4 AS [Eval]
FROM YourTable
...........
UNION ALL SELECT Employee, 30 AS [Question], Q30 AS [Eval]
FROM YourTable;

When the qryMyUnion is run the data will look like this --
Employee Question Eval
Jim 1 -1
Jim 2 0
Jim 3 -1
Jim 4 -1
Bill 1 -1
Bill 2 -1
Bill 3 0
Bill 4 0

Then you can create a query in design view by clicking on the Greek symbol
that looks like an 'M' on its side ( ∑ ) and laid out like this --
FIELD Employee % Compliance: (Abs(Sum([Eval]))/Count([Eval])* 100)
TABLE qryMyUnion
TOTAL Group By Expression

--
Build a little, test a little.


Sue said:
Very sorry for my ignorance, but where would I put this? Does this go in the
report or the query? I have been reading for a few hours on how to do this
and I'm a little confused right now.

I have 30 columns that I need to do this with and 1200 employees. So, I'm
hoping once I get one column to work, I should be good to go!

Name Q1 Q2 Q3.... etc.
1200 of them

Thank you SO much... :)

I'm an AA, not a programmer... :)

Sue

KARL DEWEY said:
Try this --
SELECT Employee, (Abs(Sum([Eval]))/Count([Eval])* 100) AS [% Compliance]
FROM YourTable
GROUP BY Employee;

--
Build a little, test a little.


Sue said:
We have our data in a table for our evaluation of employees. What we need is
group or total the number of Yes and No responses to equal the perscentage of
times the employee is meeting the standard we have established. Please
direct me as to where I need to do this so I can have:

Sue Yes
Sue Yes
Sue Yes
Sue Yes
Sue No


Become one line stating:

Sue 80% compliance.

Is this in query or in a report?

Please help... thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Karl is correct (as usual). If you would like to see a normalized survey
application, download "At Your Survey" found at
http://www.rogersaccesslibrary.com/forum/topic3.html.

--
Duane Hookom
MS Access MVP


KARL DEWEY said:
You have a 'Spreadsheet' instead of a relational database.

First to get your data in the proper structure you need a union query. A
union query can only be done in SQL view. Create a select query, open in
design view, click on VIEW - SQL View. The new window has the query in
SQL
(Structured Query Language).

It will look something like this --
SELECT Employee, Q1, Q2, Q3, Q4, .......... Q29, Q30
FROM YourTable;

You will need to edit it to look like this --
SELECT Employee, 1 AS [Question], Q1 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 2 AS [Question], Q2 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 3 AS [Question], Q3 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 4 AS [Question], Q4 AS [Eval]
FROM YourTable
..........
UNION ALL SELECT Employee, 30 AS [Question], Q30 AS [Eval]
FROM YourTable;

When the qryMyUnion is run the data will look like this --
Employee Question Eval
Jim 1 -1
Jim 2 0
Jim 3 -1
Jim 4 -1
Bill 1 -1
Bill 2 -1
Bill 3 0
Bill 4 0

Then you can create a query in design view by clicking on the Greek symbol
that looks like an 'M' on its side ( ∑ ) and laid out like this --
FIELD Employee % Compliance: (Abs(Sum([Eval]))/Count([Eval])* 100)
TABLE qryMyUnion
TOTAL Group By Expression

--
Build a little, test a little.


Sue said:
Very sorry for my ignorance, but where would I put this? Does this go in
the
report or the query? I have been reading for a few hours on how to do
this
and I'm a little confused right now.

I have 30 columns that I need to do this with and 1200 employees. So,
I'm
hoping once I get one column to work, I should be good to go!

Name Q1 Q2 Q3.... etc.
1200 of them

Thank you SO much... :)

I'm an AA, not a programmer... :)

Sue

KARL DEWEY said:
Try this --
SELECT Employee, (Abs(Sum([Eval]))/Count([Eval])* 100) AS [%
Compliance]
FROM YourTable
GROUP BY Employee;

--
Build a little, test a little.


:

We have our data in a table for our evaluation of employees. What we
need is
group or total the number of Yes and No responses to equal the
perscentage of
times the employee is meeting the standard we have established.
Please
direct me as to where I need to do this so I can have:

Sue Yes
Sue Yes
Sue Yes
Sue Yes
Sue No


Become one line stating:

Sue 80% compliance.

Is this in query or in a report?

Please help... thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Thank you Duane and Karl. I could not find my question from yesterday. Now
I have found it and will study your answers and try to apply it to my table.

I did repost thinking I had lost my question.... so if you would not mind
looking at that to see if it better explains what I need that would be great.


I am hopeful that this will work and will check back with you to confirm or
deny my success!! :)

Thank you so much....

Sue

Duane Hookom said:
Karl is correct (as usual). If you would like to see a normalized survey
application, download "At Your Survey" found at
http://www.rogersaccesslibrary.com/forum/topic3.html.

--
Duane Hookom
MS Access MVP


KARL DEWEY said:
I have 30 columns that I need to do this
Click to expand...
You have a 'Spreadsheet' instead of a relational database.

First to get your data in the proper structure you need a union query. A
union query can only be done in SQL view. Create a select query, open in
design view, click on VIEW - SQL View. The new window has the query in
SQL
(Structured Query Language).

It will look something like this --
SELECT Employee, Q1, Q2, Q3, Q4, .......... Q29, Q30
FROM YourTable;

You will need to edit it to look like this --
SELECT Employee, 1 AS [Question], Q1 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 2 AS [Question], Q2 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 3 AS [Question], Q3 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 4 AS [Question], Q4 AS [Eval]
FROM YourTable
..........
UNION ALL SELECT Employee, 30 AS [Question], Q30 AS [Eval]
FROM YourTable;

When the qryMyUnion is run the data will look like this --
Employee Question Eval
Jim 1 -1
Jim 2 0
Jim 3 -1
Jim 4 -1
Bill 1 -1
Bill 2 -1
Bill 3 0
Bill 4 0

Then you can create a query in design view by clicking on the Greek symbol
that looks like an 'M' on its side ( ∑ ) and laid out like this --
FIELD Employee % Compliance: (Abs(Sum([Eval]))/Count([Eval])* 100)
TABLE qryMyUnion
TOTAL Group By Expression

--
Build a little, test a little.


Sue said:
Very sorry for my ignorance, but where would I put this? Does this go in
the
report or the query? I have been reading for a few hours on how to do
this
and I'm a little confused right now.

I have 30 columns that I need to do this with and 1200 employees. So,
I'm
hoping once I get one column to work, I should be good to go!

Name Q1 Q2 Q3.... etc.
1200 of them

Thank you SO much... :)

I'm an AA, not a programmer... :)

Sue

:

Try this --
SELECT Employee, (Abs(Sum([Eval]))/Count([Eval])* 100) AS [%
Compliance]
FROM YourTable
GROUP BY Employee;

--
Build a little, test a little.


:

We have our data in a table for our evaluation of employees. What we
need is
group or total the number of Yes and No responses to equal the
perscentage of
times the employee is meeting the standard we have established.
Please
direct me as to where I need to do this so I can have:

Sue Yes
Sue Yes
Sue Yes
Sue Yes
Sue No


Become one line stating:

Sue 80% compliance.

Is this in query or in a report?

Please help... thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
That is amazing and if I could figure out how to build that type of survey I
would be held as some kind of "goddess" within our organization!!

That is SO what I want. Where do I start to learn how to build such a
creature??

Sue

Duane Hookom said:
Karl is correct (as usual). If you would like to see a normalized survey
application, download "At Your Survey" found at
http://www.rogersaccesslibrary.com/forum/topic3.html.

--
Duane Hookom
MS Access MVP


KARL DEWEY said:
I have 30 columns that I need to do this
Click to expand...
You have a 'Spreadsheet' instead of a relational database.

First to get your data in the proper structure you need a union query. A
union query can only be done in SQL view. Create a select query, open in
design view, click on VIEW - SQL View. The new window has the query in
SQL
(Structured Query Language).

It will look something like this --
SELECT Employee, Q1, Q2, Q3, Q4, .......... Q29, Q30
FROM YourTable;

You will need to edit it to look like this --
SELECT Employee, 1 AS [Question], Q1 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 2 AS [Question], Q2 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 3 AS [Question], Q3 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 4 AS [Question], Q4 AS [Eval]
FROM YourTable
..........
UNION ALL SELECT Employee, 30 AS [Question], Q30 AS [Eval]
FROM YourTable;

When the qryMyUnion is run the data will look like this --
Employee Question Eval
Jim 1 -1
Jim 2 0
Jim 3 -1
Jim 4 -1
Bill 1 -1
Bill 2 -1
Bill 3 0
Bill 4 0

Then you can create a query in design view by clicking on the Greek symbol
that looks like an 'M' on its side ( ∑ ) and laid out like this --
FIELD Employee % Compliance: (Abs(Sum([Eval]))/Count([Eval])* 100)
TABLE qryMyUnion
TOTAL Group By Expression

--
Build a little, test a little.


Sue said:
Very sorry for my ignorance, but where would I put this? Does this go in
the
report or the query? I have been reading for a few hours on how to do
this
and I'm a little confused right now.

I have 30 columns that I need to do this with and 1200 employees. So,
I'm
hoping once I get one column to work, I should be good to go!

Name Q1 Q2 Q3.... etc.
1200 of them

Thank you SO much... :)

I'm an AA, not a programmer... :)

Sue

:

Try this --
SELECT Employee, (Abs(Sum([Eval]))/Count([Eval])* 100) AS [%
Compliance]
FROM YourTable
GROUP BY Employee;

--
Build a little, test a little.


:

We have our data in a table for our evaluation of employees. What we
need is
group or total the number of Yes and No responses to equal the
perscentage of
times the employee is meeting the standard we have established.
Please
direct me as to where I need to do this so I can have:

Sue Yes
Sue Yes
Sue Yes
Sue Yes
Sue No


Become one line stating:

Sue 80% compliance.

Is this in query or in a report?

Please help... thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
I'm reading.... that's why I'm so quiet. :)

I'm getting "normalized".

Sue

Duane Hookom said:
Karl is correct (as usual). If you would like to see a normalized survey
application, download "At Your Survey" found at
http://www.rogersaccesslibrary.com/forum/topic3.html.

--
Duane Hookom
MS Access MVP


KARL DEWEY said:
I have 30 columns that I need to do this
Click to expand...
You have a 'Spreadsheet' instead of a relational database.

First to get your data in the proper structure you need a union query. A
union query can only be done in SQL view. Create a select query, open in
design view, click on VIEW - SQL View. The new window has the query in
SQL
(Structured Query Language).

It will look something like this --
SELECT Employee, Q1, Q2, Q3, Q4, .......... Q29, Q30
FROM YourTable;

You will need to edit it to look like this --
SELECT Employee, 1 AS [Question], Q1 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 2 AS [Question], Q2 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 3 AS [Question], Q3 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 4 AS [Question], Q4 AS [Eval]
FROM YourTable
..........
UNION ALL SELECT Employee, 30 AS [Question], Q30 AS [Eval]
FROM YourTable;

When the qryMyUnion is run the data will look like this --
Employee Question Eval
Jim 1 -1
Jim 2 0
Jim 3 -1
Jim 4 -1
Bill 1 -1
Bill 2 -1
Bill 3 0
Bill 4 0

Then you can create a query in design view by clicking on the Greek symbol
that looks like an 'M' on its side ( ∑ ) and laid out like this --
FIELD Employee % Compliance: (Abs(Sum([Eval]))/Count([Eval])* 100)
TABLE qryMyUnion
TOTAL Group By Expression

--
Build a little, test a little.


Sue said:
Very sorry for my ignorance, but where would I put this? Does this go in
the
report or the query? I have been reading for a few hours on how to do
this
and I'm a little confused right now.

I have 30 columns that I need to do this with and 1200 employees. So,
I'm
hoping once I get one column to work, I should be good to go!

Name Q1 Q2 Q3.... etc.
1200 of them

Thank you SO much... :)

I'm an AA, not a programmer... :)

Sue

:

Try this --
SELECT Employee, (Abs(Sum([Eval]))/Count([Eval])* 100) AS [%
Compliance]
FROM YourTable
GROUP BY Employee;

--
Build a little, test a little.


:

We have our data in a table for our evaluation of employees. What we
need is
group or total the number of Yes and No responses to equal the
perscentage of
times the employee is meeting the standard we have established.
Please
direct me as to where I need to do this so I can have:

Sue Yes
Sue Yes
Sue Yes
Sue Yes
Sue No


Become one line stating:

Sue 80% compliance.

Is this in query or in a report?

Please help... thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
That is SO what I want. Where do I start to learn how to build such a
creature??
Click to expand...

Download it from Duane's website; open it; study it; even use it. Read his
description on the website.
 
Yes, it is a spreadsheet that I Imported from Excel. Will this not work?

Duane Hookom said:
Karl is correct (as usual). If you would like to see a normalized survey
application, download "At Your Survey" found at
http://www.rogersaccesslibrary.com/forum/topic3.html.

--
Duane Hookom
MS Access MVP


KARL DEWEY said:
I have 30 columns that I need to do this
Click to expand...
You have a 'Spreadsheet' instead of a relational database.

First to get your data in the proper structure you need a union query. A
union query can only be done in SQL view. Create a select query, open in
design view, click on VIEW - SQL View. The new window has the query in
SQL
(Structured Query Language).

It will look something like this --
SELECT Employee, Q1, Q2, Q3, Q4, .......... Q29, Q30
FROM YourTable;

You will need to edit it to look like this --
SELECT Employee, 1 AS [Question], Q1 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 2 AS [Question], Q2 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 3 AS [Question], Q3 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 4 AS [Question], Q4 AS [Eval]
FROM YourTable
..........
UNION ALL SELECT Employee, 30 AS [Question], Q30 AS [Eval]
FROM YourTable;

When the qryMyUnion is run the data will look like this --
Employee Question Eval
Jim 1 -1
Jim 2 0
Jim 3 -1
Jim 4 -1
Bill 1 -1
Bill 2 -1
Bill 3 0
Bill 4 0

Then you can create a query in design view by clicking on the Greek symbol
that looks like an 'M' on its side ( ∑ ) and laid out like this --
FIELD Employee % Compliance: (Abs(Sum([Eval]))/Count([Eval])* 100)
TABLE qryMyUnion
TOTAL Group By Expression

--
Build a little, test a little.


Sue said:
Very sorry for my ignorance, but where would I put this? Does this go in
the
report or the query? I have been reading for a few hours on how to do
this
and I'm a little confused right now.

I have 30 columns that I need to do this with and 1200 employees. So,
I'm
hoping once I get one column to work, I should be good to go!

Name Q1 Q2 Q3.... etc.
1200 of them

Thank you SO much... :)

I'm an AA, not a programmer... :)

Sue

:

Try this --
SELECT Employee, (Abs(Sum([Eval]))/Count([Eval])* 100) AS [%
Compliance]
FROM YourTable
GROUP BY Employee;

--
Build a little, test a little.


:

We have our data in a table for our evaluation of employees. What we
need is
group or total the number of Yes and No responses to equal the
perscentage of
times the employee is meeting the standard we have established.
Please
direct me as to where I need to do this so I can have:

Sue Yes
Sue Yes
Sue Yes
Sue Yes
Sue No


Become one line stating:

Sue 80% compliance.

Is this in query or in a report?

Please help... thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Yes, it is a spreadsheet that I Imported from Excel. Will this not work?
Click to expand...

Excel is a spreadsheet, best of breed.
Access is a relational database development environment.

THEY ARE DIFFERENT!!!!

A good spreadsheet can be a lousy table, and vice versa.

"You can drive nails with a crescent wrench, but that doesn't make it a
hammer".

You can certainly use the *data* from a spreadsheet in a normalized database,
but you'll need to run Append and Update queries to migrate it from the
wide-flat structure appropriate for a spreadsheet into a tall-thin structure
which works with a database.
 
I totally see what you are saying. Spreadsheet vs. database... got it.

I also after reading everything everyone has said understand that we need to
find out who our Access guru is here and get them involved because this is
certainly turning out to be something that while I am learning much, I am not
the appropriate individual to be setting this up. Time to solicit help from
our IS team.

I do love a challenge, this is getting to be bigger than I thought. :)

Thank you so much, both of you, for all of your time and instructions.

I did go to Duane's website to check out the ATS and it is very cool.
Totally what I think we should be using.

Thank you again for your help and have a blessed Easter.

Sue
 
I did not realize I could make changes to try the survey I downloaded...
thank you.

I think I am making progress.... I will have something to present to IS for
an idea of what we need.

Thank you... thank you!! :)

Sue
 
I am so excited! The first "At Your Survey" is exactly what I need!! It is
giving me all 15 questions and how many yes and how many no..... it also
give me beautiful graphs next to each question! This is amazing.... I am so
excited!

Whoa! This is just SO cool!

How do I alter this to use for my purpose or is that not allowed? How do I
enter my data and delete the data currently in the database??

"Teach me to fish" guys!!! I want to be able to do this!! :)

Sue



Duane Hookom said:
Karl is correct (as usual). If you would like to see a normalized survey
application, download "At Your Survey" found at
http://www.rogersaccesslibrary.com/forum/topic3.html.

--
Duane Hookom
MS Access MVP


KARL DEWEY said:
I have 30 columns that I need to do this
Click to expand...
You have a 'Spreadsheet' instead of a relational database.

First to get your data in the proper structure you need a union query. A
union query can only be done in SQL view. Create a select query, open in
design view, click on VIEW - SQL View. The new window has the query in
SQL
(Structured Query Language).

It will look something like this --
SELECT Employee, Q1, Q2, Q3, Q4, .......... Q29, Q30
FROM YourTable;

You will need to edit it to look like this --
SELECT Employee, 1 AS [Question], Q1 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 2 AS [Question], Q2 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 3 AS [Question], Q3 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 4 AS [Question], Q4 AS [Eval]
FROM YourTable
..........
UNION ALL SELECT Employee, 30 AS [Question], Q30 AS [Eval]
FROM YourTable;

When the qryMyUnion is run the data will look like this --
Employee Question Eval
Jim 1 -1
Jim 2 0
Jim 3 -1
Jim 4 -1
Bill 1 -1
Bill 2 -1
Bill 3 0
Bill 4 0

Then you can create a query in design view by clicking on the Greek symbol
that looks like an 'M' on its side ( ∑ ) and laid out like this --
FIELD Employee % Compliance: (Abs(Sum([Eval]))/Count([Eval])* 100)
TABLE qryMyUnion
TOTAL Group By Expression

--
Build a little, test a little.


Sue said:
Very sorry for my ignorance, but where would I put this? Does this go in
the
report or the query? I have been reading for a few hours on how to do
this
and I'm a little confused right now.

I have 30 columns that I need to do this with and 1200 employees. So,
I'm
hoping once I get one column to work, I should be good to go!

Name Q1 Q2 Q3.... etc.
1200 of them

Thank you SO much... :)

I'm an AA, not a programmer... :)

Sue

:

Try this --
SELECT Employee, (Abs(Sum([Eval]))/Count([Eval])* 100) AS [%
Compliance]
FROM YourTable
GROUP BY Employee;

--
Build a little, test a little.


:

We have our data in a table for our evaluation of employees. What we
need is
group or total the number of Yes and No responses to equal the
perscentage of
times the employee is meeting the standard we have established.
Please
direct me as to where I need to do this so I can have:

Sue Yes
Sue Yes
Sue Yes
Sue Yes
Sue No


Become one line stating:

Sue 80% compliance.

Is this in query or in a report?

Please help... thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
I'm glad you like my little app. You are able to delete all the surveys,
questions, and responses etc and design your own surveys. I don't charge
anything for your company to use At Your Survey. I don't allow a contractor
to sell my application to his/her customers without contacting me first.

If you have any questions about the application, please post them back here
in the news groups.

--
Duane Hookom
MS Access MVP


Sue said:
I am so excited! The first "At Your Survey" is exactly what I need!! It
is
giving me all 15 questions and how many yes and how many no..... it also
give me beautiful graphs next to each question! This is amazing.... I am
so
excited!

Whoa! This is just SO cool!

How do I alter this to use for my purpose or is that not allowed? How do
I
enter my data and delete the data currently in the database??

"Teach me to fish" guys!!! I want to be able to do this!! :)

Sue



Duane Hookom said:
Karl is correct (as usual). If you would like to see a normalized survey
application, download "At Your Survey" found at
http://www.rogersaccesslibrary.com/forum/topic3.html.

--
Duane Hookom
MS Access MVP


KARL DEWEY said:
I have 30 columns that I need to do this
You have a 'Spreadsheet' instead of a relational database.

First to get your data in the proper structure you need a union query.
A
union query can only be done in SQL view. Create a select query, open
in
design view, click on VIEW - SQL View. The new window has the query in
SQL
(Structured Query Language).

It will look something like this --
SELECT Employee, Q1, Q2, Q3, Q4, .......... Q29, Q30
FROM YourTable;

You will need to edit it to look like this --
SELECT Employee, 1 AS [Question], Q1 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 2 AS [Question], Q2 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 3 AS [Question], Q3 AS [Eval]
FROM YourTable
UNION ALL SELECT Employee, 4 AS [Question], Q4 AS [Eval]
FROM YourTable
..........
UNION ALL SELECT Employee, 30 AS [Question], Q30 AS [Eval]
FROM YourTable;

When the qryMyUnion is run the data will look like this --
Employee Question Eval
Jim 1 -1
Jim 2 0
Jim 3 -1
Jim 4 -1
Bill 1 -1
Bill 2 -1
Bill 3 0
Bill 4 0

Then you can create a query in design view by clicking on the Greek
symbol
that looks like an 'M' on its side ( ∑ ) and laid out like this --
FIELD Employee % Compliance: (Abs(Sum([Eval]))/Count([Eval])*
100)
TABLE qryMyUnion
TOTAL Group By Expression

--
Build a little, test a little.


:

Very sorry for my ignorance, but where would I put this? Does this go
in
the
report or the query? I have been reading for a few hours on how to do
this
and I'm a little confused right now.

I have 30 columns that I need to do this with and 1200 employees. So,
I'm
hoping once I get one column to work, I should be good to go!

Name Q1 Q2 Q3.... etc.
1200 of them

Thank you SO much... :)

I'm an AA, not a programmer... :)

Sue

:

Try this --
SELECT Employee, (Abs(Sum([Eval]))/Count([Eval])* 100) AS [%
Compliance]
FROM YourTable
GROUP BY Employee;

--
Build a little, test a little.


:

We have our data in a table for our evaluation of employees. What
we
need is
group or total the number of Yes and No responses to equal the
perscentage of
times the employee is meeting the standard we have established.
Please
direct me as to where I need to do this so I can have:

Sue Yes
Sue Yes
Sue Yes
Sue Yes
Sue No


Become one line stating:

Sue 80% compliance.

Is this in query or in a report?

Please help... thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top