How many Tables to create for data

[cpill]

I am trying to figure out what data to put with what tables and how many tables. I have the contact information but I can't figure out what to do with hourly wage, the days and hours they worked each week (5 days each week for two weeks and 9 or 3 hours a day)



What I think it is and the relationships.

Contact Information
ID (PK)
Last Name
First Name
Address

Employee Wage
ID
Hourly Wage (PK)
Days worked (2 weeks)

Actual work per person table
ID (PK)
Monday
Tuesday
Wednesday
Thursday
Friday

[jzwp11]

Welcome to the forum!

First, I would use more descriptive field names in place of the "ID" field in the tables. Additionally, it is best not to have spaces or special characters in your table or field names.

I assume that your contact information table holds the contact info for your employees. Now as to employee wage table, assuming that an employee can have multiple wages (due to raises, promotions etc.) over time and you want to track those changes, then that describes a one-to-many relationship between the employee and the wage table which will be handled like this:

tblEmployees
-pkEmpID primary key, autonumber
-LastName
-FirstName
-Address

tblEmployeeWages
-pkEmpWageID primary key, autonumber
-fkEmpID foreign key relating the wage to the employee in tblEmployees
-dteEffective (effective date of the wage)
-WageRate


You would not need the Days worked (2 weeks) field since you can calculate it from the following table. Assuming that you want to capture the actual work start and end times, an employee will have a start and end date/time for each day worked which describes a one-to-many relationship (one employee to 2 date/times values), and you can calculate the actual time worked from these two records.

You can extract the day of the week from the date using functions in Access.

tblWork
-pkWorkID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-dtetmeWorked (a date/time field that includes both the date and time)

As a note, the foreign key fields indicated above should all be long integer number datatypes to match the autonumber datatype of the corresponding primary key field.

[cpill]

Welcome to the forum!

First, I would use more descriptive field names in place of the "ID" field in the tables. Additionally, it is best not to have spaces or special characters in your table or field names.

I assume that your contact information table holds the contact info for your employees. Now as to employee wage table, assuming that an employee can have multiple wages (due to raises, promotions etc.) over time and you want to track those changes, then that describes a one-to-many relationship between the employee and the wage table which will be handled like this:

tblEmployees
-pkEmpID primary key, autonumber
-LastName
-FirstName
-Address

tblEmployeeWages
-pkEmpWageID primary key, autonumber
-fkEmpID foreign key relating the wage to the employee in tblEmployees
-dteEffective (effective date of the wage)
-WageRate


You would not need the Days worked (2 weeks) field since you can calculate it from the following table. Assuming that you want to capture the actual work start and end times, an employee will have a start and end date/time for each day worked which describes a one-to-many relationship (one employee to 2 date/times values), and you can calculate the actual time worked from these two records.

You can extract the day of the week from the date using functions in Access.

tblWork
-pkWorkID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-dtetmeWorked (a date/time field that includes both the date and time)

As a note, the foreign key fields indicated above should all be long integer number datatypes to match the autonumber datatype of the corresponding primary key field.

You dont add actual fields for records like fkEMPID or anything right?
I need to make gross sales its own table. It just has to be a combined amount of sales for the employees. I need to just track for sales, the date/hour as well as the amount

[jzwp11]

fkEmpID is a field name in the table, you would link the employeeID value to the record. For example, this is what a record might look like in the employee table

pkEmpID|LastName|FirstName|Address
1|Potter|Harry|4 Privet Dr.
2|Weasley|Ron|The Burrow

Then in tblWork

pkWorkID|fkEmpID|dtetmeWorked
1|1|05/24/2011 05:00 AM
1|1|05/24/2011 05:00 PM
1|1|05/27/2011 05:00 AM
1|1|05/27/2011 04:49 PM
1|2|05/25/2011 07:00 PM
1|2|05/26/2011 07:00 AM

The above shows 2 start and stop date/times for employee 1 (Potter) and 1 start and stop date/Time for employee 2 (Weasley)