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 -------------------------
Followup To
Question -
I have implemented the new fields into the database also implementing new tables, now how would you link the new tables together? what relationship would you use between tables?
-------------------------
Followup To
Question -
Relationship screen of the current database
i HAVE APPLIED EVERYTHING YOU HAVE TOLD ME INTO THE DATABASE FIELDS AND THIS IS THE DETAILED TABLE INFORMATION, WITH THE FOREIGN KEYS AND PRIMARY KEYS. PLEASE COULD YOU SHOW ME HOW TO LINK THE 7 TABLES TOGETHER SHOWING WHAT RELATIONSHIPS TO USE A.S.A.P. THANK YOU, YOUR ADVISE WAS EXCELLENT.
Clock in table:
CLockINID as the primary key.
• Fields are: Employee id (This field will store an 8 digit number that will be assigned to every employee that will make each employee's record unique). Data from this table can be retrieved from the company's personal information table. This is now the foreign key
• Clock in (This field will contain a short time format that will display the time the employee has clocked in.)
• Clocked out (This field will contain a short time format that will display the time the employee has clocked out.)
• Out to Lunch (This field will contain a short time format that will display the time the employee has been out to Lunch.)
• Back from Lunch (This field will contain a short time format that will display the time the employee is Back from Lunch.)
Days Worked table:
DaysworkedID as the foreign key.
• Fields are: employee id, (This field will store an 8 digit number that will be assigned to every employee that will make each employee's record unique).AS the primary key
• Sunday, (This field will hold the period of time the employee will work on this specific day for example 9-6).
• Monday, (This field will hold the period of time the employee will work on this specific day for example 9-6).
• Tuesday, (This field will hold the period of time the employee will work on this specific day for example 9-6).
• Wednesday, (This field will hold the period of time the employee will work on this specific day for example 9-6)
• Thursday, (This field will hold the period of time the employee will work on this specific day for example 9-6)
• Friday, (This field will hold the period of time the employee will work on this specific day for example 9-6)
• Saturday, (This field will hold the period of time the employee will work on this specific day for example 9-6)
Payment table:
• Fields are Employee id (This field will store an 8 digit number that will be assigned to every employee that will make each employee's record unique). Data from this table can be retrieved from the company's personal information table. AS THE FOREIGN KEY.
• Bank account number (This field will contain 8 integer that will not be able to take no text data.)
• Name of bank (This field contain the name of the bank which the employee's wages is to be paid into.)
• Sort code (This field will contain numbers of the bank account that will be displayed like so 20-89-90.)
• National insurance number (This field will display a variety of text and integers. The format will be two letters followed be five numbers followed by two letters
Companies personal information table:
• Fields are: First name, (This field will contain the name of the employee that will have a validation of text data type and a field size of 25.)
COMPANY'S PERSONAL INFORMATIONID AS THE PRIMARY KEY.
• Last name, (This field will contain the name of the employee that will have a validation of text data type and a field size of 25.)
• Employee id, (This field will store an 8 digit number that will be assigned to every employee that will make each employee's record unique). Data from this table can be retrieved from the company's personal information table. AS THE FOREIGN KEY
• Start date, (This field will have a format of short date which will display the date the employee started.)
• Pay per hour, (This field will have a currency format that will display how much the employee is being paid per hour,
• Contracted hours, (This field will display how many hours the user is contracted with the company displaying the value in integer.) in reference to the analysis stage I have made a change to delete the field number of hours worked from this table because it may cause data redundancy.
• Address1, (This field will display the first line of the user home address.)
• Address2, (This field will display the second line of the user home address.)
• Address3, (This field will display the third line of the user home address.)
• Postcode, (This field is validated to be two letters followed by two numbers followed by two letters.
• DOB, (This field will the birthday of every employee in the format of short date.)
• Telephone number, (This field will contain 11 numbers from the employee. The field will need to be formatted to >00000000000 to avoid invalid data in the system.)
Overheads table:
OVERHEADID AS PRIMARY KEY ID
PRODUCT SOLD ID AS FOREIGN KEY
• Fields are: Electricity cost (This fields will display the British pound sign where the fields will be formatted to currency data type.)
• Water cost (This fields will display the British pound sign where the fields will be formatted to currency data type.)
• Telephone cost (This fields will display the British pound sign where the fields will be formatted to currency data type.)
• Over head id (This field will be the primary key of this table displaying the identification of this field).
Product table:
• Field are: Product id, (This field will contain 6 numbers to be stored into the system) AS PRIMARY KEY
• Product name, (This field will store the name of the product using a text integer type)
• Product price (which must be included into the analysis of the system. Also this field will be displayed as a currency data type that will display the British currency)
• Number of products available (This field will display the number of products available in the system.)
Product sold during the day table:
• Field are: Product id (This field will contain 6 numbers to be stored into the system)AS PRIMARY KEY AND PRODUCTSOLDID AS FOREIGN KEY.
• Product name(This field will store the name of the product using a text integer type)
• No of products available (This field will display the number of products available in the system.)
• Payment (This field will be formatted to have yes/no field that the user will only be able to select one data or the other.)
• Sold (This field will display the number of products sold during the day.
Answer -
In the future, please use the followup option instead of a new note so I can see what I wrote. I answer lots of questions daily and can't remember each one.
I don't think you followed some of my advice exactly, because I would do things this way. Here's how I would do it
Looks like the payment table is used to contain Direct Deposit info. If so, then there is no need for a separate table for this, it should go in the Employee table.
tblEmployees
EmployeeID (PK)
LastName
FirstName
MI
StreetAddress
City
Region (State, county, Province, etc.)
PostalCode
DOB
PhoneNumber
Now I would add an employment history table like so:
tblEmpHistory
EmpHistoryID (PK)
EmployeeID (FK)
HistoryDate
HistoryType (Start, Transfer, Raise, Promotion Termination, etc.)
PayRate
ContractedHours
You seemed to have mixed up what should be primary and foreign keys on several tables. A lot of your table structure was not normalised properly. I also don't understand the Payment field you had in your products Sold table. Unless you are recording specific orders, then it doesn't make any sense.
HTH
Scott<>
Answer Mike,
The answer is already there. Tables are related using the primary and foreign keys. So wherever you have a Foreign Key you have a relation to the table where that field is the primary key.
Basically you have tblEmployees as a hub relating to tblClockInOut, tblDaysWorked and tblEmpHistory. You have tblProducts relating tblProductActivity and you have tblOverhead standing alone.
