Creating Problem

[interlekt]

Its been a long time since I created a database using access however I have been asked to do one for a small business and need it to contain the following information:

Employee Details
EmployeeID
Name
Address
City
PostCode
NINumber
Nationality
PayrollNumber

Emergency Contact
EmployeeID
Name
Address
Tel
PostCode

EmployeeOther
EmployeeID
ProofofID
PassedTest
SafetyShoes

Does this seem correct information to begin creating the tables and forms? as I am also unsure of the relationships between the data I need to create.

Please Can someone help?

 

[ajetrumpet]

all the tables really aren't necessary, because they're all about the employee. one table wold do just fine here.

possibly, this might help your quest: http://www.access-programmers.co.uk/forums/showthread.php?t=156032&highlight=sample

 

[interlekt]

all the tables really aren't necessary, because they're all about the employee. one table wold do just fine here.

Ok, I forgot to add bank details into there as well, so you recommend just having one table for everything?

 

[ajetrumpet]

Ok, I forgot to add bank details into there as well, so you recommend just having one table for everything?

why don't you post ALL the info your gonna put into the database and i'll help you from there? most likely you want relationships. but as i've said MANY A TIME, give all info necessary so we dont step backwards trying to help ya out.

 

[stopher]

Ok, I forgot to add bank details into there as well, so you recommend just having one table for everything?

You only need separate when there is a one to many relationship. Consider Emergency Contact... If you only ever record at most one contact then this can be in the Employee Details table - no need for a separate table. However, if you need to store two or more emergency contacts for any given employee then you can't sensibly do this in one table... you need to put the contacts in their own table and identify a relationship.

The key is to understand what kind of relationship exists because that largely dictates your table design i.e. one-to-one or one-to-many, (and also sometimes many-to-many)

Read up on database normalisation (normalization).

hth
Chris

 

[Galaxiom]

There are many variants in contact database. Here are a few to give you an idea.

Often two fields are provided for address. It makes addressing letters easier. For example:

Bill Smith
The Big Building
23 Some Street
Sydney 2000

Sometimes a table of postcodes is used meaning you only need to record the city in the employee table and the matching postcode is looked up in the table. Generally this is used for big customer mailorder database were deliveries rely on correct postcode information. Technically having both city and postcode is in breach of normalisation but this is often tolerated by all but the purists.

Sometimes a table of nationalities will be used and a NationalityID recorded in the employee table. It prevents the two guys from Kyrgyzstan appearing to come from different countries because somebody couldn't spell it correctly. In a small business it is probably OTT.

Often the Emergency Contact will be placed in another table as you have done. This has an advantage in that one employee can be given any number of contacts. Consider where an employee is another employee's contact. Workplaces usually require a contact outside the business if the primary contact is also an employee in case both are injuured. You would then definitely need multiple contact facilities but it is often done with extra field rather than a table. I prefer the separate table approach.

The multiple contacts would need a sequence number or some way to indicate who is the primary contact. (Otherwise the wife is not going to be impressed when they contact his sister first with the bad news.)

Sometimes all people including the employee and their Emergency contacts will be recorded in one table with a reference from the contact record to the employeeID. A field will record them as employee or contact.
The information specific to the employee but not applicable to the contact will be in another table.

 

[georgedwilkinson]

You shouldn't use the arrangement you're suggesting. Read this: http://www.tdan.com/view-articles/5014

 

[wazz]

and don't forget about the Access templates that are available.

and please don't post the same question more than once.