Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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.
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page