complex(?) sorting/grouping question 1

[Eupher]

I have report, based on a query, that displays a list of all people not available for work during a given time frame. The report lists the employee's name, the reason, and the time frame of their non-availability. The underlying query has 5 fields: [Name], [StartDate], [Description], [StartDateTime], and [EndDateTime]. [StartDate] is just a formatted version of [StartDateTime] and is used to sort the query result and also as selection criteria for the date range. As it now stands, there is no sorting/grouping in the report, and the output looks something like this:

Personnel Unavailable
8/1/03 - 8/7/03

Name:        Reason:      From:          To:
Doe, J.      Vacation     1630, 7/15/03  0800, 8/15/03
Roe, J.      Sick Leave   0001, 8/1/03   1200, 8/2/03
Smith, S.    Training     0800, 8/2/03   1630, 8/10/03
Jones, J.    Vacation     1630, 8/7/03   0800, 8/15/03
etc.

What I want the report to do is display the unavailable personnel for EACH DAY during the given time frame, so scheduling staff can, at a glance, know who's gone for any given day. Yes, I know they could just print out the report for a single day, but they like to keep the info in a "week-at-a-glance" or "month-at-a-glance" format. So, ideally the report would look something like:

Personnel Unavailable
8/1/03 - 8/7/03

8/1/03:
Name:        Reason:      From:          To:
Doe, J.      Vacation     1630, 7/15/03  0800, 8/15/03
Roe, J.      Sick Leave   0001, 8/1/03   1200, 8/2/03

8/2/03:
Name:        Reason:      From:          To:
Doe, J.      Vacation     1630, 7/15/03  0800, 8/15/03
Roe, J.      Sick Leave   0001, 8/1/03   1200, 8/2/03
Smith, S.    Training     0800, 8/2/03   1630, 8/10/03

8/3/03:
Name:        Reason:      From:          To:
Doe, J.      Vacation     1630, 7/15/03  0800, 8/15/03
Smith, S.    Training     0800, 8/2/03   1630, 8/10/03

etc...

How can I do this? Thanks!

Ken S.

 

[cghoga]

Go to View - Sorting and Grouping
Make sure to select your Date field in the field/expression column
Set the Group Header to Yes
Close the window
Make sure on your report that the date is in the header
Place your other fields in the detail section
Now, run it.

Is this what you needed?

 

[Eupher]

I tried that, it doesn't give me what I want. It gives me this instead:

Personnel Unavailable
8/1/03 - 8/7/03

7/15/03:
Name:        Reason:      From:          To:
Doe, J.      Vacation     1630, 7/15/03  0800, 8/15/03

8/1/03:
Name:        Reason:      From:          To:
Roe, J.      Sick Leave   0001, 8/1/03   1200, 8/2/03

8/2/03:
Name:        Reason:      From:          To:
Smith, S.    Training     0800, 8/2/03   1630, 8/10/03

8/7/03:
Name:        Reason:      From:          To:
Jones, J.    Vacation     1630, 8/7/03   0800, 8/15/03

Thanks for the suggestion, though.

Ken S.

 

[dhookom]

If I were you, I would start by creating a table with all potential dates as the single field [TheDate]. You could then add this to your query and don't add any join lines. Add [TheDate] to the grid and set its criteria to:
Between [StartDateTime] and [ToDateTime]. This should create one record for each date the person is gone.

Duane
MS Access MVP

 

[Eupher]

Yes, that's it exactly. Bravo! A star for you...

Thanks!

Ken S.

 

[Eupher]

Hmm...

I wonder which would perform better:

1) Fill the date table with all dates, starting from, say, 1/1/03 up through the next 10 years or so? Or...

2) Leave the date table empty and dynamically empty/fill it with only the required dates via code at run-time?

Whaddya think?

Ken S.

 

[dhookom]

I would create the table and all values ahead. Make sure you have a primary key on the date field. BTW: I would never use Date as a field name. Index your datetime fields in your other table.

Duane
MS Access MVP