New Database Design Help

[mattyn]

Hi All

This seems a good place to start. I have a fair bit of historical IT knowledge - HND level but it was about 13 years ago. I am pretty good at spread sheets, and was reasonably ok with Access - but that was Access 2K.
Now I am in a new job (education facility) there is a massive requirement to move from spreadsheets to an all inclusive management database. Eventually I need to pull all sorts of stats but I will come to that later.

Courses: The facility I am in runs a lot of short term courses - from one day to 8 week duration. Each course is run multiple times per year (the minimum of each course occurrence is laid down by the head office). Each course is grouped into pillars (each pillar has about 15 courses). Each course has a number of EOs (enabling objectives) but not all are reached and I need to be able to see what EOs are not reached.

Students: Students may attend multiple courses (rarely the same course twice but not unheard of should they fail). They are awarded qualifications on successful completion of a course, but that is dependant on what EOs were reached. I need to chase students 6 months after a course to carry out a Course Review Questionnaire.

Staff: Each member of staff has a title, staff number, position name, position number, and fits into one pillar for course delivery. Also, each member of staff needs to complete some mandatory training annually - about 15 small courses which I need to track their due date for each staff member.

I also want to be able to manage classrooms - we have a number that differ in maximum capacity - from 6 to 16. Each course has a maximum capacity of students (based on course content not classroom size) and I need to be able to link the two together to enable a slightly easier planning phase of course generation.

First question is - this year we are planning 500 course occurrences with about 3000 students going through. Is Access up to the task?

Second question - I have started to design this, but scrapped it as I think I am trying to solve all the issues at once. What is the best way to attack this problem?

Third - the HQ are mental about stats. How many training days are we actually delivering, what EOs are we missing, why are students attending the course, how many courses were cancelled for non-attendance, how many no-shows did we get per year, to name but a few.

I see this as three databases in one - a Student, Staff and Course Management databases.

I would really appreciate some expert thoughts on this. Even if I don't get HQ to support it, I am quite keen to build it as a personal project - a bit of self learning!

Many thanks in advance

Matt

 

[mattyn]

To assist you, hopefully I can make things clearer about what I have done so far, and explain the concept.

The firm manning structure has positions, which have a Position Name and a unique number. One person can only fill one position, but a position can be filled by more than one person (we call it doubling up).

Each employee is employed to do a slightly different job - and hence a number of qualifications are attached to each position. He or she may arrive any number of those qualifications, so therefore we adopt a training burden to get him/her fully qualified. The Adqual is that qualification and is different to mandatory training! Some Adquals require regular competency checks. The Achieved field is purely a tick box.

All members of staff have to complete a suite of mandatory training (think first aid, manual handling, diversity and inclusion etc). We have to track each member of staff and their mandatory training - what they need to do and when.

Hopefully that makes things clearer.

Thank you all for the help

Matt

 

[GinaWhipp]

1. Renaming is great but you cannot relate them to each other like that. You must use Foreign Keys in adjoining Tables.
2. It will be one record per Staff member to you only need one tblRank and you will only be updating one field. So, Staff Change = new record with an assigned Rank. The same applies for tblJPAN
3. Then remove the link between tblStaff and tblAdqualID that will never work.

Once you make the changes please repost diagram.

 

[GinaWhipp]

Yes, that does make things clearer but you still need to make changes to your Data Model...

 

[mattyn]

Some changes made. Relationships now look like this.

 

[mattyn]

The odd link between Staff and AdQual must have come from this Query....

I have removed the link from StaffID in tblStaff to AdQualID in tblAdQual and all seems to be working as before.

Off to read about Foreign Keys........

 

[mattyn]

Having made the changes GinaWhipp suggested ^^^

 

[GinaWhipp]

Much better and makes more sense to what you are doing...

 

[mattyn]

Thank you!

 

[Rijnsent]

Hi Matt,
I agree with Gina it's getting better, two points: "TelExt(93781)" and the point I mentioned: how will you register the trainings of your staff?

-I wouldn't use TelExt(93781), especially the () might at some point be annoying in queries/formulas, if you insist on having the number there, TelExt_93781 would be better. Better still would be TelExt5Digit. If you go to the design mode of that table: there is a column "description" in that design mode to make notes on the contents of certain fields, putting it in field names is generally bad practice .

-in tblStaffMandTrng you have a line for every training that a staff member has to do and the due date. So I imagine you put in a line for e.g. "John", "Potty Training", due 26-jul-2016. If you want to show afterwards that John really completed that training you could either: remove the line, so it drops off the "to-do" list, but that means that you can't show/prove that John passed the Potty Training. The second option is to add a field (or maybe fields) with TrainingCompletedDate and maybe something like "DiplomaReferenceNumber", so you keep a history of the completed trainings. That would also help to e.g. create new lines for trainings that have to be completed every year/5 years.

Have fun with the next steps ,

Koen

 

[mattyn]

Koen

Roger that, thank you.

I want to add a tabbed form to a form I have created from the tblStaff, to show and calculate absence.

The table I have started is as follows:

AbsenceID - PK
StaffID - linked to StaffID in tblStaff
BegDate - First day of absence
EndDate - last day of absence
TotalDays - I want this to calculate the total number of working days absence, based on the two dates above (EndDate-BegDate). This needs to be working days, not total days.

I know I have to add some code somewhere, but how do I do this? Code I have found is here

https://msdn.microsoft.com/en-us/library/office/ff191987.aspx

But how do I apply that to the cell TotalDays in tblAbsence?

I have searched and see somewhere about adding it into a query, but it is really confusing me!

Please help! (again!!!!)

Matt