New to Report - Count number problem

[Vegboy]

First of all, we have a database for all company customer cases (through out the whole year of 2006). Every case has its own priority level.

‘Priority’ column has three option, ‘High’, ‘Medium’, and ‘Low’.

We are new to Access, and we are trying group all the case month to month (group them by their created date) on separate pages. At the end of each monthly summary, we’d like to do a number count on ‘High’, ‘Medium’, and ‘Low’.

Here is a sample that we are trying to get to…

Sample Datebase,
Case #// Created Date// Priority Level
001 12/5/06 High
002 12/7/06 Low
003 12/3/06 High
004 12/1/06 Medium
005 11/9/06 Medium

Sample Report we are trying to get to...
Summary for December:
Case #// Created Date// Priority Level
001 12/5/06 High
002 12/7/06 Low
003 12/3/06 High
004 12/1/06 Medium

Total Case: 4
High Priority Case: 2
Medium Priority Case: 1
Low Priority Case: 1

your help will be very appreciated!

 

[RoyVidar]

In the report, group by your date. In the sorting/grouping thingie you'll find when in the report design view, you'll probably need both header and footer fro the date field, then in the "Group On" part, select Month.

In the group footer, add four text controls, with controlsource of

=Count([Case #])
=Sum(ABS([Priority Level] = "High"))
=Sum(ABS([Priority Level] = "Medium"))
=Sum(ABS([Priority Level] = "Low"))

In the next db, you'll probably avoid special characters, and spaces in the object names

Edit: to have some text/as in the "Summary for December:", use for instance one a text control with label. Use the label for the text, and in the text control, use the date field as controlsource, and in the property dialog, use the format of mmmm (four "m"s)

 

[Vegboy]

That's great. The counting feature worked. many thanks!!

 

[Vegboy]

Edit: to have some text/as in the "Summary for December:", use for instance one a text control with label. Use the label for the text, and in the text control, use the date field as controlsource, and in the property dialog, use the format of mmmm (four "m"s)

would you please explain this part to me again? we am having a hard time following your instruction.

 

[Vegboy]

also, is there a way to start monthly summary on new pages?

for example,
page 1-2,
January, 2006 Case Summary, blah, blah

start with a new page
page 3-5,
February, 2006 Case Summary, blah, blah.

start with a new page
page 6-9
March, 2006 Case Summary, blah, blah

go on...

**
We already did 'Sorting and Grouping' Date-Created field, and set 'Keep Together' at 'Whole Group'. and all we received were the space gaps between each group.

are we able to do a 'page break' after the footer?

thanks.

 

[RoyVidar]

Controlsource is a property of a control, found on the data tab of the property dialog. For this new text control, which is still unbound, let the date field be the controlsource. Then enter the format tab, enter four times the letter "m", i e mmmm in the format property of the control.

While still having the property dialog open, click the Header section for your date field. In the Format property (still format tab), select "Before section" in the "Force new page" property. Alternatively, try "After Section" for the date fields footer section.

 

[Vegboy]

wow Worked