View Full Version : Calculated Field in Access Report

19-11-2002, 04:08 PM
Hi, this is probably simple but I am having difficulty working it out. I have a report where I want to put summary totals at the end of the report.

Example data
[Fruit] [Amount]
Apples $20
Pears $10
Apples $30

I want to be able to count the number of times apple appears in the report (eg 2) plus I want to be able to sum the amounts for only apples (eg $50).

Could someone please tell me the 2 expressions I would type in an unbound textbox please, where Fruit is the name of the field for Apples,Pears etc and Amount is the amount of the particuar fruit item.


19-11-2002, 04:17 PM
You should be doing this in a query first, grouping the [Fruit] field, then counting / adding or whatever. Then run a report using the query as its source.

19-11-2002, 04:32 PM
To be honest I prefer using SQL to build .... even with Access.

Use SELECT COUNT(*) FROM tablename WHERE Fruit = 'Apples' for counting how many "Apples" records.

Use SELECT SUM(Amount) FROM tablename WHERE Fruit = 'Apples' for summing all "Apples" records.

Check the Access help file for its SQL reference or try www.w3schools.com/sql/default.asp (http://www.w3schools.com/sql/default.asp).

19-11-2002, 04:42 PM
Thanks but I dont think I can put the individual records and the group total within the same query. Ive seen that you can do calculated fields in a report via Help but the only examples are simple things like =[a]+[b] but I want to do sum/count under certain criteria.

19-11-2002, 04:46 PM
I think this is what you'll be wanting: SELECT Fruit, COUNT(Fruit), SUM(Amount) FROM tablename GROUP BY Fruit

19-11-2002, 04:48 PM
Thanks Antmannz, do you know how will I handle this if the underlying query has a parameter for the date? The records shown in the query are a selection of data based upon what the user selectes as a data range, so how will I incorporate this. If I use a select query as shown it will show everything, not just the date range selected.

The query has ...
between [from] and [to]

where [from] & [to] are just parameters rather than fields.

19-11-2002, 04:59 PM
I would imagine that you have a date column, so you should be able to add WHERE datecolumnname BETWEEN begindate AND enddate
Ummm ...... I think it has to added after the tablename but before the GROUP BY command.