Grouping Issue

[cfobare79]

I have a query that takes inputs from a form (AgencyID, ServiceGroup, BillingPeriod). BillingPeriod has a start variable and an end variable selected on the form. I would like sum the Quantity if it meets all other criteria (falls within the date range). The query below is what I have gotten to. It only gives me the output of the first record. Please help.



SELECT AgencyList.AgencyID, AgencyList.Address, AgencyList.ZipCode, ZipCodeT.City, ZipCodeT.State, RateTable.Rate, RateTable.Unit, RateTable.ServiceGroup, PinnacleTable.CLASS_OF_SERVICE, Sum(PinnacleTable.Quantity) AS SumOfQuantity, PinnacleTable.BillingPeriod, AgencyList.AgencyName
FROM ZipCodeT INNER JOIN (ServiceGroupT INNER JOIN (AgencyList INNER JOIN (RateTable INNER JOIN PinnacleTable ON RateTable.ServiceID = PinnacleTable.[CLASS_OF_SERVICE]) ON AgencyList.AgencyID = PinnacleTable.AgencyID) ON ServiceGroupT.ServiceGroup = RateTable.ServiceGroup) ON ZipCodeT.ZipCode = AgencyList.ZipCode
GROUP BY AgencyList.AgencyID, AgencyList.Address, AgencyList.ZipCode, ZipCodeT.City, ZipCodeT.State, RateTable.Rate, RateTable.Unit, RateTable.ServiceGroup, PinnacleTable.CLASS_OF_SERVICE, PinnacleTable.BillingPeriod, AgencyList.AgencyName
HAVING (((AgencyList.AgencyID)=[Forms]![Filter]![Combo4]) AND ((RateTable.ServiceGroup)=[Forms]![Filter]![Combo13]) AND ((PinnacleTable.CLASS_OF_SERVICE)="NYENET") AND ((PinnacleTable.BillingPeriod)>=[Forms]![Filter]![Combo0] And (PinnacleTable.BillingPeriod)<=[Forms]![Filter]![Combo2]));

[June7]

I suspect you get only one record because of the filter criteria in the HAVING clause. The dataset is limited to a single agency and a single class of service.

Consider building a report using Sorting & Grouping features with aggregate calcs in header/footer sections. This allows the display of detail records as well as summary data.

[cfobare79]

The display is not really the issue. I only want a single display. The query isn't summing up the Quantity because BillingPeriod is be set as Group By. Since there may be multiple BillingPeriods that fit the Date Ranges selected. Is there a way to bypass the date somehow?

[ItsMe]

Are you trying to avoid using a report? Often times, there are threads where people have difficulty counting periods.

[June7]

If you don't want summary by billing period then maybe don't include BillingPeriod in the SELECT and GROUP BY clauses if all you want it for is filter criteria.

[orange]

Seems people are guessing at what you are trying to do. Can you tell us exactly in plain English what you are trying to do? As June says if you don't want grouping by billing.., then don't include that in the Group BY.

[cfobare79]

OK, I am now using "Report" the correct way. I do have an issue with getting the Grand Total. In the report there is a Text Box "Text154" which has a formula in the query Totals: IIf(IsNull([Quantity]),0,[Quantity]*[Rate]). I also have a sub form with a text box "Total1" that totals that part. I also have another section that with a text box that totals "Text148". I'm using the formula =[Total1]+[Text154]+[Text148] to calculate all the totals in another text box. Everything is working fine except for the calculation of "Text154". The result from "Text154" is just a single lines worth of Total, I would like that whole reports total. All the text boxes have IIf/IsNull functions built in, so I don't think that's the problem. Help Please.

[June7]

Instead of IIf and IsNull, could use: Nz([Quantity],0)*[Rate]. And might be better to do that calc in query and bind textbox to the constructed field.

You have a subform or a subreport?

I don't understand what 'just a single lines worth of Total' nor 'whole reports total' mean.

Are all these controls on main report or subreport?

[cfobare79]

I have a subreport "Verizon" within the report "WorkPlaceServices". I would rather not have the subreport but whenever I try to add the VerizonTable to the WorkPlaceServices i do not get any query results to include results that I had the moment prior to adding the VerizonTable. The WorkplaceServices report/query run the DaveWelchTable, AgencyTable and RateTable. The comment "just a single lines worth of total" meant that the report was only calculating one of the two totals that the service was showing on the report. The "whole report total" meant I would like a grand total. I would also like to add subtotals.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

Subtotal calc occurs in group header/footer section.

[cfobare79]

I would love to attach the DB but it only zips down to 3.22 MB. Is there any other way I can transfer the DB? Does "group header/footer" mean "page header/footer"?

[June7]

No, group does not mean page.

Can upload to fileshare site and post link to file but if it is large I can't download. Might be able to handle 3.22MB.

Did you make copy and remove most data and run C&R?

[cfobare79]

The db is populated by excel sheets from different data sources and formats. My plan was to have the AgencyList have all information and appended queries would be able to run from the AgencyList to create a standardized AgencyID. The AgencyID would be referenced in the queries to build the reports. There are currently 4 reports working in some manner. There is a 5th report listed but is not complete yet. There is a form called "Filter" that is the user interface to create reports. I would like the reports to show all services provided during the date range selected grouped by date and subtotals and grand totals. Tracking the finance is not important. This is purely a billing system. Any help and suggestions would be great. I have attached the link to my db. https://www.dropbox.com/s/0mrwyterzd...2015B.zip?dl=0

[cfobare79]

I have been able to get all my reports working correctly except for the Work Place Services. There is a subreport Verizon that I would like to get rid of and include the items into the main report. Every time I attempt this the query ABC1 returns no results.

[June7]

I copied the query and remove the dynamic parameters. The query without VerizonTable and with GROUP BY has 713 records, without GROUP BY has 793 records. This tells me there is duplication. Any of these tables have 1-to-many relationship?

Why is ServiceID from RateTable saved as foreign key in several tables?

When I bring in VerizonTable it automatically links to AgencyList and RateTable. This doesn't look good. What should it link to? I removed the link to RateTable and get 68 records because of INNER JOIN (no GROUP BY). Changed to LEFT JOIN and get 819 records (also no GROUP BY) and with GROUP BY shows 733 records.

I do not understand the data structure.