HR Database - Help with structure

[muirchez]

HI, Be gentle with me....(Noob alert!)

Ok, I have recently inherited 2 spreadsheets which were being used to track Jobs/Positions in our company. (i.e. Job title, salary, hours of work etc) and also employees within the organisation.

The spreadsheets were linked with Vlookups linking by "PositionID" between the "Jobs" spreadsheet and the "employee details" spreadsheet. My task is to create a database to do this.

So far, I have created 2 main tables.
1. tblOrganisation - which holds all the position/job details
2. tblEmployees - which holds employee details (name, address etc)

The "PositionID" field unfortunately is in the format "99/1" (ie. it has to be stored as a text field (which I realise is far from ideal as a primary key)

I had set up "PositionID" as a primary key value in "tblOrganisation" and a foreign key "PositionID" in "tblEmployees".

Data entry to the 2 tables is done via 2 unbound forms. (frmAddNewStaff and frmAddtoOrganisation) (They are unbound forms because I did not want the user overwriting fields by mistake, so I have a command button locking/unlocking the records and a check if the form is dirtied asking the user "save yes/no?")

These work fine for entering data straight to the relevant tables.

My problem is that if I have an employee who is going to start in job "PositionID" 15/1 for example, I choose the "PositionID" 15/1 from a combo box on "frmAddNewStaff" and enter the employees details. When I save the record it is not linked to the "PositionID" in tblOrganisation.


I think the problem is with the unbound forms I am using to try to input details. I have attached the forms and tables in question and would really appreciate any advice.

Thanks in advance

PS - My description is probably as clear as mud, so ask if any clarification is needed

 

[speakers_86]

Sorry to tell you this, but I see a few problems with this.

1. tblOrganisation - which holds all the position/job details

I believe organization is spelled with a z. I actually looked it up to make sure, but dictionary.com has it both ways. Either way, make sure your object names are spelled correctly. Makes referencing easier!

tblOrganisation
1. I dont think the primary key is right. You should probably have the field "ID" set to primary. Also, use a more descriptive term than "ID".

2. If "PositionID" is a foreign key like it looks like it is, the data type should probably be number. This goes for the other fields that appear to be foreign keys as well.

tblPersonalDetails
1. Check your foreign keys to make sure they are numbers.
2. I HIGHLY recommend using an autonumber for your primary key. If there is some reason you have to have that weird 15/1 format, than create an auto number field as the primary not visible on any forms, remove the primary key from "PositionID" and change this field to required and must be unique.

frmAddNewStaff
1. It appears that the combo box is not set right. Just delete the control and use the combo wizard to make it over again.

Relationships
The reason the fields didnt "link" is because you dont have any relationships set! Or is that just because I'm looking at a condensed version of your db? I cant really know for sure.

You may want to study some faq's on the internet.

Hope that helps.