February Ongoing Project

[Alex2015]

Hello,

I've been building this database since last February & it's killing me, hence why I'm here. This relational database is supposed to track hundreds of employees & their compliance with training (specifically, in a hospital setting). I've run into various problems in the past but have managed to get through them until now. Please keep in mind that I'm doing all of this without VBA because I have zero clue how to write the code.

On to the question; I have more but I'll ask just one here. My database consists of a master table (employees), four other tables that signify the frequency of the training (once, annual, every two years, & every three years), & the four junction tables that join the training tables to the master table via EmployeeID. In addition, I have the hospital unit where the employees work connected to another junction table, which in turn is connected to the master table as well. I hope I have that correctly built.

I want to be able to create an option group where the user can select the hospital unit, then a second option group where the user can select the specific type of training, & lastly have a command button run a query in the background that displays the results in datasheet view. The thing that trips me up now is the fact that there are four different categories for the training (once, annual, so on & so forth). How would I accomplish this? I've been successful before with combo boxes but my database was much smaller back then.

It would be nice if a prompt for a date range could be thrown in there as well. I'm new to this site so I'm not sure if uploading my database is called for.

Thank you in advance for saving my sanity.

 

[spikepl]

Upload a zipped picture of your Relations window with all tables shown and expanded fully

 

[pbaldy]

My first thought is having a single table for training, with a field for frequency (number of months for example). I won't say it can't be done with 4, but I think you'll be jumping through hoops to do it.

 

[Alex2015]

Spike,

Ignore "Floor" & "Unit" in the employees table; I'm going to delete those. I'm also going to migrate "Manager" in the employees table to its own table as well. Thank you for your help.

 

[Alex2015]

My first thought is having a single table for training, with a field for frequency (number of months for example). I won't say it can't be done with 4, but I think you'll be jumping through hoops to do it.

The reason for the four separate tables is because of the calculations that go with them. For example, for annual I have to calculate 365 days (etc, etc.). I used to have it the way you explained until I found out that different calculations were going to have to be separate though.

 

[spikepl]

Paul is right. "frequency" is in fact the inverse of validity; each course retains validity until 1,2,3 or nevernever years have passed. Splitting similar data into different tables is a nono in db's, it is a violation of normalization (look up that term, assimilate it) and that, in turn, causes eternal PITA

 

[pbaldy]

The reason for the four separate tables is because of the calculations that go with them. For example, for annual I have to calculate 365 days (etc, etc.). I used to have it the way you explained until I found out that different calculations were going to have to be separate though.

But I'm saying you store that component of the calculation with the training, all in one table. A field with 12, 24, 36 or whatever. What are you going to do when some training is added with a 6 month frequency? You don't want to be adding tables and modifying forms, etc whenever a something new comes along. A properly normalized database handles it fluidly, with no need for the developer to do anything.

 

[Alex2015]

Paul is right. "frequency" is in fact the inverse of validity; each course retains validity until 1,2,3 or nevernever years have passed. Splitting similar data into different tables is a nono in db's, it is a violation of normalization (look up that term, assimilate it) and that, in turn, causes eternal PITA

I've read up on normalization before but obviously there's more to it. I will look it up.

Another reason why I created so many tables for training is because the same training item could potentially be more frequent on different hospital units. For example, IV insertion training could be done yearly on one hospital unit & every two years somewhere else. It's the same training but more frequent which is why I thought making four tables for once, annual, every two, & every three years was warranted. If I do what Paul suggests, how do I distinguish between the four categories in a query for the calculations?

 

[Alex2015]

But I'm saying you store that component of the calculation with the training, all in one table. A field with 12, 24, 36 or whatever. What are you going to do when some training is added with a 6 month frequency? You don't want to be adding tables and modifying forms, etc whenever a something new comes along. A properly normalized database handles it fluidly, with no need for the developer to do anything.

Thank you for your reply. I didn't see it when I replied to the other poster. I'm going to have to troubleshoot this sometime this week. I'll let you know how it goes. Screenshots helpful?

 

[James Deckert]

Screenshots helpful?
Always

 

[spikepl]

Normalization is about not encoding data into data structure. Now that doesn't makes sense, probably. Yet.

But do you have a Monday-wallet, Tuesday-wallet etc? That is encoding data in the data structure.

So you have one container for stuff looking similar, and you just put a sticker on each bit, to discern between them. So all your courses wind up in the same bucket, just with fields telling you the difference, like name, description, cost, duration, validity etc.,

 

[pbaldy]

But do you have a Monday-wallet, Tuesday-wallet etc?

Be careful, my wife's answer would probably be "yes". But then she's definitely not "normalized".

 

[Alex2015]

Screenshots helpful?
Always

So, the overall structure of my database has been wrong this entire time? :banghead:

Attached is another try. The first pic is of course the new relationship structure. If I have it correct, I do have a question about entering data into the junction table.

In addition, I attached part of the training table & some of its possibilities for the individual items. Hopefully I'm on the right track.

 

[spikepl]

Training requirements are normally bound to a position/job, and percolate through to the guys filling the post. Training history is, in turn, bound to individuals filling the respective posts.

Your new stuff is confusing me, to say the least. I'd recommend that you pay someone to help you set up a structure fitting your environment.

 

[Alex2015]

Training requirements are normally bound to a position/job, and percolate through to the guys filling the post. Training history is, in turn, bound to individuals filling the respective posts.

Your new stuff is confusing me, to say the least. I'd recommend that you pay someone to help you set up a structure fitting your environment.

The training requirements (or "items" in this case) are bound to the hospital units that the employees work on; however, the same training requirement on one floor could be due more often on another floor. Is there any way I could explain it better? Thank you for your input thus far.

 

[Alex2015]

Training requirements are normally bound to a position/job, and percolate through to the guys filling the post. Training history is, in turn, bound to individuals filling the respective posts.

Your new stuff is confusing me, to say the least. I'd recommend that you pay someone to help you set up a structure fitting your environment.

I wanted to also thank you for giving me a new perspective. I think you're correct; perhaps I should associate training items to the hospital floors & then to the employees themselves. It gets a little trickier than that because all of the training items for a particular floor would each have different frequencies, but hey, everyone loves an Access enigma!