Create a new database with relationships

[ukfastcar]

If training courses are events they have to attend... is it only a single day every time? Normally it's something like

CourseTopic--(1,M)--CourseTrainingEvent(CourseTrainingEventID, CourseTopicID,TrainingDate)--(1,M)--Attendance(CourseTrainingEventID, EmployeeID)--(M,1)--Employee

Apologies for the really nice snake, but that should give you a starting point. It's a variant of the Students and Classes database, so you may want to look that up too.

Yes each training is just a day course.

What is (1,M)

[madpiet]

CourseTopic--(1,M)--CourseTrainingEvent

Each CourseTopic can have 0 or more Course Training Events... each Training Event is related to exactly one Course Topic.

For example, "First Aid Course" can have training sessions on (Jan 1, Feb 1, March 1)...


If this is basically a training database, are there rules about who has to have what training? I'm asking because I did one like this a long time ago, and I'd bet I can save you a lot of time and hassle if you explain the rules about who has to attend what training etc. In my case, there were rules like "everyone in Department X needs to attend this set of training courses every year {list of courses}" etc.

up to you though.

[ukfastcar]

CourseTopic--(1,M)--CourseTrainingEvent

Each CourseTopic can have 0 or more Course Training Events... each Training Event is related to exactly one Course Topic.

For example, "First Aid Course" can have training sessions on (Jan 1, Feb 1, March 1)...


If this is basically a training database, are there rules about who has to have what training? I'm asking because I did one like this a long time ago, and I'd bet I can save you a lot of time and hassle if you explain the rules about who has to attend what training etc. In my case, there were rules like "everyone in Department X needs to attend this set of training courses every year {list of courses}" etc.

up to you though.

Thanks for the reply.

The courses will have many session with different people attending

For example in 2025 we will run Course 10 about 25 times and each person must attend one of these, then is 2026 we will run Course 11 about 25 times and again each person must attend only one course.

However during 2025 we might have some people who require Course 9 so we would run some of them also.

The overview is that each "course" will be different but run multiple times and each person must attend at least one of those courses. There must be a record to say what courses they have were placed on and if they attended.

I hope this explains what I am looking for madpiet and once again thanks for your help/

[madpiet]

Maybe the "instance" of a course being taught would be a "session" and then each of those would be attended by individuals.

Course--(1,M)--TrainingSession--(1,M)--Attendance--(M,1)--Employee

(Yep, I love me some crows foot diagrams! Super handy once you know how to read them!! And really quick to modify.)

So "TrainingSession" might look like (apologies for the T-SQL, but Access doesn't lend itself to text documentation. =( )

CREATE TABLE TrainingSession (
SessionID INT IDENTITY(1,1) PRIMARY KEY,
CourseID INT NOT NULL,
SessionDate DATE NOT NULL,
TrainerID INT DEFAULT NULL,
...
FOREIGN KEY CourseID REFERENCES Course(CourseID),
FOREIGN KEY TrainerID REFERENCES Trainer(TrainerID)
);

etc.
Attendance is super simple (TrainingSessionID, EmployeeID, Present(Y/N) )

[mike60smart]

Where does TachoNumber come into the Training Cycle?

[ukfastcar]

Where does TachoNumber come into the Training Cycle?

It just a reference number per person

[ukfastcar]

@madpiet

I have created what I think are the correct tables?

Where do I go from here

Thanks

[madpiet]

One thing you might want to do is find an implementation of the super exciting Students and Classes database somewhere. That should give you a lot of pointers on how to design the tables and maybe the forms, because Training stuff is only different in that the "courses" only last a day or so and there are no "grades" to speak of. Might give you some ideas about how to lay out your forms etc, and at least what's possible. The handy part is that MSFT probably has one for you to dissect and see how each of the parts works. It's a super handy way to learn. You can tear it apart and change things (which may break things) and figure out how to fix them. I think that's how a lot of us learned after getting our heads around the whole relational design thing (that takes a while too).