Access 07 Report - Need to Count lines in a Group

I have a summary report that Groups on [Date] by day. I need to count the
number of days on which records occur. Trying the "obvious" =COUNT([Date])
returned the total number of records rather than the total number of days.

Thank You!
TraciAnn

Finally!!!!

I found the answer on Steve Jorgensen's web site
http://mvps.org/access/reports/rpt0016.htm

To count the number of groups on a report:

Add a control to the header or footer of the group to be counted and name it
something like txtGroupCount. Make the control invisible, set its control
source to =1, and set its Running Sum property to Over All. This effectively
generates a running count of the number of groups.

In the report footer, add a control with a Control Source of
=[txtGroupCount]. This displays the value of txtGroupCount as of the last
group that occurs in the report at which time it is equal to the number of
groups in the report.

Note that the group count can only be displayed in the report footer (not
the header) since that is the only point at which txtGroupCount contains a
value equal to the total number of groups.

This technique can be modified to show the number of groups within the
group immediately surrounding the group to be counted by setting the Running
Sum property of the first control to Over Group, and placing the second
control in the surrounding group's footer instead of the report footer.

Thanks Steve!!!
TraciAnn

"TraciAnnNeedsHelp" wrote
in message ...
Finally!!!!

I found the answer on Steve Jorgensen's web site
http://mvps.org/access/reports/rpt0016.htm

To count the number of groups on a report:



Hi TraciAnn!

Feels good to find a solution, doesn't it?

Thanks for sharing with the group ... this one looks like information
I'll be able to use g.

--
Clif

Yeah...it felt great...for a second. Then I ran into another stumper and have
been trying to figure it out ever since. I was being stubborn, now I have to
swallow my pride.

Maybe you can help.

I'm not sure if I need to do this on the query level or the report level
but...

The report is displaying a summary of SLA tblTickets which have
tblRequestParts associated to them. The Tickets table has a foreign PartID
field with a one to many (RequestParts to Tickets) so that one Ticket can
have several Requested Parts for it.

I have an initial query qfltTicketsAndParts that just filters the data I
need from the two tables.

A second query qryTicketsAndParts takes the fields from the inital query and
adds another table which looks up part costs.

rptTicketsAndParts is the report that draws from the second query and
summarizes the data Group on [TicketDate] by Day. This is the only group.

Well, part of the summary is to count total tickets. It doesn't seem like a
problem, but, the report is counting each instance of the [TicketID] from the
RequestParts table. Therefore, I have the same number of Tickets as I do part
requests.

I only want to count each ticket once rather than each time it has a
RequestedPart.

Can you help Clif?

Thanks!
TraciAnn

I hope your pride doesn't taste as bitter as mine sometimes does grin.

Perhaps I can help ... my one and only Access app only gets attention in
fits and starts when I steal time from something else .... and so far
I've done very little with reports. Also, I find that I tend to forget
things that I figured out 6 months ago but only did once (or that I
read on the ng but never found opportunity to try it out.)

I'm thinking that you want to create a 2nd level group on tickets and
count groups ... a running sum over a lower level group instead of over
data, if that makes sense to you.

In the built-in help (A2003) I searched for "report grouping" and in the
topics:

Print the group header on the same line as the first detail record
Count the number of records in each group or report

I found information that I think you can adapt to your need. The idea is
to add a group on Tickets, put the hidden "TicketCount" textbox in the
group footer with the running sum property set to over report and
reference it in your report footer.

(I think I have seen discussion about counting groups in the past.)

If this is a 'bad' suggestion I hope some experienced developers are
watching this thread and jump in!

HTH
--
Clif

"TraciAnn" wrote in message
...
Yeah...it felt great...for a second. Then I ran into another stumper
and have
been trying to figure it out ever since. I was being stubborn, now I
have to
swallow my pride.

Maybe you can help.

I'm not sure if I need to do this on the query level or the report
level
but...

The report is displaying a summary of SLA tblTickets which have
tblRequestParts associated to them. The Tickets table has a foreign
PartID
field with a one to many (RequestParts to Tickets) so that one Ticket
can
have several Requested Parts for it.

I have an initial query qfltTicketsAndParts that just filters the data
I
need from the two tables.

A second query qryTicketsAndParts takes the fields from the inital
query and
adds another table which looks up part costs.

rptTicketsAndParts is the report that draws from the second query and
summarizes the data Group on [TicketDate] by Day. This is the only
group.

Well, part of the summary is to count total tickets. It doesn't seem
like a
problem, but, the report is counting each instance of the [TicketID]
from the
RequestParts table. Therefore, I have the same number of Tickets as I
do part
requests.

I only want to count each ticket once rather than each time it has a
RequestedPart.

Can you help Clif?

Thanks!
TraciAnn



--
Clif

Try the following:
--add another grouping level based on Tickets and show the group header
--set the visible property of the group header to false (no)
--add a textbox control to the group header
-- Name: txtCountTickets
-- Value: =1
-- Running Sum: OverAll
-- In the report footer, add a textbox control and set its control
source to =[txtCountTickets]

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


TraciAnn wrote:

Well, part of the summary is to count total tickets. It doesn't seem like a
problem, but, the report is counting each instance of the [TicketID] from the
RequestParts table. Therefore, I have the same number of Tickets as I do part
requests.

I only want to count each ticket once rather than each time it has a
RequestedPart.

Can you help Clif?

Thanks!
TraciAnn

John, That's PERFECT!!! Thank You!!!

I'm almost there with this issue. I finally got the right total count but I
also need the daily count to work, too.

Currently, on the top grouping [CreatedDate] by Day, each line gives me sums
of their respective data, except [Tickets] which still gives me total entries.

Example:
------------------------------
Date Tickets Parts
3/2/09 116 116
3/3/09 41 41
3/4/09 61 61
3/5/09 50 50
3/6/09 82 82
Total 350 350
------------------------------
txtCountTickets = 204

As you can see, the totals in both columns are the same, where the first
column should add up to equal the txtCountTickets.

Thanks Guys!!!
TraciAnn


"John Spencer" wrote:

Try the following:
--add another grouping level based on Tickets and show the group header
--set the visible property of the group header to false (no)
--add a textbox control to the group header
-- Name: txtCountTickets
-- Value: =1
-- Running Sum: OverAll
-- In the report footer, add a textbox control and set its control
source to =[txtCountTickets]

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


TraciAnn wrote:

Well, part of the summary is to count total tickets. It doesn't seem like a
problem, but, the report is counting each instance of the [TicketID] from the
RequestParts table. Therefore, I have the same number of Tickets as I do part
requests.

I only want to count each ticket once rather than each time it has a
RequestedPart.

Can you help Clif?

Thanks!
TraciAnn

"TraciAnn" wrote in message
...
John, That's PERFECT!!! Thank You!!!

I'm almost there with this issue. I finally got the right total count but
I
also need the daily count to work, too.

Currently, on the top grouping [CreatedDate] by Day, each line gives me
sums
of their respective data, except [Tickets] which still gives me total
entries.

Example:
------------------------------
Date Tickets Parts
3/2/09 116 116
3/3/09 41 41
3/4/09 61 61
3/5/09 50 50
3/6/09 82 82
Total 350 350
------------------------------
txtCountTickets = 204

As you can see, the totals in both columns are the same, where the first
column should add up to equal the txtCountTickets.

Thanks Guys!!!
TraciAnn


"John Spencer" wrote:

Try the following:
--add another grouping level based on Tickets and show the group header
--set the visible property of the group header to false (no)
--add a textbox control to the group header
-- Name: txtCountTickets
-- Value: =1
-- Running Sum: OverAll
-- In the report footer, add a textbox control and set its control
source to =[txtCountTickets]

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


TraciAnn wrote:

Well, part of the summary is to count total tickets. It doesn't seem
like a
problem, but, the report is counting each instance of the [TicketID]
from the
RequestParts table. Therefore, I have the same number of Tickets as I
do part
requests.

I only want to count each ticket once rather than each time it has a
RequestedPart.

Can you help Clif?

Thanks!
TraciAnn

"TraciAnn" wrote in message
...
John, That's PERFECT!!! Thank You!!!

I'm almost there with this issue. I finally got the right total count
but I
also need the daily count to work, too.

Currently, on the top grouping [CreatedDate] by Day, each line gives
me sums
of their respective data, except [Tickets] which still gives me total
entries.

Example:
------------------------------
Date Tickets Parts
3/2/09 116 116
3/3/09 41 41
3/4/09 61 61
3/5/09 50 50
3/6/09 82 82
Total 350 350
------------------------------
txtCountTickets = 204



TraciAnn - what is the control source for Tickets? Try using
txtCountTickets as the control source.
You may need to Hide (visible = no) the Tickets control and use a new
control in it's place to get what you're after.


--
Clif

=Count([TicketID]) is the control source in the CreatedDate Header.

When I try to use [txtCountRequests] as the control source, it isn't
recognized. Rather, it asks for the parameter value for "txtCountRequests".

Aaargh!!!

I've been trying what you do. Reviewing other peoples' unrelated issues. I'm
learning allot, but I'm starting to get pressure to get this report done.

Any other suggestions?

Thanks Clif!

--
TraciAnn


"Clif McIrvin" wrote:

"TraciAnn" wrote in message
...
John, That's PERFECT!!! Thank You!!!

I'm almost there with this issue. I finally got the right total count
but I
also need the daily count to work, too.

Currently, on the top grouping [CreatedDate] by Day, each line gives
me sums
of their respective data, except [Tickets] which still gives me total
entries.

Example:
------------------------------
Date Tickets Parts
3/2/09 116 116
3/3/09 41 41
3/4/09 61 61
3/5/09 50 50
3/6/09 82 82
Total 350 350
------------------------------
txtCountTickets = 204



TraciAnn - what is the control source for Tickets? Try using
txtCountTickets as the control source.
You may need to Hide (visible = no) the Tickets control and use a new
control in it's place to get what you're after.


--
Clif