KeithWilliams
Registered User.
- Local time
- Today, 20:20
- Joined
- Feb 9, 2004
- Messages
- 137
Hi,
I am starting a new database. The database should allow users to set an attendance code for each hour of each day, for each member of a fire station's crew. The codes are = for available, blank for unavailable, and a range of other codes such as C for (Training) Course, A for Annual Leave etc.
The data for a day should be editable through a form that provides a grid, with a row for each person and a column for each hour of the day. Each cell in the grid should display the current code for the corresponding person and hour. It should be editable by selecting a new code from a picklist.
Another form must provide an overview, with a row for each person and a column for each day (probably for a 1 week period at a time). The same codes will be available AND EDITABLE for each entry in this second grid. A special code of * will denote a day for which more than one code is set in the person's hours for that day. The normal codes such as = and C, when set, will indicate that code is applicable for all 24 hours of the day. When a user selects a code at this overview level, all 24 hour codes for that day will be set to the same code behind the scenes. (The user cannot select the * code).
A number of possible designs occur to me, but I am wary of the likely issue of designing tables that will be non-updateable through one or both of the 2 forms I need to provide. For example:
AVAILABILITY
Person Id
Date
Day Code
Hour Code 0
Hour Code 1
Hour Code 2
:
Hour Code 23
The problem with this design is how to make the 2nd form updateable. I could use a crosstab query to lay out the grid, but a crosstab is not updateable. If I use a straightforward query to retrieve all the rows for a given date, they will be arranged vertically down the page. How would I get them to display in 7 columns (and still be editable)?
Your advice would be greatly appreciated.
Many thanks,
Keith.
I am starting a new database. The database should allow users to set an attendance code for each hour of each day, for each member of a fire station's crew. The codes are = for available, blank for unavailable, and a range of other codes such as C for (Training) Course, A for Annual Leave etc.
The data for a day should be editable through a form that provides a grid, with a row for each person and a column for each hour of the day. Each cell in the grid should display the current code for the corresponding person and hour. It should be editable by selecting a new code from a picklist.
Another form must provide an overview, with a row for each person and a column for each day (probably for a 1 week period at a time). The same codes will be available AND EDITABLE for each entry in this second grid. A special code of * will denote a day for which more than one code is set in the person's hours for that day. The normal codes such as = and C, when set, will indicate that code is applicable for all 24 hours of the day. When a user selects a code at this overview level, all 24 hour codes for that day will be set to the same code behind the scenes. (The user cannot select the * code).
A number of possible designs occur to me, but I am wary of the likely issue of designing tables that will be non-updateable through one or both of the 2 forms I need to provide. For example:
AVAILABILITY
Person Id
Date
Day Code
Hour Code 0
Hour Code 1
Hour Code 2
:
Hour Code 23
The problem with this design is how to make the 2nd form updateable. I could use a crosstab query to lay out the grid, but a crosstab is not updateable. If I use a straightforward query to retrieve all the rows for a given date, they will be arranged vertically down the page. How would I get them to display in 7 columns (and still be editable)?
Your advice would be greatly appreciated.
Many thanks,
Keith.
