X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> New Database, Am I Structuring Tables Correctly?, Access 2016    
post Feb 23 2018, 10:41 AM

Posts: 16
Joined: 9-October 17

Hello everyone. I was just tasked to create my first Access database from scratch. Previously, I have worked on and made changes to existing databases. This will be my first attempt at designing one on my own and I was hoping to get some feedback as to how I'm doing. I really don't want to get deep into it just to find out later I made a fundamental error. I am attaching a zip file with my tables and a screenshot of the current relationships in case that helps anyone visualize what I've done so far.

Here are the requirements I have. I am trying to track training on different equipment for individual employees. Each employee can be trained on several pieces of equipment. The training usually lasts several weeks and a single person (the PIC) is in charge of overseeing the training. During the training time, the employee may get called to do other things, so I've created a training detail table to show the actual days the training was done. Since the PIC doesn't actually do the training, there is also a field for the trainer the employee follows that day.

I envision a main form where I can filter by employee, and then see a subform with the tblTraining information (PIC, start/end dates, status) and a subform under that with the training details for each position that employee has trained for.

The Employees table will be linked from another database that they use to track crew assignments and I do not believe I'll be allowed to make changes to it. Everything else will be a local table.

Here are a few issues I ran into already.
1. I want to use employee ID numbers for the PIC and Trainer fields in the tblTraining and tblTrainingDetail respectively but can't link them to the EmployeeTBL because of the existing relationship. Is there a workaround for that or is it even necessary?
2. I couldn't link the tblTrainers to the tblTrainingDetail table by EmpID. It said "No unique index found for the referenced field of the primary table". Again, am I doing something wrong or is that unnecessary?

I know I could piece something together that got the job done, but I really want to learn how to do all this the "right" way. Thank you in advance for any advise you can share.

- Eric

Attached File(s)
Attached File  Training.zip ( 59.31K )Number of downloads: 4
Attached File  table_relationships.PNG ( 21.89K )Number of downloads: 14
Go to the top of the page
post Feb 23 2018, 12:06 PM

Posts: 908
Joined: 12-November 03
From: Iowa Lot

A few thoughts...

1. Assuming this is the same employee, you don't need EmpID in tblTrainingDetail, its already linked to tblTraining through TrainingID.
2. You probably want tables / FKs in EmployeeTBL for Term, Plant, Crew, Line
3. You can link EmployeeTBL to PIC just by adding EmployeeTBL again...It will show as EmployeeTBL_1 or similar.

What is PositionID defining a position for? If the employee, should it not link to EmployeeTBL instead of tblTraining? Or are you training for a Position?
Also, are Trainers also employees? Why not just have a field in employeeTBL to flag as a trainer? Not saying that's better, just asking. Would you want to link the Trainer (e.g., from tblTrainers) to TrainerID in the detail table?

prolly more questions...
This post has been edited by kfield7: Feb 23 2018, 12:12 PM
Go to the top of the page
post Feb 23 2018, 12:41 PM

Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)

Not allowed to change existing employee table? Right? So you need to maintain the trainer list in another location....ok too bad but it will work.

You wish to link trainers to trainingdetails by empid? That sounds wrong to me....would think that the trainers are linked to the trainingdetails by trainerid.
Go to the top of the page
post Feb 23 2018, 01:13 PM

Posts: 16
Joined: 9-October 17

kfield7 and zaxbat

1 & 2 make sense. I tried not to duplicate data but must have missed that or just thought about it incorrectly.
3. Got that to work. I had seen that when I tried originally but thought I had messed something up.

The PositionID is the position they're training for. They could end up being trained for several positions. They might be assigned to a certain machine, but then train on others to backfill as necessary. The employee is assigned to their permanent position in the other database that the table is linked from.

Trainers are also employees. If I run machine A, I could be tasked to train other employees on it. I thought about trying to add that field to the employee table, but there are some employees that are qualified to train on multiple machines. That's also why I was trying to use the trainer's employee ID in the tblTrainingDetails table, because a trainer could have multiple trainerIDs (combinations of the employee ID and positionID) which I thought would be confusing to reference.

I'm all for tracking this stuff in a different manner as long as it makes sense. If there are fields I could add to the employees table that would help, I can probably get that done. I just can't modify the existing fields. Thanks for looking and providing your thoughts.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th December 2018 - 10:44 AM