newbie Q: calcing %'s

[red6000]

My table of data is called QA
I have 3 columns, 1 called CSRName and 1 called Correct and 1 for a date

What I would like is a query the % of 'Yes' that appear in the 'Correct'
column per name that appears in the CSRName column.

I can do a query to tell me how many 'yes' and also a query for the 'No',
but how do I do a calculation in the query. My query for 'Yes' is:

SELECT QAs.CSRName, Count(QAs.[Correct?]) AS Number_Correct
FROM QAs
WHERE (((QAs.[Correct?])="Yes") AND ((Month([Date case worked by CSR]))=7))
GROUP BY QAs.CSRName;


Many thanks

 

[Steve Schapel]

Red

Try it like this...
SELECT QAs.CSRName, -Sum([Correct?]="yes")/Count([Correct?]) AS
ProportionCorrect
FROM QAs
WHERE Month([Date case worked by CSR])=7
GROUP BY QAs.CSRName

Since you would not normally directly see the query results, which would
normally be for the purpose of viewing on a form or report, I would
prefer to just calculate the proportion in the query, and then use the
Format property of the control on the form or report to display as a %

However, you could do...
SELECT QAs.CSRName,
FormatPercent(-Sum([Correct?]="yes")/Count([Correct?])) AS PercentCorrect
FROM QAs
WHERE Month([Date case worked by CSR])=7
GROUP BY QAs.CSRName

By the way, as an aside, it is not a good idea to use a ? as part of the
name of a field.

 

[red6000]

fantasic.

I'm slowly being able to understand how this all works. Can I ask for help
with another query I'm trying to create?

My current SQL is:

SELECT QAs.CSRName, (-Sum([pres code 1]="P01"))+(-Sum([pres code
2]="P01"))+(-Sum([pres code 3]="P01"))+(-Sum([pres code
4]="P01"))+(-Sum([pres code 5]="P01")) AS P01
FROM QAs
WHERE (((Month([Date case worked by CSR]))=6))
GROUP BY QAs.CSRName;

The problem I have is that whilst the code works perfectly if there is data
in all 5 columns (Pres code 1 to Pres code 5), if any of the cells are blank
(which they are) then it returns a blank in the result. How do I get around
this?

Many thanks, hopefully with the help that I'm getting from this forum I'll
be happily creating my database quite quickly.

Red

Try it like this...
SELECT QAs.CSRName, -Sum([Correct?]="yes")/Count([Correct?]) AS
ProportionCorrect
FROM QAs
WHERE Month([Date case worked by CSR])=7
GROUP BY QAs.CSRName

Since you would not normally directly see the query results, which would
normally be for the purpose of viewing on a form or report, I would prefer
to just calculate the proportion in the query, and then use the Format
property of the control on the form or report to display as a %

However, you could do...
SELECT QAs.CSRName,
FormatPercent(-Sum([Correct?]="yes")/Count([Correct?])) AS PercentCorrect
FROM QAs
WHERE Month([Date case worked by CSR])=7
GROUP BY QAs.CSRName

By the way, as an aside, it is not a good idea to use a ? as part of the
name of a field.

--
Steve Schapel, Microsoft Access MVP

My table of data is called QA
I have 3 columns, 1 called CSRName and 1 called Correct and 1 for a date

What I would like is a query the % of 'Yes' that appear in the 'Correct'
column per name that appears in the CSRName column.

I can do a query to tell me how many 'yes' and also a query for the 'No',
but how do I do a calculation in the query. My query for 'Yes' is:

SELECT QAs.CSRName, Count(QAs.[Correct?]) AS Number_Correct
FROM QAs
WHERE (((QAs.[Correct?])="Yes") AND ((Month([Date case worked by
CSR]))=7))
GROUP BY QAs.CSRName;


Many thanks

 

[Steve Schapel]

Red,

You can use the Nz function to have the Nulls evaluated. Like this...

SELECT QAs.CSRName, (-Sum(Nz([pres code 1],"")="P01"))+(-Sum(Nz([pres
code 2],"")="P01"))+(-Sum(Nz([pres code 3],"")="P01"))+(-Sum(Nz([pres
code 4],"")="P01"))+(-Sum(Nz([pres code 5],"")="P01")) AS P01

Or, more simply...
SELECT QAs.CSRName, -Sum((Nz([pres code 1],"")="P01")+(Nz([pres code
2],"")="P01")+(Nz([pres code 3],"")="P01")+Nz([pres code
4],"")="P01")+(Nz([pres code 5],"")="P01")) AS P01

Having said that, I should also say that having these fields [pres code
1], [pres code 2], etc, almost certainly indicates a fairly serious flaw
in your database design, which is sure to bring you many headaches.
Looks like the "fields as data" trap. If you would like to review this
aspect, please post back with further details of your table design and
the meaning of these fields.

 

[red6000]

Thanks Steve,

I am trying to move from an Excel spreadsheet that isn't very manageable to
a database.

The data I am capturing is checking the quality of people work so I am
recording:

Name
Date case done
task being performed
Who's checked it
date they checked it
Whether the case was right or wrong
And the errors.... now for the errors I have some errorcodes (about 100) as
people will only make a few mistakes it was easier to have 5 fields to
record up to 5 errors instead of having 100 different yes/no fields.

I then wnat to be able to pull of various data from it which includes the
different types of errors being made.

I'm sure a database is the way forward as the spreadsheet has become
unmanageablt due to the siz and number of staff constantly joining and
leaving.

any advice is greatly appreciated.

thanks

Red,

You can use the Nz function to have the Nulls evaluated. Like this...

SELECT QAs.CSRName, (-Sum(Nz([pres code 1],"")="P01"))+(-Sum(Nz([pres code
2],"")="P01"))+(-Sum(Nz([pres code 3],"")="P01"))+(-Sum(Nz([pres code
4],"")="P01"))+(-Sum(Nz([pres code 5],"")="P01")) AS P01

Or, more simply...
SELECT QAs.CSRName, -Sum((Nz([pres code 1],"")="P01")+(Nz([pres code
2],"")="P01")+(Nz([pres code 3],"")="P01")+Nz([pres code
4],"")="P01")+(Nz([pres code 5],"")="P01")) AS P01

Having said that, I should also say that having these fields [pres code
1], [pres code 2], etc, almost certainly indicates a fairly serious flaw
in your database design, which is sure to bring you many headaches. Looks
like the "fields as data" trap. If you would like to review this aspect,
please post back with further details of your table design and the meaning
of these fields.

--
Steve Schapel, Microsoft Access MVP

fantasic.

I'm slowly being able to understand how this all works. Can I ask for
help with another query I'm trying to create?

My current SQL is:

SELECT QAs.CSRName, (-Sum([pres code 1]="P01"))+(-Sum([pres code
2]="P01"))+(-Sum([pres code 3]="P01"))+(-Sum([pres code
4]="P01"))+(-Sum([pres code 5]="P01")) AS P01
FROM QAs
WHERE (((Month([Date case worked by CSR]))=6))
GROUP BY QAs.CSRName;

The problem I have is that whilst the code works perfectly if there is
data in all 5 columns (Pres code 1 to Pres code 5), if any of the cells
are blank (which they are) then it returns a blank in the result. How do
I get around this?

Many thanks, hopefully with the help that I'm getting from this forum
I'll be happily creating my database quite quickly.

 

[Steve Schapel]

Red,

I just *knew* this was coming from a spreadsheet, from your earlier use
of the word "cells".

Here's a take on the database approach... You have a one-to-many
relationship between "Task" and "Errors". That means the Errors must be
in a separate table. You need 3 fields:
ErrorID
PerformanceID (foreign key field to relate to primary key of your
exisitng table)
ErrorCode
.... or whatever you name them.

In other words, all errors end up in one field, and they are identified
as to which of the case/task/assessment records each error record is
related to. Each case/task/assessment record can have any number of
errors associated with it.

You can easily move the existing error data into the new table via a
series of 5 simple Append Queries. Following which, remove the [pres
code x] fields from your existing table .

After that, all your summary statistics and calculations etc. will be
*much* easier.

As regards the user interface for managint this data, you will probably
want to use a continuous view subform, based on the errors table, placed
on the task form.

Let us know if you need more explicit help with any of this.

 

[red6000]

Thanks Steve,

I understand the principle behind what you're saying, but I'll probably
struggle a bit with it as this is the first time I've used access.

I've got the Access 2003 Inside Out book by John L Viecas which I've been
trying to use. I'm slowly getting there.

Am I right in thinking this is better suited to access over excel? I'm
hoping that once I've gone thru the pain of setting up the database, then
the maintainence will much easier than the spreadsheet.

Cheers mate.

Steve (red6000)

Red,

I just *knew* this was coming from a spreadsheet, from your earlier use of
the word "cells".

Here's a take on the database approach... You have a one-to-many
relationship between "Task" and "Errors". That means the Errors must be
in a separate table. You need 3 fields:
ErrorID
PerformanceID (foreign key field to relate to primary key of your
exisitng table)
ErrorCode
... or whatever you name them.

In other words, all errors end up in one field, and they are identified as
to which of the case/task/assessment records each error record is related
to. Each case/task/assessment record can have any number of errors
associated with it.

You can easily move the existing error data into the new table via a
series of 5 simple Append Queries. Following which, remove the [pres code
x] fields from your existing table .

After that, all your summary statistics and calculations etc. will be
*much* easier.

As regards the user interface for managint this data, you will probably
want to use a continuous view subform, based on the errors table, placed
on the task form.

Let us know if you need more explicit help with any of this.

--
Steve Schapel, Microsoft Access MVP

Thanks Steve,

I am trying to move from an Excel spreadsheet that isn't very manageable
to a database.

The data I am capturing is checking the quality of people work so I am
recording:

Name
Date case done
task being performed
Who's checked it
date they checked it
Whether the case was right or wrong
And the errors.... now for the errors I have some errorcodes (about 100)
as people will only make a few mistakes it was easier to have 5 fields to
record up to 5 errors instead of having 100 different yes/no fields.

I then wnat to be able to pull of various data from it which includes the
different types of errors being made.

I'm sure a database is the way forward as the spreadsheet has become
unmanageablt due to the siz and number of staff constantly joining and
leaving.

 

[Steve Schapel]

Steve,

Yes, this is a database project, not a spreadsheet project. I am sure
you will be better off in the long run. Yes, that is an excellent book.
Yes, it is painful - most people find competency with relational
database work quite a steep learning curve, requiring a significant
investment of time and energy and paracetomol. In my experience, most
people who make that investment end up feeling it was worth it. Please
continue to use these newsgroups as a resource.