Abs Function

A

Allan H

I'm using the Abs Function in a report to calculate the total occurence of
specific incidents per month over a calender year....=Sum(Abs([MyField
Name]=-1 And [Mth]="7")).
All works well but....the report returns the same number of pages as there
are records in my query.
What the!!!
Any suggestion...
 
J

John W. Vinson

I'm using the Abs Function in a report to calculate the total occurence of
specific incidents per month over a calender year....=Sum(Abs([MyField
Name]=-1 And [Mth]="7")).
All works well but....the report returns the same number of pages as there
are records in my query.
What the!!!
Any suggestion...

The Sum expression has nothing whatsoever to do with the number of pages in
the report.

Please post the Recordsource property of the report (the SQL of it if it's a
query). My guess is that your report is... showing one page for each record.
Do you have any sorting/grouping defined on the report?
 
A

Allan H

Hi John,

Correct...my report is showing one page for each record.
Report 'Record Scource' = IncidentReportQuery
SELECT IncidentReport.IRID, IncidentReport.Date, Format([Date],"m") AS Mth,
IncidentReport.[Slip/Trip - food stuff on floor], IncidentReport.[Slip/Trip -
beverage on floor], IncidentReport.[Slip/Trip - liquid (other) on floor],
IncidentReport.[Slip/Trip - rainwater on floor], IncidentReport.[Slip/Trip -
other foreign object on floor], IncidentReport.[Slip/Trip - uneven floor],
IncidentReport.[Slip/Trip - no apparent reason], IncidentReport.[Slip/Trip -
wet weather mat], IncidentReport.[Slip/Trip - other],
IncidentReport.[Illness/Fainting], IncidentReport.Theft,
IncidentReport.Vehicles, IncidentReport.[Low height fall (less than 2m
high)], IncidentReport.[High height fall (2m high or higher)],
IncidentReport.[Caught in - door], IncidentReport.[Caught in - machinery],
IncidentReport.[Caught in - lift], IncidentReport.[Injured on - escalator],
IncidentReport.[Injured on - moving walkway], IncidentReport.[Property/Stock
damage - tenant], IncidentReport.[Property damage - other],
IncidentReport.[Walked into door/window/object], IncidentReport.[Serious
Criminal Activity], IncidentReport.[Struck by object], IncidentReport.Other,
IncidentReport.[Incident Description]
FROM IncidentReport;

Have tried Grouping...no luck.



John W. Vinson said:
I'm using the Abs Function in a report to calculate the total occurence of
specific incidents per month over a calender year....=Sum(Abs([MyField
Name]=-1 And [Mth]="7")).
All works well but....the report returns the same number of pages as there
are records in my query.
What the!!!
Any suggestion...

The Sum expression has nothing whatsoever to do with the number of pages in
the report.

Please post the Recordsource property of the report (the SQL of it if it's a
query). My guess is that your report is... showing one page for each record.
Do you have any sorting/grouping defined on the report?
 
J

John W. Vinson

Hi John,

Correct...my report is showing one page for each record.
Report 'Record Scource' = IncidentReportQuery
SELECT IncidentReport.IRID, IncidentReport.Date, Format([Date],"m") AS Mth,
IncidentReport.[Slip/Trip - food stuff on floor], IncidentReport.[Slip/Trip -
beverage on floor], IncidentReport.[Slip/Trip - liquid (other) on floor],
IncidentReport.[Slip/Trip - rainwater on floor], IncidentReport.[Slip/Trip -
other foreign object on floor], IncidentReport.[Slip/Trip - uneven floor],
IncidentReport.[Slip/Trip - no apparent reason], IncidentReport.[Slip/Trip -
wet weather mat], IncidentReport.[Slip/Trip - other],
IncidentReport.[Illness/Fainting], IncidentReport.Theft,
IncidentReport.Vehicles, IncidentReport.[Low height fall (less than 2m
high)], IncidentReport.[High height fall (2m high or higher)],
IncidentReport.[Caught in - door], IncidentReport.[Caught in - machinery],
IncidentReport.[Caught in - lift], IncidentReport.[Injured on - escalator],
IncidentReport.[Injured on - moving walkway], IncidentReport.[Property/Stock
damage - tenant], IncidentReport.[Property damage - other],
IncidentReport.[Walked into door/window/object], IncidentReport.[Serious
Criminal Activity], IncidentReport.[Struck by object], IncidentReport.Other,
IncidentReport.[Incident Description]
FROM IncidentReport;

Have tried Grouping...no luck.

A couple of things. This query does indeed return one record per incident, and
does not involve your Abs() function in any way; it's doing exactly what I
would expect.

More importantly - unless this is the result of a Crosstab query pulling the
incident types out as fieldnames - the table structure is incorrectly
normalized, making it far more difficult to manage your data. If you add a new
incident type, you'll need to restructure your table, rewrite all your
queries, redesign all your forms, redesign all your reports... OUCH! Can one
incident involve multiple types (e.g. a [Slip/Trip - food stuff on floor]
leading to a [Low height fall]? If so a normalized design would have three
tables: Incidents, IncidentTypes, and a third table with the IRID and the
TypeID.

That said... Do you want to see the details of each incident? or just the
number of occurances of each type? If you JUST want the sums, leave IRID,
[Date] and [Description] out, and make this a totals query, grouping by Mth
and using -Sum([fieldname]). If you do want to see both, use Sorting and
Grouping to group by Mth, put the IRID, [Date] and Description in the detail
section, and put textboxes in the group footer summing the negative of the
yesno fields.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top