Calculate the difference between a number and summary field

[technoranger]

I am creating a staffing database and need to compare the number of positions from one year to the next by category.

I have 25 buildings with two categories of employee per building and a report that sorts by building (BLD) and job category (JOB).

I have one Summary field that is the Total of FTE (full time equivalence)so if there are five people in BLD BE in JOB D7 and one works half time (.5 FTE) the field (SUMFTE) summarizes to 4.5

That works fine to calculate and report the current number of staff by building (BLD) and job category (JOB).

The problem is I need to calculate the difference between last years and this years staff by building (BLD) and job category (JOB). There was no database for previous years - staffing was summarized on a Word document - so I have numbers to compare and have added a table with four fields, BLD (each of 25 buildings), JOB (the two categories of job), NUM (the number of staff in that job / building), and Count (a Summary field that provides Total of NUM by BLD and JOB and is, of course, equal to NUM) to my database.

The question is how, in a report, to calculate NUM - SUMFTE or Count - SUMFTE

[comment]

Assuming you have a relationship between the two tables, based on matching Building AND Job, your calculation should be something like:

Sum ( History::NUM ) - GetSummary ( sTotalFTE ; Job )

This will work on the Job level.

[technoranger]

Assuming you have a relationship between the two tables, based on matching Building AND Job, your calculation should be something like:

Sum ( History::NUM ) - GetSummary ( sTotalFTE ; Job )

This will work on the Job level.

Something does not work - I tried as written ad the result was equal to ( History::NUM ). I then tried a field named Fred that was a calculation: GetSummary ( sTotalFTE ; Job ). The field did not display in the report although it was in the layout.

It seems that GetSummary ( sTotalFTE ; Job ) is ignored or voided out.

This is probably a stupid question to raise.....in your example you reference the field with a lower case /s/ preceding the field name. Is this simply to denote a Summary field in your explanation? When I used GetSummary in my calculation I just used the Summary field name, e.g. GetSummary ( Count Positions by FTE; JOB )

Thanks

[technoranger]

Assuming you have a relationship between the two tables, based on matching Building AND Job, your calculation should be something like:

Sum ( History::NUM ) - GetSummary ( sTotalFTE ; Job )

This will work on the Job level.

Thanks - this works.....I was sorting the report on a field that calculated JOB (which is an AlphaNumeric reference) into a name, e.g. Teacher, Instructional Assistant.

The break field in GetSummary needs to be the field the sort is based on - once I changed it the calculation works.

Thanks!