Welcome to SPN

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

Sign Up Now!

New field using IIF statements

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

  1. scubadiver

    scubadiver
    Expand Collapse
    Guest

    In my employee database I have weekly records for each employee in one or
    more of the following departments:

    Data Quality
    Finance
    Admin
    ALS
    Customer Satisfaction
    Enquiry Response
    Process
    Business Support

    I need to create a new field so that

    1) I lose Data Quality and Finance (maybe combine them into a new category
    called "NA" (Not Applicable).

    2) Admin, ALS, Customer Satisfaction and Enquiry Response remain the same

    3) Process and Business Support combine to create a new "other" category.

    Thanks in advance.
     
  2. Loading...


  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Scuba,

    Do you already have a Departments table where these departments are
    listed? If not, make one. If so, add another field to this table,
    let's call it ReportingCategory. So, in this field you simply repeat
    the name of the department in the case of Admin, ALS, Customer
    Satisfaction and Enquiry Response. But for both Data Quality and
    Finance you enter NA, and for Process and Business Support you enter
    "Other". Ok, now you can include this table into your query whenver
    needed, joined to the Department field in the Employees table (or
    whatever it is), and use this new field to Group By in order to get the
    results you want.

    --
    Steve Schapel, Microsoft Access MVP

    scubadiver wrote:
    > In my employee database I have weekly records for each employee in one or
    > more of the following departments:
    >
    > Data Quality
    > Finance
    > Admin
    > ALS
    > Customer Satisfaction
    > Enquiry Response
    > Process
    > Business Support
    >
    > I need to create a new field so that
    >
    > 1) I lose Data Quality and Finance (maybe combine them into a new category
    > called "NA" (Not Applicable).
    >
    > 2) Admin, ALS, Customer Satisfaction and Enquiry Response remain the same
    >
    > 3) Process and Business Support combine to create a new "other" category.
    >
    > Thanks in advance.
     
  4. scubadiver

    scubadiver
    Expand Collapse
    Guest

    Thats what I was thinking of doing but an employee can work for two
    different departments in one week so the information is already in the
    subform to be used.

    Nothing seems to be out of bounds with what can be done with Access so it
    must be possible!


    "Steve Schapel" wrote:

    > Scuba,
    >
    > Do you already have a Departments table where these departments are
    > listed? If not, make one. If so, add another field to this table,
    > let's call it ReportingCategory. So, in this field you simply repeat
    > the name of the department in the case of Admin, ALS, Customer
    > Satisfaction and Enquiry Response. But for both Data Quality and
    > Finance you enter NA, and for Process and Business Support you enter
    > "Other". Ok, now you can include this table into your query whenver
    > needed, joined to the Department field in the Employees table (or
    > whatever it is), and use this new field to Group By in order to get the
    > results you want.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > scubadiver wrote:
    > > In my employee database I have weekly records for each employee in one or
    > > more of the following departments:
    > >
    > > Data Quality
    > > Finance
    > > Admin
    > > ALS
    > > Customer Satisfaction
    > > Enquiry Response
    > > Process
    > > Business Support
    > >
    > > I need to create a new field so that
    > >
    > > 1) I lose Data Quality and Finance (maybe combine them into a new category
    > > called "NA" (Not Applicable).
    > >
    > > 2) Admin, ALS, Customer Satisfaction and Enquiry Response remain the same
    > >
    > > 3) Process and Business Support combine to create a new "other" category.
    > >
    > > Thanks in advance.

    >
     
  5. scubadiver

    scubadiver
    Expand Collapse
    Guest

    I managed to create a series of IIF statements in a query and concatenate the
    columns.

    "Steve Schapel" wrote:

    > Scuba,
    >
    > Do you already have a Departments table where these departments are
    > listed? If not, make one. If so, add another field to this table,
    > let's call it ReportingCategory. So, in this field you simply repeat
    > the name of the department in the case of Admin, ALS, Customer
    > Satisfaction and Enquiry Response. But for both Data Quality and
    > Finance you enter NA, and for Process and Business Support you enter
    > "Other". Ok, now you can include this table into your query whenver
    > needed, joined to the Department field in the Employees table (or
    > whatever it is), and use this new field to Group By in order to get the
    > results you want.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > scubadiver wrote:
    > > In my employee database I have weekly records for each employee in one or
    > > more of the following departments:
    > >
    > > Data Quality
    > > Finance
    > > Admin
    > > ALS
    > > Customer Satisfaction
    > > Enquiry Response
    > > Process
    > > Business Support
    > >
    > > I need to create a new field so that
    > >
    > > 1) I lose Data Quality and Finance (maybe combine them into a new category
    > > called "NA" (Not Applicable).
    > >
    > > 2) Admin, ALS, Customer Satisfaction and Enquiry Response remain the same
    > >
    > > 3) Process and Business Support combine to create a new "other" category.
    > >
    > > Thanks in advance.

    >
     
  6. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Scuba,

    Based on the infromation you have provided so far, I really can't see
    that an employee working for 2 different departments will would make any
    difference to the idea I suggested.

    --
    Steve Schapel, Microsoft Access MVP

    scubadiver wrote:
    > Thats what I was thinking of doing but an employee can work for two
    > different departments in one week so the information is already in the
    > subform to be used.
    >
    > Nothing seems to be out of bounds with what can be done with Access so it
    > must be possible!
    >
     
  7. scubadiver

    scubadiver
    Expand Collapse
    Guest

    *cough! cough!* Sorry.

    The working hours info is in a subform separate to the employee info.

    "Steve Schapel" wrote:

    > Scuba,
    >
    > Based on the infromation you have provided so far, I really can't see
    > that an employee working for 2 different departments will would make any
    > difference to the idea I suggested.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > scubadiver wrote:
    > > Thats what I was thinking of doing but an employee can work for two
    > > different departments in one week so the information is already in the
    > > subform to be used.
    > >
    > > Nothing seems to be out of bounds with what can be done with Access so it
    > > must be possible!
    > >

    >
     
  8. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Scuba,

    It will probably help you if you can understand that info (data) is not
    in forms or subforms. Forms and subforms can provide a handy window of
    access to the data, in one way or another, but the data itself is
    ultimately in tables, or defined from the table data in queries. So,
    queries are often the best approach to manipulating your data. In this
    case, adding a reference table to the existing query would be the
    easiest way to get the result you seek.

    --
    Steve Schapel, Microsoft Access MVP

    scubadiver wrote:
    > *cough! cough!* Sorry.
    >
    > The working hours info is in a subform separate to the employee info.
    >
     

Share This Page