Ivo and Jap
|
| Posted: 05/22/2002, 6:29 AM |
|
Hello,
We are working on a project for school and we have the following problem:
We have 3 tables:
1. Calender --> This table contains all days and dates of the year and a primary key
IE:
1 Monday 20-5-2002
2 Tuesday 21-5-2002
and so on...
2. Availability --> This table contains employee_nr, day and a primarykey
IE:
1 501 Monday
2 501 Friday
3 502 Friday
(501 and 502 are employee numbers)
3. Unavailable --> This table contains employee_nr, date and a primary key
IE:
1 501 25-5-2002
2 502 30-5-2002
Ok, this is what we want to do:
We want to search for a date and we want to see which person can work on that day.
IE:
If we enter 25-5-2002 (which is a friday), we want to get employee 502 as a result because he is available on friday (table 2) and he is not unavailable on this date (table 3).
Can anyone please help us how to make this query???
There's probably a more easy way to make a timeplanner, but we don't have enough knowledge, and we don't know any other way...
Thanks in advance!
|
|
|
|
Ken Hardwick
|
| Posted: 05/22/2002, 7:08 AM |
|
You can either create a query or write a custom SQL..I would suggest using
a query...
In the query...
Link the table calendar with availablity
and Link the table calendar with Unavailabity (be sure to do a left outer link)
and
select the fields you need
and then use this resulting query in your code charge grid.
If you need addition assitance in doing this, you can send me an email at
ken@kenhardwick.com
|
|
|
|
DaveRexel
|
| Posted: 05/22/2002, 3:32 PM |
|
IMHO the 3rd table is not needed,
According to the desired logic the SQL statement only has to match selected day to available employees.
An easy way to make a time planner is to show the times as images and then control the width of image by the number of times days are in sequence. This would make a good month/year view.
I would suggest normal non-image table for day/week view and drawing up occupied days as different coloured cells.
This should be fairly easy to whip up in CC.
The code produced is fairly easy to follow.
Good luck, what are you studying?
Dave
|
|
|
|
|
|
