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

June7

AWF VIP
Local time
Yesterday, 18:34
Joined
Mar 9, 2014
Messages
5,423
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.
 
Local time
Yesterday, 21:34
Joined
Apr 25, 2019
Messages
62
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

AWF VIP
Local time
Yesterday, 18:34
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:
Local time
Yesterday, 21:34
Joined
Apr 25, 2019
Messages
62
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

AWF VIP
Local time
Yesterday, 18:34
Joined
Mar 9, 2014
Messages
5,423
I did post the new query I tried. It is only the one. It uses your existing query as source.
 
Local time
Yesterday, 21:34
Joined
Apr 25, 2019
Messages
62
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

AWF VIP
Local time
Yesterday, 18:34
Joined
Mar 9, 2014
Messages
5,423
Open a new query object in SQLView. Copy/paste or type the SQL statement I provided. Switch to Design View.
 
Local time
Yesterday, 21:34
Joined
Apr 25, 2019
Messages
62
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
 
Local time
Yesterday, 21:34
Joined
Apr 25, 2019
Messages
62
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

AWF VIP
Local time
Yesterday, 18:34
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:
Local time
Yesterday, 21:34
Joined
Apr 25, 2019
Messages
62
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

AWF VIP
Local time
Yesterday, 18:34
Joined
Mar 9, 2014
Messages
5,423
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.
 
Local time
Yesterday, 21:34
Joined
Apr 25, 2019
Messages
62
I know its very complicated and very difficult to understand other peoples' programming. Thanks for trying.
 

Users who are viewing this thread

Top Bottom