If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lame Report Requirements
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 |
#2
|
|||
|
|||
Lame Report Requirements
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 |
#3
|
|||
|
|||
Lame Report Requirements
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... -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Lame Report Requirements
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. -- 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:a58c2e88096d1@uwe... 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... -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|