aggregate the rows in table before running report

[Guest]

Hi, I am trying to build report from a table but the problem I have right now
is that the information that belonged to the same customer are stored by
multiple rows, for example, row 1 has wk 1 revenue , row 2 has wk 1 revenue
too but that might be entered at different time for the same customer.
This multiple rows result in enormous amount of records in my report. Is
there a way to aggregate them before running report? Any comments?

 

[Lynn Trapp]

Vera,
You need to do a totals query. In the query grid, select View Totals and
change the Total field in the lower half of the grid to Sum.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Thanks, really appreciate ur comments. My reports now are based on tables
directly, if I were to use query for running totals, does that mean i have to
recreate all my reports from that query?
Is there a way to still keep my reports and run some changes on the existing
reports? thanks.

 

[Van T. Dinh]

No. You don't have to recreate the Reports.

Simply create the Query that aggregate the data first and make sure the
query returns the columns / rows you want. Then change the RecordSource of
the Report from the Table name to the Query name. Since you will be using a
Total Query, the available Field names (from the RecordSource will be
different) and you need to create new TextBox(es) for the Total, you will
have to change the ControlSources for some of the Texboes on the Report.

 

[Guest]

Hi Van, thanks for the feedback.
I understand your point here. However, I am not so sure how to find the
Record Source of the report. Do I select the report first and go to design
view and properties or what? I could only see Control Source for each
particular control. How do I get to the Record Source for that report?

 

[Van T. Dinh]

Yep. Open the Report in Design View. Open the Properties window. Make
sure the Selection ComboBox on the Properties window show "Report" and not a
Control name. Select "Report" if required. Go to the "Data" tab and you
should see the RecordSource Property with the Table name.