# Thread: Grouping for totals (2003)

1. ## Grouping for totals (2003)

I have a table with data in it that uses a number to identify an employee's work group.

I need to get some averages of the data in the table grouped by work group, but I need to combine 2 of the work groups into one resulting average.

For example, I have 30 employees in work group 1, 50 in work group 2, and 10 in work group 3.

I need an average for the 30 and an average for the 60 (50 + 10).

Can this be done in one query?

2. ## Re: Grouping for totals (2003)

Let's say that the relevant field is named WorkgroupID, and that you want to "merge" WorkgroupIDs 25 and 37, and treat all others separately.
Create a query based on the table, and add the following calculated field:

WGID: IIf([WorkgroupID]=37,25,[WorkgroupID])

Add the fields you want to calculate averages for.
Select View | Totals.
Set the Total option for all fields except WGID to Avg.

The average for WorkgroupIDs 25 and 37 combined will be listed under 25.

3. ## Re: Grouping for totals (2003)

Perfecto, thank you!

Someday I'll understand the IIF function.

4. ## Re: Grouping for totals (2003)

IIf([WorkgroupID]=37,25,[WorkgroupID]) says: if the value of WorkgroupID is 37, return 25, and in all other cases, return the value of WorkgroupID itself. So it works like this:

<table border=1><td>WorkgroupID</td><td>WGID</td><td align=right>21</td><td align=right>21</td><td align=right>25</td><td align=right>25</td><td align=right>30</td><td align=right>30</td><td align=right>37</td><td align=right>25</td><td align=right>44</td><td align=right>44</td></table>
The values 37 and 25 are grouped together in WGID.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•