need help on count over a second group in Access reports

Hi,

I am doing an expense and funding report which contains a list of
consumers by Region and then by SiteID. For example,

Region: NE
Site: NEMV

1. Mary Doe
2. Len Willams
3. Jack Stack
Site NEMV Totals: 3

Site: NELM

1. Shirley Temple
2. Mary Smith

Site NELM Totals: 2

Region NE: 5

Region: CW
Site: CWBK

1. Amy Wang
2. Shelley Smith
3. Jane Doe

Site CWBK: totals 3
Region CW Totals: 3


TOTAL REPORT: 8


I suppose to get the total of consumers by Site then by Region.

Now, i am getting the count of consumers by site correctly because i
have put a textbox =1 in each consumerName header section and have it
sum "over group". But i can't to get the count of consumer by region
correctly. Is there a way to get the Access to calculate totals over a
"second group(by region)" in a report?

Re: need help on count over a second group in Access reports

On Oct 16, 3:14 pm, hanshirle...@gmail.com wrote:
> Hi,
>
> I am doing an expense and funding report which contains a list of
> consumers by Region and then by SiteID. For example,
>
> Region: NE
> Site: NEMV
>
> 1. Mary Doe
> 2. Len Willams
> 3. Jack Stack
> Site NEMV Totals: 3
>
> Site: NELM
>
> 1. Shirley Temple
> 2. Mary Smith
>
> Site NELM Totals: 2
>
> Region NE: 5
>
> Region: CW
> Site: CWBK
>
> 1. Amy Wang
> 2. Shelley Smith
> 3. Jane Doe
>
> Site CWBK: totals 3
> Region CW Totals: 3
>
> TOTAL REPORT: 8
>
> I suppose to get the total of consumers by Site then by Region.
>
> Now, i am getting the count of consumers by site correctly because i
> have put a textbox =1 in each consumerName header section and have it
> sum "over group". But i can't to get the count of consumer by region
> correctly. Is there a way to get the Access to calculate totals over a
> "second group(by region)" in a report?

Can't you just add "region" to your Sorting and Grouping?

Re: need help on count over a second group in Access reports

On Oct 16, 5:59 pm, OldPro wrote:
> On Oct 16, 3:14 pm, hanshirle...@gmail.com wrote:
>
>
>
>
>
> > Hi,
>
> > I am doing an expense and funding report which contains a list of
> > consumers by Region and then by SiteID. For example,
>
> > Region: NE
> > Site: NEMV
>
> > 1. Mary Doe
> > 2. Len Willams
> > 3. Jack Stack
> > Site NEMV Totals: 3
>
> > Site: NELM
>
> > 1. Shirley Temple
> > 2. Mary Smith
>
> > Site NELM Totals: 2
>
> > Region NE: 5
>
> > Region: CW
> > Site: CWBK
>
> > 1. Amy Wang
> > 2. Shelley Smith
> > 3. Jane Doe
>
> > Site CWBK: totals 3
> > Region CW Totals: 3
>
> > TOTAL REPORT: 8
>
> > I suppose to get the total of consumers by Site then by Region.
>
> > Now, i am getting the count of consumers by site correctly because i
> > have put a textbox =1 in each consumerName header section and have it
> > sum "over group". But i can't to get the count of consumer by region
> > correctly. Is there a way to get the Access to calculate totals over a
> > "second group(by region)" in a report?
>
> Can't you just add "region" to your Sorting and Grouping?- Hide quoted text -
>
> - Show quoted text -

thanks.

yes. i did add "region" to the sorting and grouping. But there are
duplicates, for example,

Name ServiceID amount
Amy Wang DMRS 10,000
Amy Wang MEDI 25,000

where Amy Wang only should count once.

so i use a txtMTSiteTotal to keep track of unduplicate count. But the
same does not work on Region groups.
It seems like Access only allow the textbox with controlsource=1 "No",
"Overall", "over group". When i choose "over group"
it worked for the Site total...with add another textbox does not work
for the Region total.