Linking a set of dates for a time sheet

[Anthrax]

The project I am currently working on requires me to build a database for checking off if a certain person has submitted a time sheet for a certain date.

I have a table for the dates that has the fields "date" and "Submitted time sheet?". The time sheet field is a checkbox.

I also have a table for employees with fields "employee last name", "employee first name" and "location".

What I want to know is how to link each employee with the dates and whether or not they submitted a time sheet.

I have yet to figure out a solution that does not result in a horribly normalized database.

 

[Pat Hartman]

The SubmittedTimesheet table needs additional columns.

tblSubmittedTimesheet:
SubmittedTimesheetID (autonumber primary key)
EmployeeID (foreign key to tblEmployee)
DateSubmitted

You don't need the Boolean field since the date tells you whether a sheet was submitted or not and in any event, column names should not include embedded spaces or special characters such as the question mark.

The EmployeeID in the TimesheetSubmitted table will be used to link to the Employee table.

 

[Anthrax]

That was helpful in getting a few things sorted out but I'm still confused as to why I don't need the Boolean field. How would somebody mark whether or not a persons time sheet was submitted?

EDIT: never mind after looking at it and inputting data I understand why. Thanks a lot.

 

[Anthrax]

One more question, how do I get historical dates to show up so I can input old data all at once?

What I mean is that its more rare for an employee not to submit a time sheet so I want it do default to showing up as submitted.

 

[Pat Hartman]

You need to decide what would prompt you to create a missing record and what you would use for hours. Creating bulk rows could be done with a query joining the table of employees to a query that selects the timesheets submitted so far for a particular week. Using a left join and looking for a TimesheetID that is null will return only the employees who have NOT submitted a timesheet. You would then use that information to generate the missing records.