Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

newbie Q: calcing %'s

Discussion in 'Information Technology' started by red6000, Jul 28, 2006.

Tags:
  1. red6000

    red6000
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Newbie here, just wanted to introduce myself Interfaith Dialogues Jan 18, 2005

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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

    red6000 wrote:
    > 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
    >
    >
     
  4. red6000

    red6000
    Expand Collapse
    Guest

    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" <schapel@mvps.org.ns> wrote in message
    news:OW9KUq3pGHA.3908@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    > red6000 wrote:
    >> 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
     
  5. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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

    red6000 wrote:
    > 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.
     
  6. red6000

    red6000
    Expand Collapse
    Guest

    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


    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:%23be$1e4pGHA.524@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    > red6000 wrote:
    >> 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.
     
  7. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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

    red6000 wrote:
    > 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.
     
  8. red6000

    red6000
    Expand Collapse
    Guest

    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)


    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:eMgfHI6pGHA.4424@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    > red6000 wrote:
    >> 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.
     
  9. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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.

    --
    Steve Schapel, Microsoft Access MVP

    red6000 wrote:
    > 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.
     

Share This Page