Access 07 Report - Need to Count lines in a Group

[Clif McIrvin]

TraciAnn -- I think I have got it figured out.

Think "I need to count sub-groups", not "I need to count Tickets."

Using some of my data, I have come up with this:


Date Location Items
.... this is page 8 ........
2/23/2009 2 3
2/24/2009 4 2
2/25/2009 2 4
2/26/2009 2 2
3/2/2009 1 2
3/3/2009 2 2
3/4/2009 2 4
3/5/2009 1 6
3/6/2009 1 10
12/18/2009 1 2

Totals 483 1161

I counted the records using a select query, and the totals are correct.

You will need eight unbound text boxes and two groups to get the totals
you want.

I think you have the groups working as you need, we just need to get the
running sums doing what you want.

On the Detail section you need two unbound text boxes:
--- Name: txtPartsInGroup txtPartsAll
--- Control Source: =1 =1
--- Running Sum: Over Group Over All

On the Tickets (hidden) group header you need two unbound text boxes:
--- Name: txtTicketsInGroup txtTicketsAll
--- Control Source: =1 =1
--- Running Sum: Over Group Over All

On the Date Footer you have two unbound text boxes:
--- Control Source: txtTicketsInGroup txtPartsInGroup
--- Running Sum: No No

On the Report Footer you have two unbound text boxes:
--- Control Source: txtTicketsAll txtPartsAll
--- Running Sum: No No

(You already have Date behaving itself, so I didn't count it <g>.)

 

[TraciAnn]

I think we are almost there!

Using your directions I am getting the Over All number of Tickets, but I'm
still not getting the number of (unique) Tickets per day.

Going back to the example, this is what I'm getting:
------------------------------
1 - Date Tickets Parts
2 - 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
3 - Total 204 350
------------------------------
The first column of numbers is identifying the section they are in.
1 = Page Header
2 = Date Header
3 = Report Footer

All other sections are hidden (Ticket Header, Detail, Date Footer)

This is what I should be getting (Notice the Tickets column values):
------------------------------
Date Tickets Parts
3/2/09 74 116
3/3/09 27 41
3/4/09 28 61
3/5/09 41 50
3/6/09 34 82
Total 204 350

 

[Clif McIrvin]

On line 2 what is the controlsource for Tickets? (Date Header)

 

[TraciAnn]

=Count([TicketID])
--
TraciAnn

On line 2 what is the controlsource for Tickets? (Date Header)

 

[Clif McIrvin]

Thanks ...

change it to

=[name of running sum over group textbox on Tickets Header]

*and* move it (and the other Date Header controls) to the Date Footer.

--
Clif

=Count([TicketID])

 

[Clif McIrvin]

TraciAnn - I pulled up the test report I made yesterday and tried using
the group header section instead of the footer ... the group header line
*always* printed a value of 1, while the footer line shows the correct
value.

I suspect that your group header textbox for Tickets still has the wrong
control source - it needs to point to a running sum in the Tickets
Group, not ; also you will probably need to move those controls from the
group header to the group footer.

It's a matter of timing ... when formatting the group header no counting
for that group has been done yet.

 

[TraciAnn]

(Angelic Chorus in HD Surround Sound) Hallelujah...Hallelujah!!!!

All I needed to do was move everything to the Date Footer. And all my
numbers were there. That is EXACTLY what I needed.

Thank you SO MUCH Clif!!! And everyone else that helped!

Clif...Between you and me...we've got to get my Posts back down in the
single digits

 

[Clif McIrvin]

Great!

It was a chance for me to investigate some reporting design that will
likely prove useful ...

Getting things to behave sure is great, isn't it?