Question MS Access Pivot Table Structure

[willbarraclough]

Hi guys, I'm new on here, but come baring a question I just cannot seem to solve myself, despite many attempts using different methods.
At work, we are using a MS Excel work sheet to keep track of staff hours, and naturally it's growing, and Excel cannot cope with the data in it.

I am looking for what I think will be classed as a pivot table, but cannot seem to calculate the table structure required to work with in the same way as the excel sheet.

Attached is a screen shot of what we're working with, I need to create a system that would have the same user interface, but obviously would store the data in normal table form, making it easier to handle, and produce reports from.

Naturally I've used sample data, but the date range across the top needs to continue indefinitely - no fancy rules for weekends required as we're a 24/7 operation.

Please get in touch if you need more elaboration...

 

[plog]

Structuring your data properly is called normalization (http://en.wikipedia.org/wiki/Data_normalization), I suggest you read up on it and work through a few tutorials.

One key aspect of normalization is that the data itself determines the proper structure. Not the forms you want to use, not the report layouts you want to achieve, just the data itself.

Your data is pretty simple, the correct way to structure your tables is like so:

Staff
Staff_ID - autonumber primary key
Staff_FirstName - text
Staff_LastName - text

TimeSheet
TimeSheet_ID - autonumber primary key
Staff_ID - number field, foreign key to Staff table
ShiftStart - Date/Time
ShiftEnd - Date/Time

With the sample data you provided, that is your table structure when you use a relational database.

Now to achieve the reports and input forms you want, that's really going to take some complex coding because your prefered method of input doesn't align with a simple form. My suggestion would be look at other's databases (time trackign ones are out there) and see the forms they used. I really think you need to use a more traditional input form, it can be just as easy as you have.