I have a report with two groups and a detail section.  The groups are 
Location and Person and the detail lists services.  I already have a count of 
each service per person and each service per location.  What I need is a 
count of each person per location.  I've done this in the Location footer 
with no problem, but I need to show it in the Location header instead.

I have a text box with a control of =1 that sums over the group in the 
Person group header.  I just refer to that text box in my location footer and 
all is good.  But when I try to refer to that same text box from my location 
header, it shows 1.

How can I get this to show the way I need it to?

JD wrote:

>I have a report with two groups and a detail section.  The groups are 
>Location and Person and the detail lists services.  I already have a count of 
>each service per person and each service per location.  What I need is a 
>count of each person per location.  I've done this in the Location footer 
>with no problem, but I need to show it in the Location header instead.
>
>I have a text box with a control of =1 that sums over the group in the 
>Person group header.  I just refer to that text box in my location footer and 
>all is good.  But when I try to refer to that same text box from my location 
>header, it shows 1.
>

It can be very difficult to display a value before if has
been calculated.  Depending on your version of Access, you
might(?) be able to have the Location group header text box
refer to the Location group foot text box (instead of the
detail running sum text box).

If that does not work for you, I strongly suggest that you
forget about displaying it in the header and live with it in
the footer (because it involves a lot of advanced VBA coding
and some tricks in the report that may cause a performance
hit).

-- 
Marsh
MVP [MS Access]

Due to the nature of my report, it has to be in the header.  I have found a 
work around and thought I would post it in case someone else runs into the 
same problem.

I created two queries with just the location and the person.  The first 
query groups these fields.  The second query (using the first at the source) 
groups by location and counts the person field.  I then took the second query 
and joined it to the report's source table by the location field.  I then 
just added the CountofPerson field to the location group header.  Kind of 
crude, but gets the job done.

Thanks for your reply and if you have any other suggestions based on my 
solution, they would be most welcome.

"Marshall Barton" wrote:

> JD wrote:
> 
> >I have a report with two groups and a detail section.  The groups are 
> >Location and Person and the detail lists services.  I already have a count of 
> >each service per person and each service per location.  What I need is a 
> >count of each person per location.  I've done this in the Location footer 
> >with no problem, but I need to show it in the Location header instead.
> >
> >I have a text box with a control of =1 that sums over the group in the 
> >Person group header.  I just refer to that text box in my location footer and 
> >all is good.  But when I try to refer to that same text box from my location 
> >header, it shows 1.
> >
> 
> It can be very difficult to display a value before if has
> been calculated.  Depending on your version of Access, you
> might(?) be able to have the Location group header text box
> refer to the Location group foot text box (instead of the
> detail running sum text box).
> 
> If that does not work for you, I strongly suggest that you
> forget about displaying it in the header and live with it in
> the footer (because it involves a lot of advanced VBA coding
> and some tricks in the report that may cause a performance
> hit).
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 

JD wrote:
>Due to the nature of my report, it has to be in the header.  I have found a 
>work around and thought I would post it in case someone else runs into the 
>same problem.
>
>I created two queries with just the location and the person.  The first 
>query groups these fields.  The second query (using the first at the source) 
>groups by location and counts the person field.  I then took the second query 
>and joined it to the report's source table by the location field.  I then 
>just added the CountofPerson field to the location group header.  Kind of 
>crude, but gets the job done.
>
>Thanks for your reply and if you have any other suggestions based on my 
>solution, they would be most welcome.


Axtually, I should have mentioned the multiple query
approach.  While it is kind of clumsy, it is an acceptable
way to go about it.

If it isn't too comfusing, you could do it all in the record
source query with something like:

SELECT table.*, C.PersonCount
FROM table INNER JOIN
		(SELECT X.joinfield, X.Count(*) As PersonCount
		 FROM table As X
		 GROUP BY X.location, X.Person) As C
	ON table.joinfield = C.joinfield

-- 
Marsh
MVP [MS Access]