Report Field Summing

[cfobare79]

I have a report called Data Network that is fed from a query called ABCData which in fed by the table PinnacleTable. I have the query activated from a filter form I created. What I would like to happen is that when I select my Date Range, AngencyID and Report Type that only a sum of the field Quantity is displayed on the report. It is currently working in a way that it only will display the most recently dated records Quantity. I have displayed the query code below. Thank you...

SELECT RateTable.ServiceName, RateTable.Rate, RateTable.Unit, RateTable.ServiceGroup, PinnacleTable.Quantity, PinnacleTable.BillingPeriod, PinnacleTable.[CLASS_OF_SERVICE], AgencyList.AgencyID, AgencyList.AgencyName, AgencyList.Address, AgencyList.ZipCode, ZipCodeT.City, ZipCodeT.State


FROM ZipCodeT INNER JOIN (AgencyList INNER JOIN (RateTable INNER JOIN PinnacleTable ON RateTable.ServiceID=PinnacleTable.[CLASS_OF_SERVICE]) ON AgencyList.AgencyID = PinnacleTable.AgencyID) ON ZipCodeT.ZipCode = AgencyList.ZipCode
WHERE (((PinnacleTable.BillingPeriod)>=Forms!Filter!Comb o0 And (PinnacleTable.BillingPeriod)<=Forms!Filter!Combo2 ) And ((AgencyList.AgencyID)=Forms!Filter!Combo4));

[ranman256]

where (PinnacleTable.BillingPeriod) between Forms!Filter!Combo0 and Forms!Filter!Combo1

Is it NOT pulling days in this date range?

[cfobare79]

The Combo0 and Combo1 are the date ranges. BillingPeriod is the date of the service provided. Currently it is only pulling the data from the most recent BillingPeriod that falls in the requested date range stated from the Combo boxes. What I would like it to do is combine all Quantities that fall in the data range.

Is it something that I would need to do in the Report? I'm not sure if the criteria in the Report would need to be modified....

[June7]

Options:

1. base the report on an aggregate query that returns only the one value

2. base the report on the posted query and do aggregate calcs in report header/footer section, don't put any controls in Detail section and size it to 0 height or set as not visible

[cfobare79]

Thank you very much. The problem I was having was that I kept the BillingPeriod in the query. So all other fields were the same except BillingPeriod and Quantity. By removing BIllingPeriod it allowed the query to merge all the rest of the data. Solved. Solved