PDA

View Full Version : Calculated Field in Access Report



parry
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.

cheers
Parry

crozier
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.

antmannz
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).

parry
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.

antmannz
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

parry
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.

antmannz
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.