Group Totals

[Deleco]

Hi,

I have a bit of a dilema, i think it should be really easy but i just can't seem to get my head around it.

I have a table VISIT_HEADER and this holds information about the VISIT i.e Charge Days and the Visit Type. Then i have an ASSESSOR_BOOKINGS table which holds the dates of the Visit and the Assessor doing the visit. A VISIT can actually have more than 1 physical visit hence the extra table.

So fo instance VISIT 0900112 is for 12 Chargeable days and will take place on the 01/06/2005-05/06/2005 and 11/06/2005-15/06/2005.

My problem is when the VISIT_HEADER contains more than 1 physical visit it messes up the group totals as the Charge Days are added for each detail line. I need to include ASSESSOR_BOOKINGS in the report as this is the only table which contains the dates.

for instance

Visit Num	Assessor	ChargeDays	Date From	Date To
0900023	Dean		4		
						01/06/2005	02/06/2005								11/06/2005	12/06/2005

		Group Total	8 (I actually want this to be 4)

Hope this makes sense and someone can help
Thanks

Deleco

 

[Madawc]

Would Maximum solve it? If not, please show the detail records so that we can figure out another way.

Madawc Williams (East Anglia, UK)

 

[Deleco]

Hi,

The details are just the Dates. Obviously i get the correct total if there is only one set of dates, but when there are multiple dates per visit this is when it becomes a problem. The reason being that the value i need to SUM is in the VISIT_HEADER table and not the ASSESSOR_BOOKINGS table.

Thanks

Deleco

 

[Madawc]

It's still not clear what you're doing. If there are several dates, do a Maximum of each and then a formula field to compare.

Madawc Williams (East Anglia, UK)

 

[Deleco]

VisitNum     Assessor    ChrgDays    DateFrom   DateTo
[b]098211       Dean        [/b]
                          5          01/06/05   02/06/05
                          5          11/06/05   12/06/05
             [b]Total       10[/b]

[B]075535       Ben       [/b]
                          3          15/09/05   18/09/05
             [b]Total        3[/b]

             [b]Report Total 13[/b]

I hope this makes it more clear. The Bold sections are the group header and group footer. The dates are the detail section. The ChrgDays is actually part of the group table VISIT_HEADER. Whatever i do when trying to use the Insert Summary Function it always adds up the ChrgDays as if they were part of the detail (ChrgDays is only on the detail line for example).

Example number 1 is wrong as the ChrgDays total should be 5. I can see why this is happening as there is 2 detail lines.

Example number 2 is correct as the ChrgDays total should be and is 3. This is because there is 1 detail line.

The Report Total is wrong as it should be 8 and not 13 that it is reporting.

I can add the ChrgDays to the group section and the group totals will always be correct as the ChrgDays field is from the group table, but the Report Total is still in correct as it adds up ChrgDays as if it was part of te detail.

What i actually want is a formula for my Report Total that only takes into consideration the values of the Group header and not the details section.

Thanks for your assistance

Deleco

 

[lbass]

Try using a running total which does a sum of {table.chgdays}, evaluate on change of group (visit number), reset never.

-LB

 

[Deleco]

Thank you,,, running totals did the job

Deleco