Need design suggestions

[jtriggs1941]

Hi, I hope someone can suggest the best way to design my database. I've tried
it a couple of way but am not having much luck.

I am looking to keep personnel data in a database that will change from time
to time (promotion, etc). My problem is I want to only display the most
current info for each employee but keep the original data as well. For
example, Joe is hired 1/1/07, gets promoted 7/1/07 and then transfers to
another office on 9/1/07. I need to keep all of that info but only want to
display the most current info in my spreadsheet of employees. Is there a way
I can set something up so that as I input the changes that the original data
goes somewhere else for "historical" purposes?

I am using Access 2003, if that matters.

Thanks!
Terri

 

[Vadimbar]

I don't post many answers here but I will give it a try. You should start
with a name table that has unique auto number. Your other table should be
linked by that number each time you want to add additional or updated
information a new record in table 2 would be created that would receive the
persons unique number and then you would enter all pertinent new data
hopefully you capture the date as well.

Your form can bring up a persons name and a sub form can be introduced that
will show the most recent rec for that person. By modifying view properties
of the subform you can set it to show only the most current data.

Once the tables have been created you can use the wizard to link the table
with the unique number.

Hope this helps?
VADIMBAR

 

[Jeff Boyce]

As described else-thread, you probably don't want to have the historical
date "go somewhere else". This would only make it harder to look up, and it
isn't necessary, since you can use queries to return only those rows you
wish to see.

It sounds like, as a first step, you have people and you have employee info.
Sounds like two tables.

By the way, you do NOT need to store data in tables in a way that mimics how
that data will be displayed (form, report). In fact, you do NOT want to do
it that way if you want to get easy use of Access' relationally-oriented
features/functions.

First get your data (entities & relationships) figured out. Then use
queries to help gather/format data for display in forms and reports.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Evan Keel]

Consider the following table:

Employees(EmployeeNumber (PK), VersionNumber (PK), EmployeeLastName,
DepartmentNumer)

PK= Primary Key

Instead of VersionNumber you could something like ChangedDate.

The following query would get you the latest Employee row"

SELECT *
FROM Employees e1
where e1.VersionNumber = (
select max(VersionNumber)
from Employees e2
where e2.EmployeeNumber = e1.EmployeeNumber)

This should get you started in the right direction. And I agree with
Jeff--get your design right. You are working with a database, not a
spreadsheet.

Good Luck!
Evan

 

[Evi]

Once you have your design right, you could include a Yes/No tick field which
you tick when the last record for that employee is no longer current and you
want to add a new recordYour reports and forms could be based on queries
where this tick field is filtered as True.

If you need to put data into a spreadsheet (and I know that some people are
really insistent about using them), you can do that with one of these
filtered queries. Simply copy and paste it into a spreadsheet.

You would want more than 1 table but not so that you can seperate old data -
that will be done in your queries


TblEmployee
EmpID (autonumber, Primary Key)
FirstName
Surname
other stuff about the employee (but not his work history)

TblPosition
PosID (PK Autonumber)
EmpPosition (contains a list of all the positions any employee could obtain
from Cleaner down to Managing Director, you would include items like
Retired, Sacked, Left)
Any fields about that position which could apply to anyone working there

TblDept
DepID (PK autonumber)
Department (contains a list of all the departments an employee could work in
including None for the record when he leaves the firm)
Any fields about that department


TblEmpHistory
EmpHisID (autonumber, Primary Key)
EmpID (Foreign Key number field linked from TblEmployee
ChangeDate (date field - a better design than using a version number - the
earliest ChangeDate will be the date when the employee started at the firm)
PosID (Linked from TblPosition)
DepID (Linked From tblPosition)
Archv( a Yes/No field which you will tick for the current record whenever
you add a new record for that employee denoting a change)
HistoryNotes (to record any extra details about a specific change

You will probably think of other tables which you will need, once you get
the idea

Evi