Refining Found Data

[selzlerb]

Hello Team,

I'm working on a database that takes attendance data for a school to generate reports. At this point I can identify different types of attendance data, and report on how many instances happen on Mondays, Tuesday, Wednesdays, etc. I can also identify the week with the most attendance entries.

I would like to be able to refine this report to specific instances of data. For example, a "tardy" is "Absence Code = A", an Unexcused Absence is "Absence Code = B" where Absence code is the field name and the "A,B,C ... J" identifies the type of attendance entry it is.

I identify days and weeks with the following calculations;

DayOfWeek(Absence Date)

Weekstart = Absence Date - DayOfWeek ( Absence Date ) + 2

Weekend = weekstart + 4

I'm assuming that I need to use these basic calculations and somehow filter them for each Absence Code I want to look at, but I'm not seeing how that can happen.

Any ideas on this would be greatly appreciated. Thanks.

- Sez

[Ender]

What is it you actually want to see in your report?

[selzlerb]

I would like to see what days of the week had the most absences. As well as the week of the year that had the most absences. I would also like to break it down by month as well as the entire school year.

Furthermore I would like to be able to identify the same information for the different "types" of absences. Excused, Unexcused, Unverified, Hospital, Illness, Independent Study, etc. Each of these types is identified by a different letter, A through J.

Thanks.

- Sez

Edited July 18, 2007 by Guest

[Ender]

This can be done with one or two sub-summary reports. I'd suggest using a single layout with multiple sub-summary parts:

Header

-----------

Sub-Summary by School Year

------------

Sub-Summary by Month

------------

Sub-Summary by Week#

------------

Sub-Summary by Day of Week

------------

Footer

On each sub-summary part, include the corresponding field and a Count of RecordID (summary) field.

Have two sort options in your report, one to sort by School Year and Month, and one to sort by School Year, Week#, and Day of Week. You can't really break the Weeks across different months (for example, you might see week 5 in both January and February), which is why I'd do month breakdowns separately from weeks.

When the sub-summary report is viewed in Preview Mode or printed, the sub-summary parts will show.

[selzlerb]

Thanks! I was thinking along these lines, but was having trouble pulling it off. I'll try the organization you suggest to see if I can pull what I need.

- Sez

[selzlerb]

Ok, I've been working on this and your suggestions have worked in the cases where I have a summary field to work with. And I can see generating "counting fields" for the months, and days of the week in order to develop summary fields for those.

But I'm running into the same problem I have in the past. That is, how do I identify the week of the year with the most absence entries as well as the day of the year with the most entries. The counting field method wont work as that would result in hundreds of fields.

So my problem is, how do I identify the highest number of the day of the year, and also the highest count of the week of the year each with the most absences? I'm guessing there is a formula that would sort this out, but this is an area where I'm really weak. Any help here would be appreciated.

Thanks.