Database design help!

[Takstein]

Hi all!

I am thinking of designing a simple database for our office for resource allocation of workers. We typically have 5 to 15 projects running at a time divided over 15 workers or so.

The idea is to make an open database where each worker is responsible themselves to enter data (The amount of hours they will work on which project).

The idea is to get a picture a few weeks ahead for what each worker will be doing.

I have made a test database based on a template from access 2010, but now i need to represent the data and i am struggling. I have attached a excel sheet print (The numbers are hours worked) of how i'd want to see the data. Does anybody have any ideas of how this can be easily implemented in access?

I am fairly new to access btw, but think this will be the right tool to use.


Any feedback would be highly appreciated..

regards,

Takstein

 

[NickHa]

This sounds like a complex project for a novice Access user. Have you considered pre-packaged software which does what you describe using web interfaces? You would have a much shorter implementation time, plus you would get the support of the product vendor. If you search the internet for 'webtime' you will find vendors who do this sort of thing.
Probably not the response you want - apologies.

 

[Lightwave]

While I agree to a certain extent with Nick I find internal applications with only a few users are sometimes a good application to get started on for beginners. Also I find project management software really diverse so I find bespoke is often a good option

Bear bones of your database probably should be three tables
TableEmployees
PKID
First Name
Surname

Table Projects
PKID
ProjectName
Start
Deadline
Description

TableHours
PKID
EmployeeID
ProjectID
Hours
Description
Week

Reports will be some kind of Pivot Table on the third table - I would Group by project and make column headings as the separate weeks and employee names as the rows.

The table hours is a junction or many to many table. You would probably quickly want to get more complicated than this but this would always be my starting three tables.

You might be able to implement a pivot table through your existing design but as I don't know its structure that's just a guess.

 

[Takstein]

This sounds like a complex project for a novice Access user. Have you considered pre-packaged software which does what you describe using web interfaces? You would have a much shorter implementation time, plus you would get the support of the product vendor. If you search the internet for 'webtime' you will find vendors who do this sort of thing.
Probably not the response you want - apologies.

Thank you for the input, its appreciated!

 

[Takstein]

While I agree to a certain extent with Nick I find internal applications with only a few users are sometimes a good application to get started on for beginners. Also I find project management software really diverse so I find bespoke is often a good option

Bear bones of your database probably should be three tables
TableEmployees
PKID
First Name
Surname

Table Projects
PKID
ProjectName
Start
Deadline
Description

TableHours
PKID
EmployeeID
ProjectID
Hours
Description
Week

Reports will be some kind of Pivot Table on the third table - I would Group by project and make column headings as the separate weeks and employee names as the rows.

The table hours is a junction or many to many table. You would probably quickly want to get more complicated than this but this would always be my starting three tables.

You might be able to implement a pivot table through your existing design but as I don't know its structure that's just a guess.

Thank you for your input! This is actually very close to what i have already, i just never thought of putting week as one of the factors... I was thinkin a needed a ned "time" table...

I haven't tried pivot tables before but will do that now! thanks for your help!

 

[Lightwave]

Thank you for your input! This is actually very close to what i have already, i just never thought of putting week as one of the factors... I was thinkin a needed a ned "time" table...

You could use a date field instead of a week field and use calculation probably to work out the weeks. Weeks is the simpler.