Week View Calendar 2

[bowserj]

OK, i inherited a project and need to build a form to display a week view of scheduling information. I have a table of scheduled events in the following format:

ScheduleID EmployeeID ClientID StartTime EndTime

I need the form to display a week view with the days of the week accross the top, and the scheduled events under each day. I am usually pretty good at these kinds of things but am drawing a blank on this one for some reason. Can anyone point me in the right direction? I don't need to edit the data in this form as I will use the onclick event to open another form to enter the data as necessary.

Thanks in advance for any help

 

[maxhugen]

Sounds like you need a CrossTab query?

Max Hugen
Australia

 

[bowserj]

Thanks for the quick response, but I looked into a CrossTab Query and don't see how it will do what I need to do. Perhaps I should explain better. The final result that I need will be something like this.

Sun Mon Tues Wed Thurs ...
8:00-4:00 7:00-3:00 8:00-4:00 8:00-4:00
J. Bowser T. Jones J. Bowser J. Bowser

9:00-5:00 8:00-4:00
B. Smith B. Smith

Where 8:00-4:00
J. Bowser
is a single "record" which the user could then click on to bring up the scheduling form to edit the complete record. The employee name is not in the table, so I would either have to reference it somehow from the stored EmployeeID or pull the data together in a query first and work with it from there.
This is something that is so basic in concept to me, but I cannot seem to bring it together in code at the moment.

 

[maxhugen]

I'm assuming that StartTime and EndTime are date fields?

Max Hugen
Australia

 

[maxhugen]

Try this in a query (pls change the table name though!):

TRANSFORM First(Format([StartTime],"h:nna/p") & "-" & Format([EndTime],"h:nna/p")) AS TimeRange
SELECT ScheduleID
FROM [blue]scheduledEvents[/blue]
GROUP BY scheduleID
PIVOT Format([StartTime],"ddd");

To add the employee name, add the table to this query, and concatenate the employee name into the calculated field 'TimeRange'.

Max Hugen
Australia

 

[maxhugen]

To ensure all days display, change last line to:

PIVOT Format([StartTime],"ddd") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

Max Hugen
Australia

 

[bowserj]

Thanks for all your help, and that is super close to what I am looking for but when I present it to the user, I will want to have multiple items on the same line:

Sun Mon Tues Wed Thurs ...
8:00-4:00 7:00-3:00 8:00-4:00 8:00-4:00
J. Bowser T. Jones J. Bowser J. Bowser

9:00-5:00 8:00-4:00
B. Smith B. Smith


as opposed to having 6 blank txt boxes and one scheduled date on each line. I am thinking that the easiest way to do this is to modify the code to only pull one day at a time then just line up 7 subforms next to each other on the main form....

Any better suggestions?

 

[maxhugen]

I can't think of a better solution than 7 subforms either... :-(

Max Hugen
Australia

 

[maxhugen]

FWIW, you'll want reports as well, no doubt? Duane Hookom has a useful 'Calendar Reports sample' mdb that you might be able to utilise, at

http://www.access.hookom.net/Samples.htm

Max Hugen
Australia

 

[MajP]

Actually I have done very similar to this with a bound form and a single table. I make a table called tblWeek. This is a non-normalized table, but you only use it for output purposes. You read through the events table and populate this table. Each column is a field (day) and each record is a line of output.

tblWeek
strEventMonday
strEventTuesday
...
strEventSunday

When I open the form and select a specific week, I read through the scheduled events table and push the relevant events into the corresponding fields of tblWeek. Using a tabular form and this table you will have your format. If you choose a different week to show then you repeat the steps:
1) Clear the old data (delete query)
2) Read through the events table using dao or ado selecting the relevant events. (May want to read through seven seperate recordsets returning only events for each day)
3) place relevent events into tblweek

 

[bowserj]

Thank you for your help Max, I ended up getting it to work perfectly with 7 seperate subforms.