Counting within a group

[Guest]

Hi,
In my Summary report, there are two groups: jobNo within Category.
jobNo contains data which can be repetative, but not for all records.
In other words, there can be:
6 records with jobNo 1234,
2 records with 4567 and
1 record for 9701.

I want to count the number of times that jobNo changes, but not the total
number of jobNo records. I tried a query using an expression Max(jobNo), then
tried to sum it in the report, but it gives the total number for jobNo's. How
can I achieve the result I am seeking?


Thanks in advance,
Jake

 

[Guest]

You can count distince jobno values in a couple ways. One solution is to
create a totals query that groups by Category and JobNo. Then create another
totals query based on your previous one that groups by Category and counts
JobNo.

Add this last totals query to your report's record source and join the
Category fields. Drop the CountOfJobNo field into the grid for use in your
report.

 

[Allen Browne]

If you want to list all the records on the report, but at the end give a
count of the number of distinct job numbers:
1. Open the report in design view.

2. Open the Sorting And Grouping dialog.

3. Add JobNo to the dialog, and in the lower pane, set:
Group Header Yes
Access adds a new section to the report.
(You can set the Visible property of this section to No if you don't want it
to print.)

4. Add a text box to this new section, and set these properties:
Control Source =1
Running Sum Over All
Format General Number
Name txtCountRS

5. Add another text box to the Report Footer section, and set its Control
Source to:
=[txtCountRS]

The text box in the JobNo group header adds 1 each time there is a new
JobNo. The Running Sum accumulates these values, giving a count of the
distinct job numbers. Finally the text box in the report footer shows the
count.

 

[Guest]

Super! This sounds exactly like what I am looking for..
Thanks much!!

Jake

If you want to list all the records on the report, but at the end give a
count of the number of distinct job numbers:
1. Open the report in design view.

2. Open the Sorting And Grouping dialog.

3. Add JobNo to the dialog, and in the lower pane, set:
Group Header Yes
Access adds a new section to the report.
(You can set the Visible property of this section to No if you don't want it
to print.)

4. Add a text box to this new section, and set these properties:
Control Source =1
Running Sum Over All
Format General Number
Name txtCountRS

5. Add another text box to the Report Footer section, and set its Control
Source to:
=[txtCountRS]

The text box in the JobNo group header adds 1 each time there is a new
JobNo. The Running Sum accumulates these values, giving a count of the
distinct job numbers. Finally the text box in the report footer shows the
count.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
In my Summary report, there are two groups: jobNo within Category.
jobNo contains data which can be repetative, but not for all records.
In other words, there can be:
6 records with jobNo 1234,
2 records with 4567 and
1 record for 9701.

I want to count the number of times that jobNo changes, but not the total
number of jobNo records. I tried a query using an expression Max(jobNo),
then
tried to sum it in the report, but it gives the total number for jobNo's.
How
can I achieve the result I am seeking?

Thanks in advance,
Jake