Print Preview and Print to Paper and Print to PDF all changing my Report totals (1 Viewer)

[June7]

Yes, I said I see the difference and also explained in post #17 why the values are different in each view.

As arnelgp already suggested, build another query that further aggregates the data. I will take another look.

The aggregate query does include a memo field - the one showing oriental characters.

The textbox transparency was a side note to clean up the row color irregularity.

 

[jmark@stayintouch.us]

thanks for the reply and the tip on the transparency. Can you help build the proper query? I have tried and failed. I have 12 years of access programming but cant seem to get this one done right. can you help? I can compensate. Thanks! jm

 

[June7]

I tried:

SELECT [Query - Inventory By Category Totals Department].LI_LocationStoreID, [Query - Inventory By Category Totals Department].LI_LocationStoreName, Sum([Query - Inventory By Category Totals Department].CostAverage) AS SumOfCostAverage, Sum([Query - Inventory By Category Totals Department].EVAverage) AS SumOfEVAverage
FROM [Query - Inventory By Category Totals Department]
GROUP BY [Query - Inventory By Category Totals Department].LI_LocationStoreID, [Query - Inventory By Category Totals Department].LI_LocationStoreName;

I copied the report, removed the grouping sections and the calculated textboxes except for the ones in report header and footer, bound textboxes to these fields in Detail section. I replaced expressions in report footer textboxes with Sum() calcs. The data is the same in both report views but very different from the original report output.

 

[jmark@stayintouch.us]

I tried:

SELECT [Query - Inventory By Category Totals Department].LI_LocationStoreID, [Query - Inventory By Category Totals Department].LI_LocationStoreName, Sum([Query - Inventory By Category Totals Department].CostAverage) AS SumOfCostAverage, Sum([Query - Inventory By Category Totals Department].EVAverage) AS SumOfEVAverage
FROM [Query - Inventory By Category Totals Department]
GROUP BY [Query - Inventory By Category Totals Department].LI_LocationStoreID, [Query - Inventory By Category Totals Department].LI_LocationStoreName;

I copied the report, removed the grouping sections and the calculated textboxes except for the ones in report header and footer, bound textboxes to these fields in Detail section. I replaced expressions in report footer textboxes with Sum() calcs. The data is the same in both report views but very different from the original report output.

Can you post your new queries here so I can check them out. They should be showing the same numbers, as the numbers are correct in my original report. Thank you. jm

 

[June7]

I did post the new query I tried. It is only the one. It uses your existing query as source.

 

[jmark@stayintouch.us]

I do see the written query but I'm not familiar with how to download it or create my own from it. would you mind creating the query in the front end file of mine and posting the whole file so i can see your query in the manner I am accustomed to seeing it. I know that is amateurish, but its the only way i have ever used queries. thanks

 

[June7]

Open a new query object in SQLView. Copy/paste or type the SQL statement I provided. Switch to Design View.

 

[jmark@stayintouch.us]

YES! That worked as far as me now seeing your query! thanks for that education! I will play with it a bit and get back to you. thanks again! JM

 

[jmark@stayintouch.us]

you are correct that the numbers are very wrong. I have set up the two original queries to not include zeros (which would skew the averages) and to manually count the totals and the number of no zero records so the averages would be accurate. Do you want to continue to try to work on it or are you finished at this point? The data produced by the new query is the wrong numbers. Thanks! JM

 

[June7]

The query is based on your existing query so the filter criteria is still applied.

Switch your db to Overlapping Windows and then you can open objects side-by-side or one above other to view at same time. I ALWAYS do this.
Set TOTALS row on each query and you will see the raw data sums are same but the calculated fields vary.

I am just not understanding your average calcs so I probably won't be much further help.

That query and the report changes I described would be a conventional approach for outputting aggregation at that group level.

 

[jmark@stayintouch.us]

yes, but the report uses hidden running group summaries that the query can not. I believe that is necessary to get the accurate numbers.

 

[June7]

Unfortunately, I just don't follow the calculation logic and therefore cannot offer alternative solution.

I also tried using MAIN query as source for Department aggregation and that looked even worse.

 

[jmark@stayintouch.us]

I know its very complicated and very difficult to understand other peoples' programming. Thanks for trying.