Making sure tables are set up correctly

[KristenD]

I have a post in the Forms section that relates to this post but I have not received any replies as of yet. As I was thinking about the form and having redone the tables I want to make sure the table design is normalized. I have 3 tables for this sub form which ties back to the Main form PK plus the main table. They are as follows:

tblEmployee
*EmpID <PK>
*EmpName
*EmpStatus

tblOSHA
*OSHAID <PK>
*EmpID <FK> Ties back to the main table/main form
*EmpInfoID <FK>
*OSHATraining
*TrainingDate
*Document
This table must be filled out.

tblEmpInfo
*EmpInfoID <PK>
*EmpType
*CraftCode
*JobNumber <FK>
This table is only filled out if the EmpStatus = "Active"

tblJobs <LookUp Table>
*JobNumber <PK>
*JobSite

One of the members here helped me with a code to "gray out" the boxes in the form (which has been deleted and I'm trying to figure out the best way to recreate the form so it works if you want to look at my post in the Forms section) of the table fields that are not required. As of right now when I try and enter on the previous form I created it gives me a lot of compile errors as well as if the tblJobs fields are not filled out then it will not let me advance to the next record and save the info.

So I thought maybe I need to go back to the drawing board and make sure that the tables are normalized correctly. Originally I only had 2 tables with tblOSHA and tblEmpInfo together and then the tblJobs look up.

I really need help...I am coming up against a deadline and I still have 2 more sections of this database to complete. I do appreciate all the help that has been given to me, if some one can PLEASE at least point me in the right direction that would be super helpful. Anything at this point!

 

[plog]

Big picture, give us what this data is for and a sentence or two about how it operates--don't use any database or VBA jargon, just plain english.

Then from a table standpoint here's some specifics:

1. For every Employeein tblEmployee, how many records can they have in tblOSHA? You've said it has to be 1, but could it be more?

2. For every Employeein tblEmployee, how many records can they have in tblEmployeeInfo? You've stated it could be 0, but could it be more than one?

3. What happens to the data in tblEmpInfo if an Employee's status changes from 'Active' to something else?

4. How many stati are there?

 

[KristenD]

1. For every Employeein tblEmployee, how many records can they have in tblOSHA? You've said it has to be 1, but could it be more?
They can have multiple records.

2. For every Employeein tblEmployee, how many records can they have in tblEmployeeInfo? You've stated it could be 0, but could it be more than one?
Again, they can have multiple records here as well.

3. What happens to the data in tblEmpInfo if an Employee's status changes from 'Active' to something else?
At that point, I would not want to see that information

4. How many stati are there?
I'm not sure what you mean.

This data is to be used to track OSHA cards for our employees. As employees are rehired or hired in as a new hire they are required to have this training. If they are active employees, there will be a report generated to management to show the percentage of cards on a particular job. Currently the report is done through excel and is rather time consuming. I hope that helps.

 

[plog]

By stati, I mean how many values can the EmpStatus field have? I was just wondering if there was more than just Active and Inactive. With your other answers, its not really important--you gave me enough info.

The relationship among those 3 tables is tripping me up a little. It may be right, I just can't get my head around it. The main thing is that tblEmpInfo isn't directly connected to tblEmp, it's connected to tblOSHA. Is that right? It seems wrong, especially when you consider tblEmpInfo is somewhat dependent on tblEmp (only populated if EmpStatus='Active'). Throw in the 1 to many relationships you have going and it seems like they should be directly connected.

 

[KristenD]

That is exactly what I was thinking too, since tblEmpInfo is dependent on that EmpStatus if I should have it linked there too.

There are 4 stati that an employee can have: Active, Eligible, No Rehire and Disability. The only reason I am using it in this table is to have that report pull directly from Access instead of doing a manual update in the weekly Excel report I am currently doing.

Can I do it that way? Would have to have EmpStatus in tblEmpInfo or EmpID?

I think once I get the tables related properly then I won't be having the errors I am getting with the current db setup. It seems no matter what fixes I do at the moment it just will not work with the current table set up and relationships.

 

[plog]

I really don't understand what tblEmpInfo does. I can't see the hierarchy of the tblEmployee, tblOSHA and tblEmpInfo. Can you give some sample data of those 3 tables?

Now that I look at the tblJobs table, I'm starting to think that the relationship between it and tblEmp is incorrect as well. Somewhere there should be a table that has JobNumber and EmpID in it. The way it is now, you have to go through all 4 of those tables to connect an employee to a job.

 

[KristenD]

This is from a previous back up but this will give you a general idea of what I am looking to do in the form. But instead of the yes/no field I was going to do a combo box.

When I enter the data if on the mainform above the tabs that status is active the form will require me to fill out all the information. If the status is anything BUT active then I will only need to fill out the OSHA information (OSHATraining, TrainingDate, and Document)

This is where I am running into my dilemma, that tblEmpInfo is based off that status and the status can change as well as the jobnumber and jobsite can change. This information is not integral to the data we are collecting it is only for reporting purposes. tblOSHA holds the information that we are really tracking and will be used most often.

tblEmpInfo I thought would be easier to maintain in Access but is not critical to the database.

 

[Adam Caramon]

Hi Kristen,

I was the person that helped you before. Your database design needs some retooling. I think I see what it is that you're trying to do, but it would take some time to fix it (consolidate some tables, redesign relationships, etc).

When is your deadline?

 

[KristenD]

Two weeks. I figured I would need to redesign the tables and relationships as no matter what I did in the current design I was getting errors.

The thing that is tripping me up is the two tables that are only needed for a certain portion of employees (tblEmpInfo & tblJobs). In the current db (I have one portion that went live already) I have nearly 600 employees.

But out of that 600 for this portion of the db I would only have records on about 250 that are currently working. I know I would be adding at least another 75 records in the OSHA portion. I'm thinking this will take a lot of coding and that is where I am still in the beginning stages of learning.

I just heard back from one of the mgmt team and it will only be one record per employee. Meaning data will be overwritten.

For example, if an employee has a 10 hour card and then receives a 30 hour card with a more current training date then the information that is currently written in the record will be changed. Hope that makes sense.

Thanks again for all your help. I am going crazy here!! :banghead:

 

[Adam Caramon]

Two weeks. I figured I would need to redesign the tables and relationships as no matter what I did in the current design I was getting errors.

Okay, 2 weeks is a good amount of time anyhow. I will definitely have some time over this weekend to help.

Do you have a more recent version of your db that you want to upload, or is the one you provided a week ago or so still current?

The thing that is tripping me up is the two tables that are only needed for a certain portion of employees (tblEmpInfo & tblJobs). In the current db (I have one portion that went live already) I have nearly 600 employees.

It is all in your perception. Tables should contain all data, queries filter that data, and forms present the data. So, if an employee is not active, they still are listed in the table, but you would filter them out in your queries (assuming you don't care about them anymore), and then if you base your main form on that query, you won't even see the non-active anymore (but you can still access them should you need to).

If an employee changes from active to something else, you update them once, and then next time you open the form, they would no longer show up (since they're no longer active).

I just heard back from one of the mgmt team and it will only be one record per employee. Meaning data will be overwritten.

I do not recommend that approach, unless you're intentionally writing over the data for security purposes (which should be very rare). The ongoing value of a database is to be able to review data, regardless of how old, which really enhances your reporting capabilities.


If you're comfortable waiting for a couple of days, until sometime Sunday at the latest, I will try to provide a retooled version of your database along with an explanation. You can then either use that database and make any changes, or pick and choose things from it that you want to incorporate into your copy of the database, whichever makes more sense to you.

 

[KristenD]

is the one you provided a week ago or so still current?

Yes, that is still the most current version of the database. The back up I have right now has no data or tables in it at the moment. I have no problem waiting. I am doing tutorials right now on VB for Access as I know the rest of the database will require more and more coding.

Tables should contain all data, queries filter that data, and forms present the data. So, if an employee is not active, they still are listed in the table, but you would filter them out in your queries (assuming you don't care about them anymore), and then if you base your main form on that query, you won't even see the non-active anymore (but you can still access them should you need to).

Ok, I was wondering about that since I had posted a question a day or two ago asking if the form should be based off of a query.

The only "problem" is I am the only one that does the entry for all the departments. The front end that the department managers use has only reports and excel exports on their switchboard. I was trying to figure out the easiest way for me to do all the entry with ALL the employees.

The one report using the "active" status is the ONLY report that is based off that. All the other reporting will not use that field.

Maybe I should have some sort of "pop up" form then that would request that information. Would that work?

The first portion of the database only took me 6 weeks from development to entry to go live. I am on almost 8 weeks now and back at the drawing table with this portion.

Thank you for your help!!

 

[Adam Caramon]

Yes, that is still the most current version of the database.

Okay, I will take a look, do some fixes, and post an update to this thread.

The one report using the "active" status is the ONLY report that is based off that. All the other reporting will not use that field.

Right, so you will have several different reports. One of which could be "active only", for example.

Maybe I should have some sort of "pop up" form then that would request that information. Would that work?

You could do that. You could have a form pop up when you open a report, and that form would then supply parameters to the report.

The first portion of the database only took me 6 weeks from development to entry to go live. I am on almost 8 weeks now and back at the drawing table with this portion.

Yes, that will happen on occasion. Especially as your needs change, you will need to reevaluate your design and tinker with it.

 

[KristenD]

Okay, I will take a look, do some fixes, and post an update to this thread.

Thank you so so so much!!

You could have a form pop up when you open a report, and that form would then supply parameters to the report.

Ok I think I'm getting it. So that form would then generate the information based off the EmpStatus and only pull up those employees that have that status and somehow link that to the OSHA info as well and then fill out the info as needed. The first entry would take awhile but then after that it would just be maintenance as employees transfer, get laid off, rehired. Correct?

Yes, that will happen on occasion.

I feel better! It seems as though I had met my match. The other portion of the database was tricky like this one but in a different way but I had a lot more AHA I get it moments than I have this go around!

 

[Adam Caramon]

Ok I think I'm getting it. So that form would then generate the information based off the EmpStatus and only pull up those employees that have that status and somehow link that to the OSHA info as well and then fill out the info as needed. The first entry would take awhile but then after that it would just be maintenance as employees transfer, get laid off, rehired. Correct?

Yes, exactly.

I feel better! It seems as though I had met my match. The other portion of the database was tricky like this one but in a different way but I had a lot more AHA I get it moments than I have this go around!

With databases, its all about not giving up (and backups, and normalization, and, you get the idea )

There have been times when I have been completely stumped, but then I take a break from it for a while, and that AHA moment hits.

 

[KristenD]

Yes, exactly.

I think that was an AHA moment for me! I think I'm more frustrated since this portion seemed to be a bit easier than the last one.

But thank you so much for helping me out with this. I will be doing some research on pop up forms and some tutorials tomorrow.

 

[Adam Caramon]

Attached is what I have come up with so far. As tblEmployeeInfo and tblOsha are both blank, I'm struggling a bit to determine their exact purpose. I have a feeling that tblEmployeeInfo could be condensed into tblEmp, but it could be that I am not understanding what you're trying to capture with this table?

A few general things to keep in mind: tables stores data, you should not be entering data via a table. Queries ask questions of the data. Forms are for data entry. Reports are for displaying the data.

I removed all of the queries that were not directly related to a report (I suspect you'll have to recreate a few if you use them for other purposes). As many of the tables were redesigned, it will definitely be easier to do so rather than trying to update the queries as they were.

I also suspect you could lower the number of queries anyhow, as many of them seemed to be displaying the same data.

There is still a lot of work to be done with this db, but hopefully this will help. As I said before, feel free to either use this db and then add to it, or simply look at it and update your current db.

Here is a short list of things I changed (I am sure I missed some):

Created tblSupervisor to hold supervisor info.
Deleted supervisor name from tblEmpRating (not needed as tblSupervisor hold this info).
Changed tblOSHA FK to EmpIDFK (if this is OSHA training for employees, seems to make sense that it one be a one to many relationshio, tblEmp - tblOSHA).
Changed tblEmployeeInfo JobNumber field to a number field (lookups are generally a bad idea).
Deleted JobNum from tblEmployeeInfo (you had JobNumber and JobNum, assuming this was duplication of purpose).
Changed tblRating Code field to a number field.
Deleted SkillCode from tblRating
Created qryEmployee
Changed record source for frmEmployeeTraining to qryEmployee
Created qryJoinEmpRating&Supervisor
Changed record source for Skill Assessment subform to qryJoinEmpRating&Supervisor
Created qryJoinEmployeeInfo&Jobs
Changed record source of OSHA subform to qryJoinEmployeeInfo&Jobs

If you have any questions, feel free to post, and I'll try to check back as time permits.

 

[KristenD]

Thank you!

As tblEmployeeInfo and tblOsha are both blank, I'm struggling a bit to determine their exact purpose. I have a feeling that tblEmployeeInfo could be condensed into tblEmp, but it could be that I am not understanding what you're trying to capture with this table?

For tblOSHA, I am tracking their OSHA training. Since it is the nature of our industry (construction) to have employees be laid off and rehired due to the work of the projects, some projects may require OSHA training. So as a company, we made it a prerequisite to have the OSHA 10 training before being rehired or transferred to another job. The purpose of this is to have the hiring manager be able to go into the database and make sure that a specific employee does have the training before being rehired or transferred to another project.
For tblEmpInfo, is to track all active employees on the jobs to make sure we have the correct paperwork (the cards) to able to submit for certain jobsites. So this table is predicated on tblEmployee, and the field EmpStatus. Currently this is being tracked through an excel spreadsheet where I then have to do a compare and merge with another spreadsheet to make sure all employees that are active are being tracked. This table and form will change weekly as employees come and go as I said before it's the nature of the industry.

I am not entering any data through the tables, but I am trying to figure out the most logical way to get the info into the tables for the reports and queries as the front end for the users will only have access to the reports and queries. I did on the test database take out quite a few queries as like you said they were all displaying pretty much the same info.

 

[KristenD]

I really like what you did...and understand it a little better. The tables make a little bit more sense than before.

I did however change some of the tables only because I really think the tblOSHA and tblEmpInfo need to be related somehow. I created a junction table but still kept tblOSHA connected with tblEmp on its own.

I'm not sure if that is correct way of doing it. But it is for only one report that I need that information.

Mostly it will just be running reports to make sure that employee's have the training and the correct documentation.

Please let me know if that is not the correct way of getting the information. I just thought that the tables needed to be related.

 

[Adam Caramon]

I did however change some of the tables only because I really think the tblOSHA and tblEmpInfo need to be related somehow. I created a junction table but still kept tblOSHA connected with tblEmp on its own.

No, they do not need to be connected in this way. Any report that you want to run you would base on a query that pulls in tblEmp, tblOSHA, and tblEmployeeInfo. As tblEmp is related to both of the other tables, you can access the data needed. At that point, it is just a matter of setting the right query criteria to have a report produce the right information.

Mostly it will just be running reports to make sure that employee's have the training and the correct documentation.

If you have an example of a report that you need, I can explain how to set it up. Keep in mind, however, that without data in tblOSHA or tblEmployeeInfo, it is going to be difficult to reproduce necessary results.

 

[KristenD]

No, they do not need to be connected in this way.

Ok, I understand. As long as the EmpID is in both tables it should be able to pull the info needed.

If you have an example of a report that you need, I can explain how to set it up

I have attached the Excel Report that I am currently using but would like to be able to pull from Access once the data is entered. It doesn't necessarily have to look like this but it should be separated by job then employee.