AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Question Relationship screen of the current database
Example of my current relationship
One Clocked In to Many Days Worked,
Many Days Worked to One Employee Payment Details
One Employee Payment Details to One Company's Personal Information
Many Days Worked to One Overhead
One Product to Many Product Sold
Table description:
In the Clock In Table This displays the time the employee clocks in to work.
In the Overhead table this displays the cost the company must pay including employees wages.
In the Employee Payment Details this displays bank account number, national insurance, sort code etc.
In the Company's Personal Information this displays the employees address, start date date, hourly rate, dob, contracted hours
In the table Days Work this will display the time employees worked during the week.
In the Products table it will display all the products in the store, product availablle, price etc.
In the Product Sold it will display Product id, product name, Product sold, money taken so far on the day.
How would you link up these tables together as a relational database.
Primary Keys of the current database
In the "Clock In" Table primary keys are:
Employee Id*
In the "Product Sold" Table primary keys are:
Product Sold Id*
Product Id*
In the "Days Worked" Table primary keys are:
Days Worked Id*
Empoloyee Id*
In the "Overhead Table" primary Keys are:
Overhead Id*
In the "Company's Personal Information" primary keys are:
Employee Id*
In "Employee Payment Details" primary keys are:
Employee Id*
In the "Products" table the primary keys are:
Product id*
Please help me if you need to remove or merge tables do so please so i can link my tables together.
Answer Let me first explain primary and foreign keys. A primary key is a unique identifier for a record. That is the sole purpose of a primary key. That's why using an autonumber as a primary keyt is recommended. A Foreign key is a field in a related table that holds the value of the primary key for the related record. You link tables by joining primary key to foreign key.
Therefore the ClockIn table should have EmployeeID as a foreign key (not primary key) to identify a clock in record for an employee. Use an Autonumber to add a ClockInID as PK.
Also you should NOT have multiple primary keys. If you need to set another field so that there are no duplications you can add a unique index, but there should be only one primary key.
Some more specifics.
In Personal Info and Payment Details, you should have an autonumber as PK and employeeID as FK.
In Product Sold, ProductID is a FK, not a PK
In Days worked EmployeeID is an FK not PK
Also its not as good idea to use spaces in field or object names.
