Aggregation of several columns in one query

[isetea]

Hi,
I'm working on a access database to automate our Staff procedures a bit. One part is to select several informations from a table and to aggregate some of the outputs.

For example:
50 people are in the department
45 are current staff (15 business adviser, 15 financial planner and 15 sales officer)
3 are on extended leave (1 business adviser and 2 financial planner)
1 is resigned (1 sales officer)
and 1 moved to another department (1 sales officer)

I need the aggregation of all current, extended leave and resigned (contains resigned and moved) staff per job title.

I created a query with the title column and than created 4 new columns with the sum of all current, extended leave, resigned and moved staff. But I can't manage to get the aggregation for the resigned and moved staff.

The below example is only a shorted version. Generally I have 5-7 status to be aggregated into one.

Does anybody has an idea what I can do?

If I work with excel, I would probably do a horizontal aggregation. Easy...but in access I fail.

Thanks for your help.

[MMcCarthy]

There is no example attached. The only way we can help you is if you post the full query code.

Hi,
I'm working on a access database to automate our Staff procedures a bit. One part is to select several informations from a table and to aggregate some of the outputs.

For example:
50 people are in the department
45 are current staff (15 business adviser, 15 financial planner and 15 sales officer)
3 are on extended leave (1 business adviser and 2 financial planner)
1 is resigned (1 sales officer)
and 1 moved to another department (1 sales officer)

I need the aggregation of all current, extended leave and resigned (contains resigned and moved) staff per job title.

I created a query with the title column and than created 4 new columns with the sum of all current, extended leave, resigned and moved staff. But I can't manage to get the aggregation for the resigned and moved staff.

The below example is only a shorted version. Generally I have 5-7 status to be aggregated into one.

Does anybody has an idea what I can do?

If I work with excel, I would probably do a horizontal aggregation. Easy...but in access I fail.

Thanks for your help.

[PEB]

Hi,

You can do a Crosstab query to perform your task...

So You select Crosstab from Query menu

You select the field JobTitle and you assign it AS RowData

You Select the field that shows the status /moved, resigned, and other/
And you assign it as Column DATA

Copy an other time this field
under it assign that is value and that it have to run the count function instaed of grouped by


When you open your crosstab you will see your staff countet by job title and status..

[isetea]

Hi! Sorry here is the sql. The bold sentence is a count I tried, but it was not working. Thanks.

SELECT
Title, Sum(Resigned) AS SumOfResigned, Sum(Terminated) AS SumOfTerminated, Sum([Retired Ill Health]) AS [SumOfRetired Ill Health], Sum(Redundant) AS SumOfRedundant, Sum(Moved) AS SumOfMoved,

Sum([Resigned]+[Terminated]+[Retired Ill Health]+[Redundant]+[Moved]) AS Attrition

FROM
qry_Union_All_Status_for_Distribution_Report

GROUP BY
Title;

[MMcCarthy]

Try this:

SELECT
Title, Sum(Resigned) AS SumOfResigned, Sum(Terminated) AS SumOfTerminated, Sum([Retired Ill Health]) AS [SumOfRetired Ill Health], Sum(Redundant) AS SumOfRedundant, Sum(Moved) AS SumOfMoved,
Sum (SumOfResigned + SumOfTerminated + [SumOfRetired Ill Health] + SumOfRedundant + SumOfMoved) As Attrition FROM
qry_Union_All_Status_for_Distribution_Report GROUP BY Title;


If that doesn't work try following query with a second query.

SELECT Title, Sum (SumOfResigned + SumOfTerminated + [SumOfRetired Ill Health] + SumOfRedundant + SumOfMoved) As Attrition From Query1 Group By Title;

[isetea]

Thanks. I used two queries and now it's working...
:)