Week of

[Guest]

I have a table that tracks the number of hours worked for a week. I have
several fields for Time (Worked, Vacation, Sick, School, etc) and I just need
the number of hours each employee had for each of these time fields for a
week. Our week is Sunday to Saturday. My question is how can I get the date
field to force people to use the date of the first Sunday of that work week
in my Week_of field? I would like it to make them select 1-7-07 or 1-14-07
or 1-21-07 or 1-28-07, ect. for my week of field instead of them selecting
any random date for the week.

 

[storrboy]

I have a table that tracks the number of hours worked for a week. I have
several fields for Time (Worked, Vacation, Sick, School, etc) and I just need
the number of hours each employee had for each of these time fields for a
week. Our week is Sunday to Saturday. My question is how can I get the date
field to force people to use the date of the first Sunday of that work week
in my Week_of field? I would like it to make them select 1-7-07 or 1-14-07
or 1-21-07 or 1-28-07, ect. for my week of field instead of them selecting
any random date for the week.

Don't allow them to make these changes in the table. Use a form.
After making the form the way you need it, identify where this check
will need to be done, likely in a BeforeUpdate event of a textbox.
Check to see if the day chosen is a Sunday by using the WeekDay()
function, and prevent the choice if it's not by canceling the event.

 

[Guest]

I agree with 'storrboy' that you need to restrict your users to doing data
entry with forms. However, I'd like to suggest an alternate method to
achieving this goal.

As a test case, create a form that is bound to a table that includes a
date/time data type. Add a combo box to this form and name it cboSundays. Set
the control source for this combo box to the field with the date/time values.
Add the following code behind the form, to produce a dropdown that includes
the past (6) Sundays. (You can adjust the value of the loop counter, i, to
however many past Sundays that you'd like to see in your combo box).

Option Compare Database
Option Explicit

Private Sub Form_Load()
On Error GoTo ProcError

Dim dteThisWeekSunday As Date
Dim dteLastWeekSunday As Date
Dim strRowSource As String
Dim i As Integer

dteThisWeekSunday = Date - (Weekday(Date) - 1)

For i = 1 To 6
dteLastWeekSunday = dteThisWeekSunday - 7 * i
strRowSource = strRowSource & dteLastWeekSunday & ";"
Next i

Me.cboSundays.RowSourceType = "Value List"
Me.cboSundays.RowSource = strRowSource

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Load..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

I'd suggest also covering this in the table definition as well as at form
level. Set the column's ValidationRule property to:

Weekday([Week_of],1)=1

Give it an appropriate message as its ValidationText. That will at least
ensure only a Sunday can be entered into the column via any means. You can
validate it to the Sunday of the current week at form level as explained by
the other respondents.

A more fundamental point, however, is that having separate columns for the
different time categories is not a good design. Its what's known in the
jargon as encoding data as column headings, whereas data in a relational
database should only be stored as values at column positions in rows in
tables. A better design would be to have 4 columns; EmployeeID, Week-of,
Hours and TimeCategory. Another table TimeCategories would have a single
column TimeCategory with values Worked, Vacation etc. This would be related
to the main time log table on the TimeCategory columns with referential
integrity enforced. In the data entry form the TimeCategory could then be
selected from a combo box with a RowSource:

SELECT TimeCategory
FROM Timecategories
ORDER BY TimeCategory;

You might want to make Worked its default value as that will presumably be
the value selected mostly.

For data entry you could have an unbound main form with a combo box from
which an employee can be selected and a combo box from which a Sunday date
can be selected, using the approach suggested by Tom (or you can easily
create a table of Sunday dates over a range of 4 or 5 years say by importing
a serially filled column from Excel as a table into Access and query this
table for the combo box's list). In this unbound form put a continuous view
subform bound to your time log table and set its LinkMasterFields property to
the names of the combo boxes in the main form, e.g. cboEmployee;cboWeek_of,
and its LinkChildFields property to the columns in the time log table,
EmpoyeeID;Week_of. You then simply select an employee and week-of from the
combo boxes in the main form and insert one row in the subform for each work
category fro the employee for that week. You would not need any controls in
the subform for the EmployeeID and Week-of as these values would be shown in
the main parent form and would be automatically inserted into the subform's
underlying table via the linking mechanism.

With the structure suggested above you'll find querying the database easier,
particularly for returning aggregated values, e.g. if you wanted to return
the hours over a year per employee for non-worked time:

SELECT [Enter Year:] AS [Work Year], EmployeeID,
SUM([Hours]) AS [Unworked Hours]
FROM [TimeLog]
WHERE [TimeCategory] <> "Worked"
AND YEAR([Week_of] = [Enter Year;]
GROUP BY EmployeeID;

Ken Sheridan
Stafford, England

 

[Guest]

Small typo in my post; it should have been:

AND YEAR([Week_of] = [Enter Year:]

I'd put a semi-colon in the WHERE clause, but a colon in the SELECT clause.

Ken Sheridan
Stafford, England

 

[Guest]

Doh! Not to mention the missing closing parenthesis:

AND YEAR([Week_of]) = [Enter Year:]

Ken Sheridan
Stafford, England

 

[Michael Gramelspacher]

(e-mail address removed)>, =?Utf-8?B?VG9tIFdpY2tlcmF0aA==?=

I agree with 'storrboy' that you need to restrict your users to doing data
entry with forms. However, I'd like to suggest an alternate method to
achieving this goal.

As a test case, create a form that is bound to a table that includes a
date/time data type. Add a combo box to this form and name it cboSundays. Set
the control source for this combo box to the field with the date/time values.
Add the following code behind the form, to produce a dropdown that includes
the past (6) Sundays. (You can adjust the value of the loop counter, i, to
however many past Sundays that you'd like to see in your combo box).

Option Compare Database
Option Explicit

Private Sub Form_Load()
On Error GoTo ProcError

Dim dteThisWeekSunday As Date
Dim dteLastWeekSunday As Date
Dim strRowSource As String
Dim i As Integer

dteThisWeekSunday = Date - (Weekday(Date) - 1)

For i = 1 To 6
dteLastWeekSunday = dteThisWeekSunday - 7 * i
strRowSource = strRowSource & dteLastWeekSunday & ";"
Next i

Me.cboSundays.RowSourceType = "Value List"
Me.cboSundays.RowSource = strRowSource

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Load..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

an alternative might be to have a calendar table with two
columns for calendar_date and weekday_nbr, then the combo box
source could be a query such as

SELECT Calendar.calendar_date
FROM Calendar
WHERE Calendar.weekday_nbr=1
AND Calendar.calendar_date BETWEEN DATEADD("ww",-8,Now()) And
Now();

you could play with the BETWEEN clause to get any range of
Sundays you wanted

 

[Jamie Collins]

having separate columns for the
different time categories is not a good design. Its what's known in the
jargon as encoding data as column headings, whereas data in a relational
database should only be stored as values at column positions in rows in
tables. A better design would be to have 4 columns; EmployeeID, Week-of,
Hours and TimeCategory. Another table TimeCategories would have a single
column TimeCategory with values Worked, Vacation etc. This would be related
to the main time log table on the TimeCategory columns with referential
integrity enforced.

I do not agree. Consider a simple, common sense rule such as the
number of hours in all categories for the week cannot exceed the total
number of hours in the week: how would you implement a constraint to
enforce the rule when the data is split across multiple rows?

[I assume your mention of "a relational database" is a misstatement
and you actually meant "SQL DBMS" or perhaps "Access".]

Jamie.

--

 

[Jamie Collins]

I do not agree. Consider a simple, common sense rule such as the
number of hours in all categories for the week cannot exceed the total
number of hours in the week: how would you implement a constraint to
enforce the rule when the data is split across multiple rows?

Although this is possible with a table-level CHECK constraint my point
is it makes things unnecessarily difficult. A close analogy would be
if you'd said, instead of first_name, middle_name and last_name on the
same row in the Persons table, the OP should have a three-row lookup
table of PersonNameTypes ('first', 'middle', 'last') and a
relationship table (a.k.a. junction table) of PersonsNames to include
complex (impractical?) to ensure a person cannot have more than one
name of type 'first', *must* have one of type 'last', etc.

Jamie.

--