help with summarising a field

  • Thread starter Thread starter lynn
  • Start date Start date
L

lynn

I need to create a report which has the total number of
absence days for each employee. I have a calculated field
in the underlying query which calculates the duration of
absence from dates. I then want to add these durations
together to give me a total in the last 12 months.
I have tried creating another calculation field in the
query which totals the duration field but when I try to
run the query, I get the message

you tried to execute a query that does not include the
specified expression 'surname' as part of an aggregate
function.

Where do I put 'surname'? Where is it getting the
requirement for 'surname'?

I know I can use the grouping feature to produce a summary
field, but I want this total figure to appear in the body
of the report along with other information.

I am really stuck!
 
select surname, sum(field1) from table1
where wdate between (date1 and date2)
Group by surname
Order by surname

This will give you one record for every surname.

Jones, 12
Morris, 14
Thomas, 10

The issue arises of having to give up data in the query
because you want the query to sum. You have to either sum
or group by <b>all<b> fields in a query if you want to sum
just one field. Think of it logically. You are creating a
summary view not a detail view. Generally you can't have
it both ways. I don't know if it will work but try this.
Create some interim queries that store the detail you
want, maybe linking tables. Then use those views to create
another view or generate your report from them. I've used
this technique to get around some problems like yours
before.
 
Back
Top