Forum Home
Press F1
Thread ID: 27294	2002-11-19 03:08:00	Calculated Field in Access Report	parry (27)	Press F1

Post ID	Timestamp	Content	User
99596	2002-11-19 03:08:00	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	parry (27)
99597	2002-11-19 03:17:00	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.	crozier (2004)
99598	2002-11-19 03:32:00	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 ( . w3schools . com/sql/default . asp" target="_blank">www . w3schools . com) .	antmannz (28)
99599	2002-11-19 03:42:00	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.	parry (27)
99600	2002-11-19 03:46:00	I think this is what you'll be wanting: SELECT Fruit, COUNT(Fruit), SUM(Amount) FROM tablename GROUP BY Fruit	antmannz (28)
99601	2002-11-19 03:48:00	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.	parry (27)
99602	2002-11-19 03:59:00	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.	antmannz (28)
1