Query to calculate percent, but not greater that 100%

T

Tina S

We have employees that our managers are required to review once a quarter.
They cannot do two reviews in one quarter to make up for missing a prior
review. I have created a crosstab query that lists all the employess and the
number of reviews on them by quarter.

I need to calcuate the managers quartly compliance. if they do 0 for the
quarter, it needs to be 0%. If they did 1 it needs to be 100%. If they did
2 or 3, etc, I need the % to still be 100% and not 200% etc.

How do I calcuate the % in a query?
How do I limit it to give me a maximum of 100%?

Here is what the crosstab query looks like:

Manager Name Employee Name Qtr 1 Qtr 2 Qtr 3
Mgr #1 name #1 1 1 1
Mgr #2 name #2 0 1 2
Mgr #3 name #3 1 1 0
Mgr #1 name #4 1 1 1
Mgr #2 name #5 1 2 1
ETC...


thanks.
 
K

KARL DEWEY

Use your table name for [[Change Requests] and the xxx for [Date open] in
this query. Create a table named [CountNumber] with field [CountNUM]
containing number from 0 (zero) through your maximum spread of quarters.
This query goes back 2 years based on the '-2' in two places.

SELECT Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq") AS
Year_QTR, [Change Requests].[Manager Name], [Change Requests].[Employee
Name], IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%") AS Compliance
FROM [Change Requests], CountNumber
GROUP BY Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),
[Change Requests].[Manager Name], [Change Requests].[Employee Name],
IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%")
HAVING
(((Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"))<Format(Date(),"yyyyq")));
 
T

Tina S

Karl,

I did this, not really knowing what I am doing. I made the CountNumber
table and just put 2 columns, Primary Key and CountNum. In CountNUM I put 5
record, 0-4. Our database is just for 2008.

When I run the query, it asks me to "enter parameter value" "Date".
In the query, the Year_QTR column goves me 20063 many times, then 20064 many
times, then 20071 many times, etc.
The complance column returns 0% for all.

I am definitely a novice at Access, so this was a bit over my head, but I
tried to follow what you said.


KARL DEWEY said:
Use your table name for [[Change Requests] and the xxx for [Date open] in
this query. Create a table named [CountNumber] with field [CountNUM]
containing number from 0 (zero) through your maximum spread of quarters.
This query goes back 2 years based on the '-2' in two places.

SELECT Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq") AS
Year_QTR, [Change Requests].[Manager Name], [Change Requests].[Employee
Name], IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%") AS Compliance
FROM [Change Requests], CountNumber
GROUP BY Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),
[Change Requests].[Manager Name], [Change Requests].[Employee Name],
IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%")
HAVING
(((Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"))<Format(Date(),"yyyyq")));

--
KARL DEWEY
Build a little - Test a little


Tina S said:
We have employees that our managers are required to review once a quarter.
They cannot do two reviews in one quarter to make up for missing a prior
review. I have created a crosstab query that lists all the employess and the
number of reviews on them by quarter.

I need to calcuate the managers quartly compliance. if they do 0 for the
quarter, it needs to be 0%. If they did 1 it needs to be 100%. If they did
2 or 3, etc, I need the % to still be 100% and not 200% etc.

How do I calcuate the % in a query?
How do I limit it to give me a maximum of 100%?

Here is what the crosstab query looks like:

Manager Name Employee Name Qtr 1 Qtr 2 Qtr 3
Mgr #1 name #1 1 1 1
Mgr #2 name #2 0 1 2
Mgr #3 name #3 1 1 0
Mgr #1 name #4 1 1 1
Mgr #2 name #5 1 2 1
ETC...


thanks.
Click to expand...
Click to expand...
 
K

KARL DEWEY

it asks me to "enter parameter value" "Date".
Post your SQL.
--
KARL DEWEY
Build a little - Test a little


Tina S said:
Karl,

I did this, not really knowing what I am doing. I made the CountNumber
table and just put 2 columns, Primary Key and CountNum. In CountNUM I put 5
record, 0-4. Our database is just for 2008.

When I run the query, it asks me to "enter parameter value" "Date".
In the query, the Year_QTR column goves me 20063 many times, then 20064 many
times, then 20071 many times, etc.
The complance column returns 0% for all.

I am definitely a novice at Access, so this was a bit over my head, but I
tried to follow what you said.


KARL DEWEY said:
Use your table name for [[Change Requests] and the xxx for [Date open] in
this query. Create a table named [CountNumber] with field [CountNUM]
containing number from 0 (zero) through your maximum spread of quarters.
This query goes back 2 years based on the '-2' in two places.

SELECT Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq") AS
Year_QTR, [Change Requests].[Manager Name], [Change Requests].[Employee
Name], IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%") AS Compliance
FROM [Change Requests], CountNumber
GROUP BY Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),
[Change Requests].[Manager Name], [Change Requests].[Employee Name],
IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%")
HAVING
(((Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"))<Format(Date(),"yyyyq")));

--
KARL DEWEY
Build a little - Test a little


Tina S said:
We have employees that our managers are required to review once a quarter.
They cannot do two reviews in one quarter to make up for missing a prior
review. I have created a crosstab query that lists all the employess and the
number of reviews on them by quarter.

I need to calcuate the managers quartly compliance. if they do 0 for the
quarter, it needs to be 0%. If they did 1 it needs to be 100%. If they did
2 or 3, etc, I need the % to still be 100% and not 200% etc.

How do I calcuate the % in a query?
How do I limit it to give me a maximum of 100%?

Here is what the crosstab query looks like:

Manager Name Employee Name Qtr 1 Qtr 2 Qtr 3
Mgr #1 name #1 1 1 1
Mgr #2 name #2 0 1 2
Mgr #3 name #3 1 1 0
Mgr #1 name #4 1 1 1
Mgr #2 name #5 1 2 1
ETC...


thanks.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

It look like pasting and posting added a hard return - 2 places -
....... DateAdd("yyyy",-2,Date
())),"yyyyq"),"100%","0%")

Delete the return to read ---
...... DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%")
--
KARL DEWEY
Build a little - Test a little


Tina S said:
Karl,

I did this, not really knowing what I am doing. I made the CountNumber
table and just put 2 columns, Primary Key and CountNum. In CountNUM I put 5
record, 0-4. Our database is just for 2008.

When I run the query, it asks me to "enter parameter value" "Date".
In the query, the Year_QTR column goves me 20063 many times, then 20064 many
times, then 20071 many times, etc.
The complance column returns 0% for all.

I am definitely a novice at Access, so this was a bit over my head, but I
tried to follow what you said.


KARL DEWEY said:
Use your table name for [[Change Requests] and the xxx for [Date open] in
this query. Create a table named [CountNumber] with field [CountNUM]
containing number from 0 (zero) through your maximum spread of quarters.
This query goes back 2 years based on the '-2' in two places.

SELECT Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq") AS
Year_QTR, [Change Requests].[Manager Name], [Change Requests].[Employee
Name], IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%") AS Compliance
FROM [Change Requests], CountNumber
GROUP BY Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),
[Change Requests].[Manager Name], [Change Requests].[Employee Name],
IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%")
HAVING
(((Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"))<Format(Date(),"yyyyq")));

--
KARL DEWEY
Build a little - Test a little


Tina S said:
We have employees that our managers are required to review once a quarter.
They cannot do two reviews in one quarter to make up for missing a prior
review. I have created a crosstab query that lists all the employess and the
number of reviews on them by quarter.

I need to calcuate the managers quartly compliance. if they do 0 for the
quarter, it needs to be 0%. If they did 1 it needs to be 100%. If they did
2 or 3, etc, I need the % to still be 100% and not 200% etc.

How do I calcuate the % in a query?
How do I limit it to give me a maximum of 100%?

Here is what the crosstab query looks like:

Manager Name Employee Name Qtr 1 Qtr 2 Qtr 3
Mgr #1 name #1 1 1 1
Mgr #2 name #2 0 1 2
Mgr #3 name #3 1 1 0
Mgr #1 name #4 1 1 1
Mgr #2 name #5 1 2 1
ETC...


thanks.
Click to expand...
Click to expand...
Click to expand...
 
T

Tina S

I'm not following you. It did not paste exactly as it appears. the ends
returned in this format before the return in my SQL view.

KARL DEWEY said:
It look like pasting and posting added a hard return - 2 places -
...... DateAdd("yyyy",-2,Date
())),"yyyyq"),"100%","0%")

Delete the return to read ---
...... DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%")
--
KARL DEWEY
Build a little - Test a little


Tina S said:
Karl,

I did this, not really knowing what I am doing. I made the CountNumber
table and just put 2 columns, Primary Key and CountNum. In CountNUM I put 5
record, 0-4. Our database is just for 2008.

When I run the query, it asks me to "enter parameter value" "Date".
In the query, the Year_QTR column goves me 20063 many times, then 20064 many
times, then 20071 many times, etc.
The complance column returns 0% for all.

I am definitely a novice at Access, so this was a bit over my head, but I
tried to follow what you said.


KARL DEWEY said:
Use your table name for [[Change Requests] and the xxx for [Date open] in
this query. Create a table named [CountNumber] with field [CountNUM]
containing number from 0 (zero) through your maximum spread of quarters.
This query goes back 2 years based on the '-2' in two places.

SELECT Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq") AS
Year_QTR, [Change Requests].[Manager Name], [Change Requests].[Employee
Name], IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%") AS Compliance
FROM [Change Requests], CountNumber
GROUP BY Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),
[Change Requests].[Manager Name], [Change Requests].[Employee Name],
IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%")
HAVING
(((Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"))<Format(Date(),"yyyyq")));

--
KARL DEWEY
Build a little - Test a little


:

We have employees that our managers are required to review once a quarter.
They cannot do two reviews in one quarter to make up for missing a prior
review. I have created a crosstab query that lists all the employess and the
number of reviews on them by quarter.

I need to calcuate the managers quartly compliance. if they do 0 for the
quarter, it needs to be 0%. If they did 1 it needs to be 100%. If they did
2 or 3, etc, I need the % to still be 100% and not 200% etc.

How do I calcuate the % in a query?
How do I limit it to give me a maximum of 100%?

Here is what the crosstab query looks like:

Manager Name Employee Name Qtr 1 Qtr 2 Qtr 3
Mgr #1 name #1 1 1 1
Mgr #2 name #2 0 1 2
Mgr #3 name #3 1 1 0
Mgr #1 name #4 1 1 1
Mgr #2 name #5 1 2 1
ETC...


thanks.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Open query in SQL view, put cursor between the -2, and Date.
....... DateAdd("yyyy",-2,Date
())),"yyyyq"),"100%","0%")
Press space bar once to make it look like this --
....... DateAdd("yyyy",-2, Date
())),"yyyyq"),"100%","0%")
Move cursor to just behind Date and press Delete. It should then look like
this --
....... DateAdd("yyyy",-2, Date())),"yyyyq"),"100%","0%")
OR this --
....... DateAdd("yyyy",-2,
Date())),"yyyyq"),"100%","0%")

Do this in both place where you see ,-2,Date and it should solve the
problem.

--
KARL DEWEY
Build a little - Test a little


Tina S said:
I'm not following you. It did not paste exactly as it appears. the ends
returned in this format before the return in my SQL view.

KARL DEWEY said:
It look like pasting and posting added a hard return - 2 places -
...... DateAdd("yyyy",-2,Date
())),"yyyyq"),"100%","0%")

Delete the return to read ---
...... DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%")
--
KARL DEWEY
Build a little - Test a little


Tina S said:
Karl,

I did this, not really knowing what I am doing. I made the CountNumber
table and just put 2 columns, Primary Key and CountNum. In CountNUM I put 5
record, 0-4. Our database is just for 2008.

When I run the query, it asks me to "enter parameter value" "Date".
In the query, the Year_QTR column goves me 20063 many times, then 20064 many
times, then 20071 many times, etc.
The complance column returns 0% for all.

I am definitely a novice at Access, so this was a bit over my head, but I
tried to follow what you said.


:

Use your table name for [[Change Requests] and the xxx for [Date open] in
this query. Create a table named [CountNumber] with field [CountNUM]
containing number from 0 (zero) through your maximum spread of quarters.
This query goes back 2 years based on the '-2' in two places.

SELECT Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq") AS
Year_QTR, [Change Requests].[Manager Name], [Change Requests].[Employee
Name], IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%") AS Compliance
FROM [Change Requests], CountNumber
GROUP BY Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),
[Change Requests].[Manager Name], [Change Requests].[Employee Name],
IIf(Format([Date
open],"yyyyq")=Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"),"100%","0%")
HAVING
(((Format(DateAdd("q",[CountNUM],DateAdd("yyyy",-2,Date())),"yyyyq"))<Format(Date(),"yyyyq")));

--
KARL DEWEY
Build a little - Test a little


:

We have employees that our managers are required to review once a quarter.
They cannot do two reviews in one quarter to make up for missing a prior
review. I have created a crosstab query that lists all the employess and the
number of reviews on them by quarter.

I need to calcuate the managers quartly compliance. if they do 0 for the
quarter, it needs to be 0%. If they did 1 it needs to be 100%. If they did
2 or 3, etc, I need the % to still be 100% and not 200% etc.

How do I calcuate the % in a query?
How do I limit it to give me a maximum of 100%?

Here is what the crosstab query looks like:

Manager Name Employee Name Qtr 1 Qtr 2 Qtr 3
Mgr #1 name #1 1 1 1
Mgr #2 name #2 0 1 2
Mgr #3 name #3 1 1 0
Mgr #1 name #4 1 1 1
Mgr #2 name #5 1 2 1
ETC...


thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count if Text is Equal in two Fields 2
show records when two fields not equal 3
Doesn't recognize Crosstab gen Fields 5
Count person only 1 time and show person not counted 10
IF statement...I think 19
Crosstab - Calculation 3
How to calculate average for current quarter? 1
Cross Tab Query...Help 4

Top