Keep track of current Job Description

[Design by Sue]

The database I have keeps track of employee training based on their job description. An employee can have more than one job description (they start in one position but are promoted to the next - the previous job description remains). I need to keep track of the current job description. Currently I have a number of tables in the database but the two that I believe are involved here are the Employee Table that keeps track of the employee number, first and last names and the Job Description Table that has the employee number in one column and the job description in the 2nd column. What are suggestions do you have to help me indicate which of the existing descriptions are the current one. I would like to be able to have the user select "current" on my form when they are linking the employee to the description and if there is a previous description as current, what ever the user does, "turns off" the current in the old description and turns it on in the new.

I was thinking of a column called Current and using Yes/No. If this solution works how would I prevent more than one yes per employee and how would I change it as described above.

Appreciate any input!

Sue

 

[Beetle]

Your idea is correct. I would just add a Yes/No field to the table and then use code in the form to set all other previous job description rows for that employee to false. Code would look something like this (air code);

Dim strSQL As String

If Me.YourCheckBox = True Then
    strSQL = "Update tblJobDescriptions Set CurrentJob = False " _
              & " Where EmpID = " & Me.EmpID & " And JobDesc <> [COLOR="red"]""[/COLOR]" & Me.JobDesc [COLOR="Red"]& """"[/COLOR]
    CurrentDb.Execute strSQL, dbFailOnError
End If

You'll need to modify this with your actual table/field/control names. If JobDesc is a text field then the values in red above (the extra quotes and ampersand) need to stay. If it is a numeric field then remove them. I'm assuming EmpID is numeric. This code would go in the Before_Update event of your form. If you're going to use this, test it on a backup copy of your db of course.

 

[Design by Sue]

Thank you for such a complete response - I won't be able to work on this now til Monday. I will test this and let you know.

Thanks a million
Sue

 

[pbaldy]

I personally wouldn't have a "current" field, I'd have a date field with the effective date. The most recent dated record is the current one, and you have a better history. This would be one way to get the current jobs:

http://www.baldyweb.com/LastValue.htm

 

[Design by Sue]

Beetle - thank you - that worked perfectly.

pbaldy - The date was another thought I had but in this case is actually not necessary - and besides the client would have difficulty finding the correct information, therefore making it useless. But I appreciate the input because in another situation that would be the answer.

Sue