tables and relationships stucture (1 Viewer)

Hi, At the moment am working on the structure for a planning database.

I have attached image of the structure so far.

The problem is that I want to capture the user which is the planning team actions. So if they amend or add for example a holiday I can track who in the team action the request. Also Do I hold all request in one table for example holiday, amend shift, amend lunch or should these be tables of their own?

Also when working with dates in the future should I have a table of dates for example so then I can then setting a table of hours worked as I know the shift length of each staff member

Date: 01/01/2018
Day: Monday
Year: 18

Hi K3vin, this may seem strange coming from a member of this forum, but what you are trying to do is a little advanced.

Don't get me wrong, Access is perfect for this sort of thing, but it is going to require a huge effort on your part to make this work. If you have the desire, you will get there as long as you do not get discouraged.

Allow me to offer two courses of action at this point.
1. Download a template (Projects maybe?) and study it. Once you get familiar with it you can modify it as needed. This forum will be able to help you better if you can provide existing tables, their structure, forms and code.

2. Depart from Access and give MS Project a try. It already does what it is you are trying to do as far as tracking user activity and it interfaces with Access nicely.

Best of luck!

I agree with Nautical about finding a template. But I will also critique your tables. Here's what I see:

~tblStaffList~

- All those day of the week hour fields need to go ([MondayHours], [TuesdayHours] etc.) into a new table. That new table will contain just 3 fields--STaffID (as a foreign key to tblSTaffList), WorkDay (to hold the values Monday, Tuesday, etc.), and WorkHours which will hold the value now in all those fields.

- Does each employee have their own CostCenter? Or does a supervisor? Where we work its the supervisor. If so, the CostCenter should go in the Supervisor table.

- Can a department have multiple supervisors? If not, you do not need both Department and SupervisorID in tblStaffList.


~tblRequestDetails~
- Date/Time fields can hold both a date and a time. Therefor StartDate and StartTime don't both need to exist. Put both pieces of data (date and time) into one field. Same for EndDate/EndTime


~qryResourcePlanningStaff~
- Queries usually don't belong in a relationship view. Instead you show what tables go into that query and use those.

Thanks Nautical and Plog for the feedback.

I have download the project management database and will start to study the relationship, tables etc.......

It does feel like a big task started building from a new database I can't seem to move forward even building the tables and relationships.

I have only build a few simple databases so this one will be a challenge.

I go with using the template but am sure I be posting for help as I go along.

With a template is the best approach to amend the tables, forms etc... to suit or should I re-build with the tables I have but using them as a reference?

Thanks again at least now I have a starting point.