Calculating in queries

[cjordan8]

I have a report and the source is a query that sorts competitors by city. In the competitor info there are numbers like distribution, circulation, and revenue. I want to be able to calculate totals for each city that will print at the bottom of the page listing the city and it's competitors. I've tried messing around with the summation function within the query, but I can't figure it out. Help?

 

[Sohaila Taravati]

Have you tried grouping?

Sohaila

 

[cjordan8]

To be honest, I don't really know how to use grouping. Everything I know about access is just from messing around with it, and I've never had occasion to do calculations before now.

 

[ColinEssex]

If you create a report through the wizard, it takes you through the grouping and totals and sub-totals. Try that first. Post back if you need more help

Col

 

[Sohaila Taravati]

Go to the design of your report and find your sorting and grouping button. When the dialog box opens, from the list choose your field and at the bottom of the same dialog box choose group header or footer. Put your fields in the header or footer and look at the results. This is the way I learned about them. You can also let the wizard take over and make your report for you. I hope that helped.

Sohaila

 

[cjordan8]

I think I'm a total idiot, because even with your help I can't figure it out. Is there anyway to create a field that will just be the sum of other fields. FOr example. When I run my query, it prompts me for the city so I just see competitors for one city. I want to be able to total the distribution, circulation and revenue for the competitors from that city without having to do it manually, and have it printed in the page footer section. I tried the grouping with the footer, but really didn't know what I was doing.

 

[simongallop]

Call the textbox in the Detail section that shows revenue txtRev.

Now in the report footer create a new txtbox and in the control source of the textbox put:

=Sum([txtRev])

HTH

 

[cjordan8]

I took your advice HTH, but I keep getting an error message when I go to view the report. In Design view, I named the bound object(not the label) txtRev, then went down to the footer, created a new text box and in the unbound object there typed =Sum([txtRev]). When I go to view it, I see the label, then #ERROR where the sum should be.

 

[Rich]

Which footer?

 

[cjordan8]

page footer

 

[cjordan8]

I am an idiot...just noticed that there was a difference between report and page footer. I'll have more questions soon as the boss decides and then changes his mind about what he wants!