Count in detail section of a report

[ccordner]

Hi

I have a report about locations.

Each manager looks after several cost centres, each of which may have one or several cost centres assigned to it.



So the report is grouped by manager, then by cost centre.

Each cost centre has a target.

The columns (in the detail section) are Location, Target, Actual. Where there are several locations for a cost centre, I want to be able to divide the target by the number of locations.

But

I then want to be able to sum up the targets for each manager.

How do I count the number of entries in the 'detail' section?

Thanks
Chris

[RayMilhon]

You don't. From your description you need to add a group footer for location and do your counts there.

[ccordner]

Thanks

I have added a footer section, for the Cost Centre and a field which counts the locations. I have named this "txtLocationCount" and I have changed the box in the detail to "=Target (Rest Days)/txtLocationCount", which works fine.

Yet if I put in a second field, called "txtTargetRestDays" and set the control source to "Target (Rest Days)" (a field in the query), and put a field in the Manager footer saying "=sum([txtTargetRestDays])" then when I open the report I get asked to tell it what txtTargetRestDays is.

Any ideas why?

Chris

[R_Badger]

Expressions on reports need to have the field you are referring to on the report (iirc). Whether the field is visible or not isn't an issue though, so if you place the field on your report it should then be able to reference it.

I hope that helps!

[ccordner]

Hi

I think the easiest way to explain this is with the attached image. All the lilac boxes and the red "Manager" can be ignored.

In the detail, "Location" lists all the locations per cost centre: usually one, but sometimes several.

In the Cost Centre Footer, the field counts these locations and the other field in the detail divides a target by the number of locations.

This works fine.

In the Manager Footer, I want to add up the targets for each cost centre. I have tried umpteen ways of doing it, but they all seem to count the target once for each listed location.

Any ideas why?

Text47 is called [txtRestDayTarget], but when I open the report, it asks me to put in a value for txtRestDayTarget. I don't understand why!

Chris

[R_Badger]

Try calling [text47] instead of [txtrestdaytarget] so you are calling the object on the report.

Its looking for the object [txtrestdaytarget] on your report and can't find it since it is bound within the control [text47]

[RayMilhon]

Is there a reason your summing the text box and not the field? I would use
=Sum([Target (Rest Days)]) instead of sum([txttestdaytarget])

Although [Target (Rest Days] is a really bad choice for a field name Access allows it but I personally would use TrgtRD Or Target_Rest_Days Since the table design has a description for each field I can use a simple field name and then describe it.

[ccordner]

if I use sum([Target (Rest Days)], it counts the target once for each location, not for each cost centre. So for example.

The first one happens to be 14 and there are three associated locations, so it sums the target up as 42 when it should be 14.

It's odd, but probably my error when setting it up. I'm still just getting to grips with Access, haven't touched it since school!

[RayMilhon]

OK, Here's a better solution.

click on properties for the section the txtrestdaytarget is located.

Click on Events and use the on format Event

In the event put the following

lngtarget = lngtarget + me.txtrestdaytarget

In the on format event of the section where you display the target put

(use the appropriate textbox name)

textbox = lngtarget
lngtarget = 0

At the top of the VBA you should see the following

Option Compare Database
Option Explicit This line is not automatically there so if missing add it.

Private lngtarget as long This line will have to be added

[ccordner]

That's perfect thanks! And with a bit of fiddling I think I could do a lot with similar functions.