New field using IIF statements

[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.

 

[Steve Schapel]

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.

 

[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!

 

[Guest]

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

 

[Steve Schapel]

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.

 

[Guest]

*cough! cough!* Sorry.

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

 

[Steve Schapel]

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.