Lame Report Requirements

[paradise95969 via AccessMonster.com]

I have to either frequently spend hours preparing an archaic report by hand
in excel for change resistant paper people or figure out how to display the
data I have in an ms access table like they want it.

Just employee start and end time pairs are in the table.

I need to convert that into strings and organize it to show who was on duty
for each 1/2 hour of the day. They are really stuck on their format- so I'm
hoping to learn how to do this if it is even possible. If it is not possible-
how should I accomplish it to represent the data visually as that is what it
really seems to me that they are asking for?



Table
name start end
Bob T 2/2/2010 8:00 am 2/2/2010 4:00 pm
Tom M 2/2/2010 10:00 am 2/2/2010 6:00 pm

Report

2/2/2010

8am BT
830 BT
9am BT
930 BT
10am BT,TM
1030 BT,TM
11am BT,TM
1130 BT,TM
12p BT,TM
1230 BT,TM
1pm BT,TM
130 BT,TM
2pm BT,TM
230 BT,TM
3pm BT,TM
330 BT,TM
4pm TM
430 TM
5pm TM
530 TM
6pm

Of course there are more than 2- 24 to be exact, with up to 7 on duty during
any 1/2 hour period around the clock. And the data would be for a 1 month
period...

 

[Allen Browne]

You can probably do this by combining a cartesian product query with a
crosstab query, provided you don't have more than 250 staff.

1. Create a table with just one field of type number, named (say) MinuteID,
and save the table as Minutes. Then enter records for 30 minute intervals to
cover the day:
0
30
60
90
...

2. Now create a query that uses your roster table and this new Minutes
table. In the upper pane of query design, there should be no line joining
the 2 tables. Type this expression into the Field row:
ReportTime: DateAdd("m", [Minutes], [start])
In the Criteria row under this, enter:
<= [end]
Save the query. This gives you a record for each half hour of each shift for
each person.

3. Create another query, and change it to a Crosstab. Use ReportTime as the
Row Heading, and the Staff field as the Column Heading. Type this expression
into the Field row:
IsRostered: ([Staff] Is Not Null)
Choose First in the Group By row, and Value in the Crosstab row.

This will give you a matrix with the times down the left, the staff across
the top, and an indication at the intersection point as to whether this
staff member is rostered at this time.

Hopefully you have another table of staff members where you can have a field
for the initials, since you seem to want to use that rather than the full
name.

If you actually need to concatenate the initials together into a string
rather than crosstab columns, you will need to use code. The crosstab will
be different, and the code will be along these lines:
http://allenbrowne.com/func-concat.html

Also, it will be best to avoid using reserved words for your field names.
All 3 of the names you used (Name, Start, and End) and known to cause
problems. Here's a list of the names to avoid:
http://allenbrowne.com/AppIssueBadWord.html

 

[paradise95969 via AccessMonster.com]

I couldn't get this to work. I ended up with a query result full of "-1"...

May I re-ask- changing the parameters?

My CurrentRoster table now has fname, lname, in time, out time- with 24 staff
and an entire months worth of data...

Time entries are as such: (showing only part of the first day here)

In time Out time
2/1/2010 2/1/2010 8:00:00 AM
2/1/2010 1:00:00 AM 2/1/2010 9:00:00 AM
2/1/2010 5:00:00 AM 2/1/2010 9:00:00 AM
2/1/2010 7:00:00 AM 2/1/2010 3:15:00 PM
2/1/2010 7:00:00 AM 2/1/2010 2:45:00 PM
2/1/2010 8:15:00 AM 2/1/2010 4:15:00 PM (needs to counted as true for 8am)
2/1/2010 9:00:00 AM 2/1/2010 1:00:00 PM (8-830 is 8am- 831 true for 9am)
2/1/2010 9:30:00 AM 2/1/2010 7:00:00 PM
2/1/2010 12:00:00 PM 2/1/2010 8:00:00 PM
2/1/2010 12:00:00 PM 2/1/2010 2:30:00 PM
2/1/2010 12:00:00 PM 2/1/2010 8:00:00 PM
2/1/2010 2:00:00 PM 2/1/2010 10:00:00 PM
2/1/2010 2:00:00 PM 2/1/2010 10:00:00 PM
2/1/2010 2:30:00 PM 2/1/2010 8:30:00 PM
2/1/2010 3:45:00 PM 2/1/2010 11:00:00 PM
2/1/2010 4:15:00 PM 2/2/2010
2/1/2010 4:15:00 PM 2/2/2010
2/1/2010 5:00:00 PM 2/1/2010 9:45:00 PM
2/1/2010 8:30:00 PM 2/1/2010 10:30:00 PM

My report needs to show each hour of the day & number of staff on duty- as
such:

time # staff
2/1/2010 12am 1
1am 2
...
7am 5
8am 5
9am 4
10am 5
...
2/2/2010 ...


Thanks for your patience...

 

[Allen Browne]

The crosstab gives you a matrix:
- times on one axis
- staff on the other
- intersection point tells you whether the that person is on at that time.

-1 is the value in Access for True. (0 is false.)
Hence, if you get -1, the person is on at that time.
You may be able to format this field to show Yes instead of -1.
Or you can change the expression so it gives you a Yes.

That matrix is the simplest solution I can suggest for you.