1

I am trying to add a few totals (sums and counts) using the wizard to an Access report. When I add them to the report and try to view the report, the report fails. Without the totals the report will display. The only thing I can figure that may be a factor is that the query being run to populate the report takes a bit to execute. The query runs a series of other queries which takes longer than normal to generate the report.

ETA: Thank you dmoody007 for your help in rooting out the problem and confirming my suspicions.

Alex B.
  • 58
  • 7

1 Answers1

0

The question is a little vague. Any of these can either display errors or cause the report to not run.

Use Control Name in detail section of report for totals field (example: Name is Overtime so total of overtime should appear like =Sum([OverTime]))

Be careful not to name your controls the same. Detail control can be named Overtime. Your total of overtime should NOT be named overtime as well. Name it like Tot_OverTime otherwise you could end up with a circular reference.

If counting a text field, know that null fields are an issue. Suggest counting identity seeds or fields you know always have a value.

Make sure each control in detail you plan to sum, is formatted for numbers. If you look at the control property, format should be a number and you can assign decimal places. If not, you need to check your query or source table to ensure field is properly assigned a format.

One good tip. Add one field to total at a time. A little tedious but ensures one works before adding the next. Always recommend this to newbies until they get real comfortable making reports. Easier to debug.

Good Luck.

dmoody007
  • 63
  • 9
  • Sorry for the vague question. When I create the totals text boxes I am creating them from the designer by clicking on the sigma in the grouping and totals section while I have the field I want to total selected. So, I am getting the default code and field name that Access is assigning the totals field I create. The field I am using for testing is formatted to currency both in the query and in the properties of the control. I guess that's why my question is a bit vague, I'm not doing anything that is immediately obvious that it is wrong, but I have to be doing something wrong. – Alex B. Jun 18 '15 at 13:24
  • Ok - Just a couple of quick questions. When you look at the properties of the "total" text box created by the wizard, what is in the control source field? Also, where is the text box placed? Is it in the report footer, page footer, detail, grouping footer? – dmoody007 Jun 18 '15 at 13:37
  • I just got my hands on 2010 and found that the Access wizard works better than I remember. One issue I typically see is people name fields like "Yearly Annual Revenue". The spaces cause issues if not handled correctly. Common practice is to name fields like "Yearly_Annual_Revenue" or "YearlyAnnualRevenue". New Wizards appear to have fixed the issues of old. Please explain more about query. You mentioned taking a long time to load. Also, are you seeing any error when you run the report with totals? Does report appear at all, blank, #'s in fields, etc? – dmoody007 Jun 18 '15 at 14:00
  • the contents of the control source is =Sum([PostageTotal]). PostageTotal is the field I need to sum and the text box is automatically placed in the report footer. I don't get any error messages or codes when attempting to run the report, it acts like it is going to run but then stops. It doesn't freeze Access, its more like it cancels out of the loading. – Alex B. Jun 18 '15 at 17:48
  • As for the query, it isn't something I designed, but as far as I can tell it is a query that selects from other saved queries, so running this query causes 5+ other queries to run. I haven't drilled down through all the queries yet to figure out how many are running. I'm working on reducing the number that need to run to feed the correct data into the report. It is someone else's queries and reports in an open source program so I don't know quite how all of it works yet. – Alex B. Jun 18 '15 at 18:00
  • A rough mapping of the query shows 7 other queries being ran all of them contain joins and one that might be running twice contains a union. That is probably why it is slow. – Alex B. Jun 18 '15 at 18:37
  • Agree - 7 simultaneous queries is heavy. As far as debugging other's work, we've all been there. Not fun. The report fields and calcs appear correct so I don't think it is the issue. It could be the additional load of totaling with the 7 queries. – dmoody007 Jun 18 '15 at 19:44
  • Have suggestion: Copy the DB to desktop or something and use the copy so you don't ruin your original. Take the final query in the chain and change to a MakeTable query and have it create a temp table. Change query back to a select query. Go into the report properties and change the source from the select query to the temp table. Try adding the total fields again and run. This way you are taking out the process load of the 7 queries. If it works, you know where the issue is. Otherwise, back to the drawing board. – dmoody007 Jun 18 '15 at 19:45
  • Yep, that works. It is definitely the multiple queries + the totals that causes it to bomb while loading the report. If you could repost that suggestion as an answer I'll mark it as correct so you get credit for it. I'll edit my original question to clarify for others that may come across this issue. Thank you for your help! @dmoody007 – Alex B. Jun 18 '15 at 20:58