Running total skips last record

[catbert]

Help!
Cr 10, MS Access:
I have a fairly simple report relating to visits to addresses - where the visit has deemed to have failed a flag is checked. My crystal report filters only those failed.

I simply need a list where this has happened more than once in a specified date range.

I have data grouped as follows:
GH1 - Area
GH2 - Street
GH3 - House No

ANd I have the following running totals:

RT0 - count of house number reset on each change of house number - gives me any property which appears more than once
RT1 - Distinct count of house no, evaluated if Count of House No grouped by House No >1 and reset by street - gives me the number I want by street.
RT2 - Distinct Count of house number, evaluated again if Count of House No grouped by House No >1 and rest never

This is the problem - this should give me a sum of each rt1 in effect - all those failed more than once - and it works perfectly except for the last record.

If I suppress and view only RT1 I get:
GF2-9
GF2-3
GF2-2
GF2-5
GF2-1
RF-19
which to me adds up to 20. When I put RT2 in the details section I can see it increase as expected except for the last record - but if I change select criteria to only view the area this house is in - all totals work fine. Same if I filter out the anomalous road - the report still shows 19.

The RT0 shows an increase as it should, and the street total counts 1 as it should.

Any suggestions gratefully received.

CB

 

[ShortyA]

It sounds like the problem is around the usage of distinctcount rather than count. If there has been more than one visit to a house number or the number is duplicated eg 25 AnyStreet and 25 AnyRoad a distinctcount would should that as 1 visit when actually it is 2. Could this be the problem ?

ShortyA

 

[Naith]

I have seen this anomaly happen a few times where the RF total tries to summarise before the last record is quantified, or where subreports are featured. However, I have also noticed this on a couple of occasions where subreports are not featured, although this was said to have been fixed in version 8.

You can workaround this with variables.

@formula1 (in gf2)
WhilePrintingRecords;
NumberVar Total := Total + RT1;

@formula2 (in rf)
WhilePrintingRecords;
NumberVar Total;

The above example assumes you retain your RTs. However, you could do away with the running totals altogether, and use variables from the detail section if you wish.

Naith

 

[catbert]

Thanks ShortyA and Naith

I was just composing an answer to say I was looking at distinct count but couldn't see the issue as the data was not duplicated, and I was going to revert to the variables solution when it hit me between the eyes.

It was Distinct count_ I have house number concatenated from number/letter/name - but left out street, so of course it was duplicated and therefore not counted as the number 5, in this case, had already appeared.

At least now I know the cause - sorry for wasting your time, but thank you for putting me out of my misery - sometimes it just takes another view point.

Cheers
CB

 

[ShortyA]

Catbert,
glad you have sorted it out. We have all been there before! I cannot remember how many times usage of distinctcounts has caused me problems.

ShortyA