Placement of formulas in reports

[BYarnn]

In spite of all the Access reports, etc I've done, I still find the topic of where to place a formula to be confusing. I can't find a pattern or commonality to tell me. Do you put the formula for sum or count, etc in the header, footer,or detail or in the group header or group footer you have created? Sometimes they work and sometimes...well you get the idea.
Any quick tips or place I can read???

THANKS!!!!!!!!!!!!!!!

 

[dhookom]

Aggregate functions like Sum(), Avg(), Count(), ... should all work in Report and Group headers and footers. They do not work in Page headers and footers.

You can also use aggregates in the detail section however this is used much less often. You might use it to calculate percent of sales like:
=[Sales]/Sum([Sales])


Duane
Hook'D on Access
MS Access MVP

 

[BYarnn]

As I said I have always found a bit of confusion, but the report I'm doing it kind of made me stop and ask the experts. I have a query which tallys the difference between two dates. In the report it inserts the resulting numbers in a column along side the other data.
I want to create a field/calc. that will show the average of those totals
Date1 Date2 Calculation
10/5/08 - 10/1/08 = 4
10/12/08 - 10/10/08 = 2
average = 3

No matter how I place (header, footer, etc) the formula to aver. the numbers, it doesn't work


Ideas??

 

[dhookom]

The expression =Avg() will always work in the sections that I suggested. It will not average a control name.

If you want help, you need to provide some section information, what you mean "Calculation", and what you mean by "it doesn't work".

Duane
Hook'D on Access
MS Access MVP

 

[BYarnn]

Calculation means the diff. between dates (# days). Doesn't work means it returns an error or incorrect #.
I just used your tips and in this case kept the expressions in the header or footer and it worked.

I think the real key to what I am asking has to do with where you can and cannot place expression (like sum, avg,etc) and if there is a choice is one place better than another? Your tips "Report and Group headers and footers. They do not work in Page headers and footers." is very useful. If those are my "choices" I can stick with using them. There other factors that seem to come into play like the settings in SORTING AND GROUPING as well as PROPERTIES..when do I need to or not need to worry about HEADERS and KEEP TOGETHER and RUNNING SUM

I've used all these things but I don't always feel I am seeing the overall gist/effect of each to repeat it next time

I THANK YOU for you time and help!!

 

[dhookom]

There are quite a few options with the Sorting and Grouping dialog and section properties. This can be confusing and is compounded by Running Sum, Repeat Section, Hide Duplicates, and other features.

One of the keys with the sorting and grouping is keeping in mind the subset of records contained between the group header and footer.

Duane
Hook'D on Access
MS Access MVP