Table Structure

[Secret Squirrel]

I've been working on building an employee database for my company to track
all employee information, etc. I'm now at the point of creating an employee
review/evaluation section but I'm having a brain fart as to how to set it up.
The problem I'm having is that when a new employee is hired they get their
first review 3 months after they start. Then they have a 6 month review and
if they last that long then after that they will get an annual review based
on the month they were hired. The problem I'm having is I can't figure out if
I should create 3 separate tables, 1 for the 3 month reviews, 1 for the 6
month reviews, and then a final one for the annual reviews. On my main
employee form I want to have a field that will tell the uers when the next
review is due. I could use a simple "DateAdd" formula to do this but since
there is 3 different review terms then I really can't do it this way. How
would I tell if the 3 month review is done and then have it calculate 6
months from the hire date. And if the 3 month and 6 month is done then how do
I have the "DateAdd" tell it to add 1 year to the calculation? I'm confused.
Can anyone shed some ideas my way?

 

[Allen Browne]

Treat reviews like surveys:
1. Create a table of review types (i.e. 3 records if there are 3 different
types of review, with different questions.)

2. Create a table of questions (one record for each question you want to
ask.)

3. Create a ReviewQuestion table - a junction between the above two. Fields:
ReviewTypeID which review this question is asked in.
QuestionID which question is being asked in this review.
SortOrder number (the order of the questions in this review.)

4. Create a table to record when a review is conducted. Fields:
EmpReviewID AutoNumber (p.k.)
EmployeeID who was reviewed
ReviewDate when
ReviewTypeID what type of review.

5. Create a table to record the employees answers to the review:
EmpReviewAnsID AutoNum (p.k.)
EmpReviewID which review this answer belongs to.
AnswerText what answer the employee gave.

If there are multiple choice answers, you will need another table of
possible answers, related to #3 above, and another field in table #5 to
record the actual answer number given.

If you need a sample database of how this works, see this one by MVP Duane
Hookom:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news[email protected]...

 

[Secret Squirrel]

Hi Allen,

I follow you somewhat through step 4 but I'm a little unsure what step 2 &
step 5 will do since there won't be any questions asked. Step 4 will record
the date when the review is done and what type of review it is so from there
I can figure how to create a next review date based on the reviewtype. Is the
table of questions necessary if I'm just trying to tie a date to a review
type (ie 3 month, 6 month, annual)? Could I get away with just doing step 1
and adding a sort order to that table. And then create the table in step 4 to
store this information. I can then query from that table to have it give me
the next review date based on which reviews are done.

SS

 

[Allen Browne]

Ah: you just want to record that there was a review, not what happened in
the review. My reply is largely irrelevant.

So you want to record that:
- first review is due 3 months after HireDate;
- second review review is due 6 months after HireDate;
- subsequent reviews are due each year on the anniversary of HireDate.
So part of the issue here is that this is an open-ended sequence. While ever
the employee stays with you, they need a review each year.

One approach would be to use the AfterInsert event procedure of the form
where you enter staff to append (say) 62 records for this employee in the
EmployeeReview table: one for 3 months; one for 6-months; and enough for the
first 62 years of employement. There would not be many cases where someone
stays longer than 60 years (if your database itself lasts that long.)

Another possibility would be to use this technique:
Recurring events - how to handle recurring events that may never end
at:
http://allenbrowne.com/AppRecur.html
You would backdate the series to start from:
DateAdd("yyyy", -2, [HireDate])
and then set exceptions for the first 2 appointments (so they are
rescheduled to 3-months and 6-months after HireDate), and the series then
continues automatically from there. Seems like overkill though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Secret Squirrel]

Much clearer now! Thank you Allen. I will work on this today and see what I
come up with.

 

[Secret Squirrel]

Say I create a table called tblEmployeeReviews and within the table I have
these fields:

EmployeeReviewID - PK
EmpID
ReviewType
ReviewDate
ActualReviewDate

And then another table called tblReviewType which would be joined with the
above table based on the ReviewType.

From the AfterInsert can I have it only create the first 3 records in the
tblEmployeeReviews, 1 being the 3month review, the second would be the 6month
review, and then the 3rd would be the first annual review. And have it
populate the "ReviewDate" based on their HireDate using
DateAdd("m",3,[HireDate]) for the 3months, etc. Then going forward I can just
add them as I create new reviews. Then on my employee main form I can use a
continuous subform that will list all the reviews that are in that table for
that specific employee. Then as the reviews get completed I can just populate
the ActualReviewDate to show that it has been completed. And also on my
employee main form I can use the first record for that employee that does not
have a date in the ActualReviewDate field as the next review date scheduled.
I hope this all makes sense. So I would need to create these 3 records for
each employee after they are inserted into my tblEmployees. Writing SQL code
in vba is not my strong suit so I wouldn't really know how to append these 3
records and have it use the DateAdd calculation to create each one with the
correct increments. Can you maybe shed some light on how I would do this?

Ah: you just want to record that there was a review, not what happened in
the review. My reply is largely irrelevant.

So you want to record that:
- first review is due 3 months after HireDate;
- second review review is due 6 months after HireDate;
- subsequent reviews are due each year on the anniversary of HireDate.
So part of the issue here is that this is an open-ended sequence. While ever
the employee stays with you, they need a review each year.

One approach would be to use the AfterInsert event procedure of the form
where you enter staff to append (say) 62 records for this employee in the
EmployeeReview table: one for 3 months; one for 6-months; and enough for the
first 62 years of employement. There would not be many cases where someone
stays longer than 60 years (if your database itself lasts that long.)

Another possibility would be to use this technique:
Recurring events - how to handle recurring events that may never end
at:
http://allenbrowne.com/AppRecur.html
You would backdate the series to start from:
DateAdd("yyyy", -2, [HireDate])
and then set exceptions for the first 2 appointments (so they are
rescheduled to 3-months and 6-months after HireDate), and the series then
continues automatically from there. Seems like overkill though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I follow you somewhat through step 4 but I'm a little unsure what step 2 &
step 5 will do since there won't be any questions asked. Step 4 will
record
the date when the review is done and what type of review it is so from
there
I can figure how to create a next review date based on the reviewtype. Is
the
table of questions necessary if I'm just trying to tie a date to a review
type (ie 3 month, 6 month, annual)? Could I get away with just doing step
1
and adding a sort order to that table. And then create the table in step 4
to
store this information. I can then query from that table to have it give
me
the next review date based on which reviews are done.

 

[Allen Browne]

Makes sense.

You can mock up an append query in query design.
(Append is on the Query menu.)
Type some vaolues into the Field row, e.g.:
EmpID: 99
and in the next column:
ReviewDate: #1/1/2008#
Then switch to SQL View for an example of the SQL statement you need.

You can then massage it in VBA to concatenate the desired values into the
string.

The the basic approach to Execute the SQL string, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Say I create a table called tblEmployeeReviews and within the table I have
these fields:

EmployeeReviewID - PK
EmpID
ReviewType
ReviewDate
ActualReviewDate

And then another table called tblReviewType which would be joined with the
above table based on the ReviewType.

From the AfterInsert can I have it only create the first 3 records in the
tblEmployeeReviews, 1 being the 3month review, the second would be the
6month
review, and then the 3rd would be the first annual review. And have it
populate the "ReviewDate" based on their HireDate using
DateAdd("m",3,[HireDate]) for the 3months, etc. Then going forward I can
just
add them as I create new reviews. Then on my employee main form I can use
a
continuous subform that will list all the reviews that are in that table
for
that specific employee. Then as the reviews get completed I can just
populate
the ActualReviewDate to show that it has been completed. And also on my
employee main form I can use the first record for that employee that does
not
have a date in the ActualReviewDate field as the next review date
scheduled.
I hope this all makes sense. So I would need to create these 3 records for
each employee after they are inserted into my tblEmployees. Writing SQL
code
in vba is not my strong suit so I wouldn't really know how to append these
3
records and have it use the DateAdd calculation to create each one with
the
correct increments. Can you maybe shed some light on how I would do this?

Ah: you just want to record that there was a review, not what happened in
the review. My reply is largely irrelevant.

So you want to record that:
- first review is due 3 months after HireDate;
- second review review is due 6 months after HireDate;
- subsequent reviews are due each year on the anniversary of HireDate.
So part of the issue here is that this is an open-ended sequence. While
ever
the employee stays with you, they need a review each year.

One approach would be to use the AfterInsert event procedure of the form
where you enter staff to append (say) 62 records for this employee in the
EmployeeReview table: one for 3 months; one for 6-months; and enough for
the
first 62 years of employement. There would not be many cases where
someone
stays longer than 60 years (if your database itself lasts that long.)

Another possibility would be to use this technique:
Recurring events - how to handle recurring events that may never end
at:
http://allenbrowne.com/AppRecur.html
You would backdate the series to start from:
DateAdd("yyyy", -2, [HireDate])
and then set exceptions for the first 2 appointments (so they are
rescheduled to 3-months and 6-months after HireDate), and the series then
continues automatically from there. Seems like overkill though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I follow you somewhat through step 4 but I'm a little unsure what step
2 &
step 5 will do since there won't be any questions asked. Step 4 will
record
the date when the review is done and what type of review it is so from
there
I can figure how to create a next review date based on the reviewtype.
Is
the
table of questions necessary if I'm just trying to tie a date to a
review
type (ie 3 month, 6 month, annual)? Could I get away with just doing
step
1
and adding a sort order to that table. And then create the table in
step 4
to
store this information. I can then query from that table to have it
give
me
the next review date based on which reviews are done.

 

[Steve Sanford]

Here is my idea...

Using these tables

TblEmployee
E_ID Autonumber PK
E_LName Text
E_FName Text
E_Hire Date
E_Next ReviewDate Date


tblReview
R_ID Autonumber PK
E_ID Long FK to Employee
R_Result Text (OK, Needs Work , Failed)



When you create a new employee record, the next review is automatically
entered (via the after update event of the Date Hired control) as hire date +
3 months.

At three months, there is a review. A form is used to enter the results of
the review into the Review table. When the review form closes, some code
would (form close event?)

1) save the record
If Me.Dirty Then
Me.Dirty = False
End if

2) check the number of records in the review table for the employee using
ECount() (http://allenbrowne.com/ser-66.html) or opening a recordset.

RecCount = ECount("*", "tblReview", "R_ID= " & Me.E_ID)
Select Case RecCount
Case 1
' 3 month Review complete. Calc 6 month review date
NxtReviewDate = Forms!frmEmployee.NextReviewDate
' 6 months from hire date
NxtReviewDate = DateAdd("m", 3, NxtReviewDate)

Forms!frmEmployee.NextReviewDate = NxtReviewDate

Case 2
'2 records, 3 & 6 month reviews completed
' add 6 months to get annual review date
' annual from hire date
NxtReviewDate = Forms!frmEmployee.NextReviewDate
NxtReviewDate = DateAdd("m", 6, NxtReviewDate)

Case Else
' annual from hire date
NxtReviewDate = Forms!frmEmployee.NextReviewDate
NxtReviewDate = DateAdd("yyyy", 1, NxtReviewDate)

End Select

The main employee form would have to remain open, but it could be hidded.

Or , when the Review form closes, you could open a recordset on the employee
table to get the Next review date, open a recordset on the review table to
count the records, do the math, then update the employee table with the next
review date. The main employee form would not have to be open.


HTH

 

[Secret Squirrel]

I like your approach. But what if there are no records in the review table?
Say they are a new hire, how do I get it to create the first record so the
first review date will be 3 months from the hire date?

 

[Steve Sanford]

When you create a new employee record, the next review is automatically
The 3 month review date gets entered when the employee record is created.
(see above)

Additional code would be required to keep from changing the review date if
the hire date is edited for some reason if they are past the 3 month review
date.

Something like this:
'-------------------------------
Private Sub HireDate_AfterUpdate()
If IsNull(Me.ReviewDate) Then
Me.ReviewDate = DateAdd("m", 3, Me.HireDate)
End If
End Sub
'-------------------------------


Or it could be in the form before update event:

'-------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.ReviewDate) Then
Me.ReviewDate = DateAdd("m", 3, Me.HireDate)
End If
End Sub
'-------------------------------

HTH

 

[Secret Squirrel]

I think I understand. You're using the Case Select (your #2 in your previous
post) in the after update event of the Date hired to create the new record,
correct?

Also, the additional code is going to make sure if the hire date is changed
that the 3month review date is also changed. Right?

 

[Steve Sanford]

OK, lets take a step back to an overall view. Remember, I don't know your
table structure, form layout or your process.

Lets say it looks something like this:

The one ----------------> The many
tblEmployee tblReview (or whatever you call them)


When a new employee is hired, you *know* when the 3 month review will
happen. So when you create an entry in the database for the new employee, you
can calc the 3 month review date and enter it into a table.

Something like this:
'-------------------------------
Private Sub HireDate_AfterUpdate()
If IsNull(Me.ReviewDate) Then
Me.ReviewDate = DateAdd("m", 3, Me.HireDate)
End If
End Sub
'-------------------------------



Lets call the form you used to enter the employee info "frmEmp_Info".

So three month roll by and the review is completed. Its time to enter the
results of the review.

You open "frmEmp_Info". There is a button on the form named "btnAddReview"
which opens another form named "frmAddReview". ("frmEmp_Info" is still open
and has the control "NextReviewDate" (or whatever you call it) on the form
(which is bound to the field for the next review date).

You enter the required info about the review, then click a Close/Save/Update
(pick one) button. The on close event fires and runs this code to update the
next review date field:

'code snippet for form close event
'-----------------------------------
If Me.Dirty Then
Me.Dirty = False
End if

RecCount = ECount("*", "tblReview", "R_ID= " & Me.E_ID)
Select Case RecCount
Case 1
' 3 month Review complete. Calc 6 month review date
NxtReviewDate = Forms!frmEmployee.NextReviewDate
' 6 months from hire date
NxtReviewDate = DateAdd("m", 3, NxtReviewDate)

Forms!frmEmployee.NextReviewDate = NxtReviewDate

Case 2
'2 records, 3 & 6 month reviews completed
' add 6 months to get annual review date
' annual from hire date
NxtReviewDate = Forms!frmEmployee.NextReviewDate
NxtReviewDate = DateAdd("m", 6, NxtReviewDate)

Case Else
' annual from hire date
NxtReviewDate = Forms!frmEmployee.NextReviewDate
NxtReviewDate = DateAdd("yyyy", 1, NxtReviewDate)

End Select

'-----------------------------------


The Add review form closes and youare back at the employee details form.

Remember, this is untested.

Does this help??


HTH