Normalization of tables

[djossh]

Hi Im new to Access and Im trying to create a database of employees with their corresponding details and informations. but I am confused on how to link each table.. I attached the file for your reference, please help me Normalized my table and how to create PK & FK ..thanks...

 

[r.harrison]

Hi,

I've looked at the tables and have a few suggestions:

The Employee table looks fine, but some of the others are a little off.

Taking the Positions table. You would only need about 3 fields:

PositionID, Position, EmployeeID

You can then link the employee# and employeeID fields. A similar approach to the projects table would also work.

Salary and Passport look fine and just need linking to the employees table by employee#

Benefits just needs a employee# field to link to.

Hope this helps.

 

[djossh]

Hi,

I've looked at the tables and have a few suggestions:

The Employee table looks fine, but some of the others are a little off.

Taking the Positions table. You would only need about 3 fields:

PositionID, Position, EmployeeID

You can then link the employee# and employeeID fields. A similar approach to the projects table would also work.

Salary and Passport look fine and just need linking to the employees table by employee#

Benefits just needs a employee# field to link to.

Hope this helps.

THANK YOU! can you check my new tables / Relationship... I don't really know if im doing it right... thanks again. (See attached file)

 

[jdraw]

The tables and relationships seem fine, but what will they be used for? Will this structure support your business needs? Do you have some business facts to test the model?

I would recommend NOT using special character in field names (#).

 

[Alansidman]

Further to JDraw's comment about special characters.

This link is for AC2000, but there are newer versions out there. Scroll to the bottom of the list for special characters that should not be used in Access.

http://support.microsoft.com/kb/209187

Alan

 

[djossh]

The tables and relationships seem fine, but what will they be used for? Will this structure support your business needs? Do you have some business facts to test the model?

I would recommend NOT using special character in field names (#).

Thanks for that info. we don't have any business structure right now, im tired of using excel and I really wanted to learn MS Access. I do have one problem now...

I have Table with One to Many relations to another table... my question is, evertime i enter a data, i will have this error "The value you entered does not match the Number data type in this column." this is because i need to enter the ID number..... ex. 1...2...3...etc. how can i have a dropdown list of the data i want to enter instead of ID#..

ex.. (Table)

ID NameField
1 Joshua
2 Roger
3 Michael
4 Vincent

instead of entering ID#. i want to have a list (Dropdown of the "Namefield').. Im really new in Access... any help is Highly appreciated..

 

[jdraw]

Show us the tables involved and how the fields are defined.
If the Id field is an identifier, why not define it as autonumber?

 

[bladedfeather]

I'm a newbie here and also new to Access programming.I hope somebody can help me to normalize my table.. thanks..

 

[djossh]

I'm a newbie here and also new to Access programming.I hope somebody can help me to normalize my table.. thanks..

Hi please see attached file for your reference.. you need to Create 4 tables based on your sample fields. Hope this would help.

 

[bladedfeather]

Hi please see attached file for your reference.. you need to Create 4 tables based on your sample fields. Hope this would help.

thank you djossh. i will try to create relationship for the table you gave me.

 

[bladedfeather]

i have five tables and i wanted to extract data out of this five table. the data that i wanted to extract is being shown below on the excell file that i will attached here. do i have to add 1 more table to create a relationship? hope somebody could help me..thanks..

 

[djossh]

i have five tables and i wanted to extract data out of this five table. the data that i wanted to extract is being shown below on the excell file that i will attached here. do i have to add 1 more table to create a relationship? hope somebody could help me..thanks..

To extract that data you need a Query.. then from Query.. you can generate the report as per your format. By the way.. you need to have a Foreign key in your Relationship

 

[bladedfeather]

To extract that data you need a Query.. then from Query.. you can generate the report as per your format. By the way.. you need to have a Foreign key in your Relationship

djossh you told me to have a foreign key so i add 1 more table so i can create a relationship. i attached again a file. if i will create a form from the last table i created how can i enter data for example on the DepartmentID that i will not enter the value 2 which correspond for example the department INTERNAL MEDICINE but will give me the value in the drop down list. thanks djossh.. i hope i explain it clearly..

 

[djossh]

djossh you told me to have a foreign key so i add 1 more table so i can create a relationship. i attached again a file. if i will create a form from the last table i created how can i enter data for example on the DepartmentID that i will not enter the value 2 which correspond for example the department INTERNAL MEDICINE but will give me the value in the drop down list. thanks djossh.. i hope i explain it clearly..

Check this Link.. it may help you..

http://www.access-programmers.co.uk/forums/showthread.php?t=211928