# Thread: Formul works in Detail Records but fails in Report Footer

1. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## Unanswered: Formul works in Detail Records but fails in Report Footer

Why would this work in the Detail Records section of a report and NOT in the REPORT FOOTER section?

=([AvgOfClosePrice]-[AvgOfListPrice])/([AvgOfListPrice])

I'm stymied!

Thanks.

Rick

2. Registered User
Join Date
Jun 2007
Location
Maitland NSW,Australia
Posts
401
=([AvgOfClosePrice]-[AvgOfListPrice])/([AvgOfListPrice])
This refers to the current record in the detail.

For your Report Footer you need to SUM the fields.
=(SUM([AvgOfClosePrice])-SUM([AvgOfListPrice]))/SUM([AvgOfListPrice])

3. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## Percent Change Still Fails . . .

=(SUM([AvgOfClosePrice])-SUM([AvgOfListPrice]))/SUM([AvgOfListPrice])

This didn't make a difference I'm sorry to say. It produces -3.69%.

The correct answer should be -29.36% - the difference between \$732,750 and \$517,610, \$215,140. It's far more than -3.69%!

Please see the attached Percent Change.pdf file showing page 10 of 10 of the report.

Thanks.

Rick

4. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## Excel Example . . .

Please see the .jpg Excel example attached.

It's the same in Access 2003 but =(SUM([AvgOfClosePrice])-SUM([AvgOfListPrice]))/SUM([AvgOfListPrice]) is not working.

I've tried so many variations on this but to no avail.

Thanks.

Rick

5. Registered User
Join Date
May 2005
Location
Posts
2,888
Based on your PDF, the answer looks correct. Not sure where you're seeing 517,610, as the avg closing price on your report is 717,563. So,

(717,563-732,732)/732,750

is -2.07%, as displayed on the report. It does seem odd that the overall averages match the last detail entry. I can't match either of the overall averages using the amount totals and number of listings.

6. Registered User
Join Date
Jun 2007
Location
Maitland NSW,Australia
Posts
401
Having read your PDF I found that I gave you the incorrect information it is mathematically incorrect to sum averages and use this for calculations.

I found errors in your Totals in the Report Footer -
How can there be a total of 6033 listing when the actual total is 340, the same for your Listing and Closing amounts?

Using the sample data in your PDF I have attached a database and the report as a jpg. The report totals are correct. In the design mode of the report called rpt_listing have a look at my totals calculations.

7. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## Discovery From Excel DATA

I queried the data then pasted it into Excel and the report footer sums are different from the Excel sums. Averages also.

I need to look closer.

I peeked at your report and don't see how you summed for each city in the detail records.

I hope to look at all this evening.

Thanks.

8. Registered User
Join Date
Jun 2007
Location
Maitland NSW,Australia
Posts
401
Rick

I did not sum for each city, I used your figures for each city in the PDF to produce the report. I assumed your city figures in the report are correct.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•