help with table design/relationship

Hello,

I am designing tables for a timesheet project.The details of the table
are as follows


Employeedetails (tableName)

EmployeeNumber
SocialSecurity
Title
Position

TimeSheet(tableName)

EmployeeNumber
date
Day
TimeIN
TimeOut
TotalHrs

EmployeeWorkSiteDetails(table Name)

Employee Number
WorkSite
Date
Day

I have created a relationship b/w
Employeedetails.EmployeeNumber - TimeSheet.EmployeeNumber
EmployeeDetails.EmployeeNumber -
EmployeeWorksitedetails.EmployeeNumber.

On the form When the user enters Timesheet details , I need to insert
the WorkDate and Day from the timesheet table into
EmployeeWorkSiteDetails.Workdate and EmployeeWorkSiteDetails.Day.

So do I have to create a relationship b/w Timesheet and
EmployeeWorksiteDetails.
And also How do I set the join types/Join properties when creating a
relationship.Do I keep it to default
Any input is highly appreciated

Thanks

re: help with table design/relationship

In database design, we aim to never store redundant data.
A lesser but useful goal is to reduce the amount of typing for the data
entry operator.

Your EmployeeDetails table is good. The TimeSheet table needs only:
EmployeeNumber Number (Long) Foreign key to EmployeeDetails.
StartDateTime Date/Time Date and time started work.
EndDateTime Date/Time Date and time finished work.

To create the relationship:
Tools | Relationships.
Drag EmployeeDetails.EmployeeNumber onto Timesheet.EmployeeNumber

User entry form will be a continuous form, with:
combo box for selecting the employee,
text box for StartDateTime
text box for EndDateTime

From that simple information, Access can give you the day name (from the
date) and the hours worked (difference between start and end times). The
structure even copes with shifts that span midnight if that's ever needed.

For help on how to calculate the time worked, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Doslil" <doslil@hotmail.com> wrote in message
news:c677919e.0312041406.2d676035@posting.google.c om...[color=blue]
>
> I am designing tables for a timesheet project.The details of the table
> are as follows
>
>
> Employeedetails (tableName)
>
> EmployeeNumber
> SocialSecurity
> Title
> Position
>
> TimeSheet(tableName)
>
> EmployeeNumber
> date
> Day
> TimeIN
> TimeOut
> TotalHrs
>
> EmployeeWorkSiteDetails(table Name)
>
> Employee Number
> WorkSite
> Date
> Day
>
> I have created a relationship b/w
> Employeedetails.EmployeeNumber - TimeSheet.EmployeeNumber
> EmployeeDetails.EmployeeNumber -
> EmployeeWorksitedetails.EmployeeNumber.
>
> On the form When the user enters Timesheet details , I need to insert
> the WorkDate and Day from the timesheet table into
> EmployeeWorkSiteDetails.Workdate and EmployeeWorkSiteDetails.Day.
>
> So do I have to create a relationship b/w Timesheet and
> EmployeeWorksiteDetails.
> And also How do I set the join types/Join properties when creating a
> relationship.Do I keep it to default
> Any input is highly appreciated
>
> Thanks[/color]

re: help with table design/relationship

OK Now that I have only 2 tables.
EmployeeInformation(tableName)
EmployeeNumber
First Name
Last Name
Social Security Number
Title
Position

TimeSheet(table)

EmployeeNumber
worksite
Workdate
Day
TimeIn
TimeOut
{HHI
PH2 ---- These are the different worksites where the
---- total hours worked will be distributed
PH4
PH6}

I am trying to design a form with a subform.The main form has employee
number,FName,LName,Title.

The SubForm Has WorkDate,TimeIn,TimeOut.For the worksite field (this is
the main woksite which has a drop down list).The user selects this only
once - i do not want the user to enter this everytime he enters the
timein and timeout.I am trying to put this as another subform.But I am
getting error.Can you please guide me how will I go with this.

regards


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: help with table design/relationship

> EmployeeNumber[color=blue]
> worksite
> Workdate
> Day
> TimeIn
> TimeOut
> {HHI
> PH2 ---- These are the different worksites where the
> ---- total hours worked will be distributed
> PH4
> PH6}
>
> I am trying to design a form with a subform.The main form has employee
> number,FName,LName,Title.
>
> The SubForm Has WorkDate,TimeIn,TimeOut.For the worksite field (this is
> the main woksite which has a drop down list).The user selects this only
> once - i do not want the user to enter this everytime he enters the
> timein and timeout.I am trying to put this as another subform.But I am
> getting error.Can you please guide me how will I go with this.[/color]

Umm... could you move, I can't see over your shoulder, so I can't see
the error message you're getting. i guess you could put the location
in the employee table... (not that I like it), but then you could set
the subform's default = to the value in the main form. Otherwise, you
could have the database get the last location for the current employee
using DLookup or something. Then You'd only have to set it once.

What's with the PH1...PH6? These should be separate records
(EmployeeID, PH#, DateIn, DateOut) or something like that.