Is there a good way to create this table?

[mikethepackerfan]

Hi All, I am trying to create a table that is much like a scheduler. The goal is for someone to enter hours for a particular week (the weeks will go across the table as columns) for multiple employees for multiple projects. What I can't figure out is how to handle the dates. If I create the dates as columns, then I can't create a chart with that many columns. Also, the dates will constantly be changing so using the dates as columns doesn't make much sense.Here's an example of the data week34 week35 week36 week37project 1 employee2 10 40 20 0project 1 employee3 30 0 20 40project3 .. . . etcThe weeks will constantly be updating as time passes, so next week it will start with week35 which also gives me reason to think that the dates can not be the columns of the table.Originally I had it setup like a standard table with a date field and a hour field. But since you couldn't see the entire spreedsheet of all weeks, my boss didn't like it.Thanks for any thoughts,Mike

[mikethepackerfan]

So my example isn't formatting well on this. Let me know if you need me to be more specific.THanks!

[mikethepackerfan]

Here's a picture of how they would like to enter the data. They would also like 40 weeks showing, as time goes on the week that past would be deleted and the next week would be added to the end.

[orange]

The underlying tables, based on the limited info given, would be something like

Project
projectID
projectName
otherProjectflds...

Employee
empID
empFirstName
empLastName
otherEmployeeflds...

EmployeeWorksOnProject
ewopID (you can have a better name)
empId
projectId
workYear
weekNumber
HoursWorked

The underlined field would be the PK
The fields in green would make a composite unique index to prevent duplicates

[mikethepackerfan]

Thanks. I have something like that setup already. The issue, is that they want to be able to enter data in a giant spreadsheet like i've shown (but with 40 weeks). I can't figure out how to show all the weeks and let someone enter hours. They want to be able to see all the data by week in one spreadsheet

[orange]

If they want a spreadsheet, literally, then may be you're going to have to export your data to Excel.

I looked at your input design and created a normalized structure to store the data. You would need a routine(s) to load the form data into the table.
You might be able to report the data in a "spreadsheet type" format with some creative programming.
Perhaps someone else who has experience in that sort of report will offer suggestions.

Quite often people who have worked with spreadsheets think that Access should create a similar output.

As for input you could set up different tabs, say 8 tabs each showing 5 weeks. Then just switch to the next tab when doing input. Still needs some logic, but should work for input. I think the output report (spreadsheet format) may be the bigger issue.
Anyway, good luck with your project.

[mikethepackerfan]

Thanks, I appreciate the help!