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/Ap****ueBadWord.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"paradise95969 via AccessMonster.com" u57765@uwe wrote in message
news:a52b75260f88f@uwe...
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...
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201003/1