A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lame Report Requirements



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2010, 02:48 AM posted to microsoft.public.access.tablesdbdesign
paradise95969 via AccessMonster.com
external usenet poster
 
Posts: 2
Default 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  
Old March 18th, 2010, 03:24 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old March 25th, 2010, 07:26 PM posted to microsoft.public.access.tablesdbdesign
paradise95969 via AccessMonster.com
external usenet poster
 
Posts: 2
Default 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  
Old March 26th, 2010, 04:53 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.