Reporting from several queries

[laina222]

I need to create a report that displays the totals for four separate fields. I need to total each unique instance of the field and I need to total the field in it's entirety. For example, I have a field called "Brand" that indicated which realtor/brand is being used. I have five realtors that can be selected from the form, Coldwell Banker, Century 21, ERA, NRT and Cendant. In the report I need to total each brand, (i.e., how many Coldwell Banker, how many Century 21, etc.) and then I need to total all brands in a grand total. I have to do this for four different fields and I need to do it so the person creating the reports each week can select which dates they want to report on. My question is this, how do I get all of these totals displayed on one report while still asking the user to enter the date range? I also would like to display the date range on the top. To do this so far, I have about twenty or so queries but I'm sure there's a more efficient way around this.

 

[JerryDennison]

To simply get a total of one field grouped by another field use a totals query. Set the Realtor/Brand field to Group By and each of the fields that have values you want to total to Sum. To limit the query to a specific date range that can be changed easily and on demand use parameters in the criteria. Since it's a totals query you should set this field's total property to Where:

Between [Enter Begin Date] And [Enter End Date]

Each time the query is run it will prompt the user to enter a begin and end date. You would then base your report on this query. To get a grand total, use an unbound control on your report's footer with '=Sum([FieldName]) in the controlsource property. Do this for each field you wish to have a grand total for.