Access 07 Report - Need to Count lines in a Group

[TraciAnnNeedsHelp]

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

 

[TraciAnnNeedsHelp]

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

 

[Clif McIrvin]

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>.

 

[TraciAnn]

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 McIrvin]

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

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

 

[John Spencer]

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]

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

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
'====================================================

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

 

[ishneha]

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

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
'====================================================

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

 

[ishneha]

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

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
'====================================================

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 McIrvin]

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

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 McIrvin]

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

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.

 

[TraciAnn]

=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

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

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.

 

[TraciAnn]

=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

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

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 McIrvin]

= [txtCountRequests]

???

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

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

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 McIrvin]

= [txtCountRequests]

???

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

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

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.

 

[TraciAnn]

I'm not sure of the implication.

In the [CreatedDate] Header, when I replaced the Control Source for the
"Ticket Count" from "=Count([TicketID])" to "=Count([txtCountRequests])" It
didn't recognize it, as stated below.

If I change the control source to simply "=[txtCountRequests]" all controls
in the report return a zero value or an error except for the Ticket Count
which = 1.

I hope I'm not frustrating you by answering a question you are not asking.

--
TraciAnn

= [txtCountRequests]

???

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

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.

 

[TraciAnn]

I'm not sure of the implication.

In the [CreatedDate] Header, when I replaced the Control Source for the
"Ticket Count" from "=Count([TicketID])" to "=Count([txtCountRequests])" It
didn't recognize it, as stated below.

If I change the control source to simply "=[txtCountRequests]" all controls
in the report return a zero value or an error except for the Ticket Count
which = 1.

I hope I'm not frustrating you by answering a question you are not asking.

--
TraciAnn

= [txtCountRequests]

???

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

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.

 

[Gina Whipp]

TraciAnn,

I believe, as per ohn's instructions, that "=Count([txtCountRequests])"
should be in the footer, is that where it is?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I'm not sure of the implication.

In the [CreatedDate] Header, when I replaced the Control Source for the
"Ticket Count" from "=Count([TicketID])" to "=Count([txtCountRequests])"
It
didn't recognize it, as stated below.

If I change the control source to simply "=[txtCountRequests]" all
controls
in the report return a zero value or an error except for the Ticket Count
which = 1.

I hope I'm not frustrating you by answering a question you are not asking.

--
TraciAnn

= [txtCountRequests]

???

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


:

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.

 

[Gina Whipp]

TraciAnn,

I believe, as per ohn's instructions, that "=Count([txtCountRequests])"
should be in the footer, is that where it is?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I'm not sure of the implication.

In the [CreatedDate] Header, when I replaced the Control Source for the
"Ticket Count" from "=Count([TicketID])" to "=Count([txtCountRequests])"
It
didn't recognize it, as stated below.

If I change the control source to simply "=[txtCountRequests]" all
controls
in the report return a zero value or an error except for the Ticket Count
which = 1.

I hope I'm not frustrating you by answering a question you are not asking.

--
TraciAnn

= [txtCountRequests]

???

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


:

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.

 

[TraciAnn]

Ignore my previous post. I was testing another feature in another report and
accidently entered code into this report causing the erroneous results (duh).

Here is what I am getting using your suggestion. Keeping with the same
scenario

I have 2 Grouping levels: 1st on [CreatedDAte] by Day, 2nd on [TicketID]
which is hidden with a single text box control txtCountTickets with a value =
1.

In the Report Footer is a Control = [txtCountTickets] which produces the
Correct Total I am looking for.

As the example shows. The [CreatedDate] Header produces a total count of ALL
TicketID's per day, on the Many side of the relationship of tbleTickets &
tblParts. On the same line as my other information for each day, I need a
count of unique TicketID's just like the [txtCountTickets] is giving me.

When I add a control with =txtCountTickets in the DateCreated Header I get
the numbers in the last column of the Example.

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

Sorry for the confusion.
--
TraciAnn

= [txtCountRequests]

???

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

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.