Count Number of Subgroups in Group Header

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

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 said:
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.
Click to expand...

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).
 
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.
 
JD said:
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.
Click to expand...


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
 
Back
Top