Summing on Reports

[petermb72]

I hope I can get this across correctly. I have several locations. Those locations input one line of data a day into the system. Here is a some of the data that they enter:


Location
Date
Total number of positives in last 24 hours
Total number of negatives in last 24 hours
Total number of Positives in the facility
Tests given in last 24 hours

On the report I hide the detail and show the totals and I have the data grouped by location. I hide the detail and show the totals in the group footer. I do a Dlast for the Total number of positives in the facility. I do a sum for Number of tests done (we want total number of tests over time not just the last 24 hours).

This works fine. What I want is to be able to total all of the Dlast totals in the report footer to get a grand total of positives. We do not want to use the total number of positives in the last 24 hours as they can not always be accurate as the Total number of positives number.

I hope I explained that ok.



Thanks for your help
Peter

[ranman256]

In the footer ,cant you put the field: =sum([Total number of Positives in the facility])

or are you wanting to subtract the other fields from it?

if so, put all totals in the footer, HIDE them all , then add 1 visible text box that shows the result (txtResult )
note: you must know the text box names (or assign them) in order to do the math:

txtSumPos24=Total number of positives in last 24 hours

txtSumNeg24=Total number of negatives in last 24 hours

txtSumPosAll=Total number of Positives in the facility

txtResult =txtSumPosAll - txtSumPos24

[CJ_London]

always helps to provide some example data and the outcome required

[petermb72]

always helps to provide some example data and the outcome required

I shortened up what I was looking for.
The report looks like this:

Page Header(column headings):
Location Census Active Positives Total number of tests given

Location Header (Hidden)
Detail (Hidden)
Location Footer:
Location Census =CDbl(DLast([Census],"Data")) Active Positives =CDbl(DLast([Total Positive Residents],"Data")) Total Number of test given =Sum([Number of Tests Done])

the =CDbl is in the control source property

This gives me a grand total for the total number of tests given by adding all of them together. No problem. But how do I get the Census and the Active Positives grand total. Here is what the report would look like:

Locaton census Active Positives total number of tests given
Uptown 100 10 95
Downtown 40 1 4
West 114 20 113


What I am looking for is how do I total those columns when census and active positives are Dlast totals and not a Sum of the whole column? I still want total number of tests just to be a sum. Does this make sense?

[petermb72]

yuck that did not format well at all

[CJ_London]

if you go to the advanced editor you can add a table (bottom ribbon on left)

col1	col2
A	123
B	321

and from your example data, show the expected result

[petermb72]

if you go to the advanced editor you can add a table (bottom ribbon on left)

col1	col2
A	123
B	321

and from your example data, show the expected result

Ok here is what I have in a basic form I will only talk about pertinent columns.

Design view of report:

Page Header	Location	Total Positives	Number of Tests
Location Header	hidden
Detail	hidden
Location Footer		=CDbl(DLast([Total Positives],"Data"))	=Sum([Number of tests])

The above gives me a report that looks like:

location	Total Positives	Number of tests
Downtown	5	20
Uptown	19	200
East	4	40

The table data looks Like this

Date	location	total positives	number of tests	New positives
1/1/2020	Uptown	0	15
1/2/2020	Uptown	3	25	3
1/3/2020	Uptown	4	1	1
1/4/2020	Uptown	4	13	0
1/1/2020	downtown	3	10	3
1/2/2020	downtown	4	1	1
1/3/2020	downtown	4	45	0
1/4/2020	downtown	4	0	0

What I want is on the report to show a grand total of the all the locations at the bottom of the report. The problem I have is that I am not sure how to do that seeing that the sub totals I use are using the Dlast formula to get the number rather than any sort of Sum.

[CJ_London]

show a grand total of the all the locations at the bottom of the report

Sorry to be pedantic but what would that look like?

I can't see how you have derived this data from your data table

location	Total Positives	Number of tests
Downtown	5	20
Uptown	19	200
East	4	40

The data does not include East and to me the data is saying positives for downtown should be 15 (or should that be 4?) , not 5 - same inconsistencies for the other figures

Also, dlast is not a good function to use. It all depends on an order - and you can't specify order with dlast.

[petermb72]

I was just showing an example the data really doesn't matter. let me try to put one together start to finish for you. Really what I need to know is if there is a way to total a bunch of subtotals on a report that that were derived from the formula using DLAST

[CJ_London]

better to focus on the requirement than assuming how you are doing it is the correct way