Help with subform datasheet view looks good(the way I designed); but the form view in my main form looks like a datasheet. (1 Viewer)

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
I will create a emp_training table as you described to me yesterday. my certificates is just a check box letting me know I received their certificate.
 

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
What is the value of "Default View" property?
What is the value of "Allow Datasheet View"?
What is the value of "Allow Form View"?

I did add the Driver lic to the main form and deleted that table.

By what you sent me yesterday telling me to set up empl table with foreign keys.

Should that new table have a primary key? Or should one of these FK be the primary key. The Required traing table only has the trainings that al staff are required to take. I do have an excel spread sheet (large) tracking all trainings staff take. All staff take several trainings - this is an ongoing all the time. I am taking a training next week and I want my database to be set up correctly before my training because I really need help on linking my excel to my database and I do know that my excell is not set up correctly for my database currently. In my training I really want to focus more on that. I believe I will need to renter my data in my excel sheet and that will be a very huge undertaking. Some of our staff takk over hundred hours of trainings. so there is a lot of data that I will need to revamp. Eventually I want total trainings from my exel sheet and there are 9 categories that each staff need to fill also. So I want Total hours and how may hours in each category to appear on each staff's training form. So that 's the overview as to what I need the whole database to do for me. Please don't give on me... You have been of more help than of anyone I've asked. I so do appreciate this so much!!!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:46
Joined
May 21, 2018
Messages
3,620
I think I understand what you're saying however I'm not so sure how to go about fixing it. A PK needs to have a number in it - no blanks.
That is correct. Each row in a table should uniquely describe a single entity. So every identity needs a unique identifier
How about a FK - there doesn't need to be data in that field at all times?
That is correct, but at times your buisness rules may require it to ensure data integrity.

Your drivers license is a little bit of an exception so it is not a great example. And your training is a many to many which is also a little more advanced so not a great example.
Normally there is a One to Many relationship. The FK is on the many side. So assume you have tblEmployees and each Employee has a unique ID. Assume you have hundred of Client Accounts that employees are respsonsible for. So you have a table of accounts that have detailed info about that account and an FK that says which Employee links to that account. In the account table there should be no Employee info just an FK to that employee. Your buisiness rules may be that at no time can a account not be assigned to an employee so you would enforce referential integrity. Or your rule may be that accounts can be unassigned awaiting assignment. Then you do not enforce referential integrity.

Assume you keep info on the family members of an employee as previously described. You enter a family member in the family member table and each record has an FK to the employee. In this case it makes no sense to have a family member in your db that does not relate to an employee. In fact you would enforce referential integrity and set cascade deletes. If John Smith gets fired you delete him from your db and delete his family members automatically.

So the driver license is a little unique in your case and this is a judgement call not a hard rule. If most of your people will eventually have a drivers license in the db and they can only have one drivers license then this information goes directly into the employee table. It uniquely describes an employee like birthday, name, etc.

If they can have more than 1 license then it has to go in a license table. That would be a traditional With a foreign key to the employee. In this case you have to ensure their is an FK, because having a license not related to a person makes no sense. You would not track random licenses. However there will not be a record for someone without a license

If most of the employees will not have a drivers license and employees could have one and only one then you could have a seperate table for licenses. This would be a little more compact because then you do not have a lot of empty fields in your employee table for all the employees without licenses. However this makes it a little more complicated in form design, but not a lot. This is really a judgement call. If 5% have licenses then I am making a seperate table, if 90% have licenses then I am putting it in the employee table.
 

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
That is correct. Each row in a table should uniquely describe a single entity. So every identity needs a unique identifier

That is correct, but at times your buisness rules may require it to ensure data integrity.

Your drivers license is a little bit of an exception so it is not a great example. And your training is a many to many which is also a little more advanced so not a great example.
Normally there is a One to Many relationship. The FK is on the many side. So assume you have tblEmployees and each Employee has a unique ID. Assume you have hundred of Client Accounts that employees are respsonsible for. So you have a table of accounts that have detailed info about that account and an FK that says which Employee links to that account. In the account table there should be no Employee info just an FK to that employee. Your buisiness rules may be that at no time can a account not be assigned to an employee so you would enforce referential integrity. Or your rule may be that accounts can be unassigned awaiting assignment. Then you do not enforce referential integrity.

Assume you keep info on the family members of an employee as previously described. You enter a family member in the family member table and each record has an FK to the employee. In this case it makes no sense to have a family member in your db that does not relate to an employee. In fact you would enforce referential integrity and set cascade deletes. If John Smith gets fired you delete him from your db and delete his family members automatically.

So the driver license is a little unique in your case and this is a judgement call not a hard rule. If most of your people will eventually have a drivers license in the db and they can only have one drivers license then this information goes directly into the employee table. It uniquely describes an employee like birthday, name, etc.

If they can have more than 1 license then it has to go in a license table. That would be a traditional With a foreign key to the employee. In this case you have to ensure their is an FK, because having a license not related to a person makes no sense. You would not track random licenses. However there will not be a record for someone without a license

If most of the employees will not have a drivers license and employees could have one and only one then you could have a seperate table for licenses. This would be a little more compact because then you do not have a lot of empty fields in your employee table for all the employees without licenses. However this makes it a little more complicated in form design, but not a lot. This is really a judgement call. If 5% have licenses then I am making a separate table, if 90% have licenses then I am putting it in the employee table.
Right now only about 1/3 of employees have driver's licenses - only one. but we are getting more so eventually most if not all will have a driver license in my database. so I will keep the license in the staff main table.
 

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
Right now only about 1/3 of employees have driver's licenses - only one. but we are getting more so eventually most if not all will have a driver license in my database. so I will keep the license in the staff main table.
In the table relationships I sent you yesterday. You did give me suffestion to create a employee training table with EmpID_FK / TrainngID_FK / TrainingDate / Certificate (checked if received). How do I mark the FK's - is there a PK that I will need to add to this table?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:46
Joined
May 21, 2018
Messages
3,620
Take a look at this demo. It uses the many to many for employee training. In the Employee table there is a FK for security level. This demos a one to many. One security level can be associated with many employees. The table with an FK is on the many side. The same table can be on the many side to another table and can be on the one side of a different table. If I added an Employee family table then the records in that table would have an FK back to the employee table.

The many to many is an advanced concept, but once you see it it should make sense.
 

Attachments

  • TrainingDB.accdb
    1.1 MB · Views: 15

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
Take a read on this
It discusses relationships and joins.
Ok...going through everything you sent me. I am restructuring my database somewhat. I see you you have in emptble a primary key (automatic) and in the trainingtbl you have trainingID as primary key (automatic). My PK is employee Numbers (not automatic). so would I use a different PK in training table (automatic) or do I need to have the employee # remain in the training table? Also, would it be easier to have my primary key as automatic and also use employee number as FK (they are never duplicated) would this be better for table relationships. I hope I'm not confusing you with all my babble...I'm really trying to get this database working topnotch before I use it to the hilt.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:46
Joined
May 21, 2018
Messages
3,620
If you have a natural key (required, unique, does not change, and known at creation of the record) then you can use a natural key. Employee number would be a fine natural. It is unique, and should never change, and you should know it when you create a record. It does not have to be automatic, but you can still have an autonumber PK and the Employee number not as a PK if you wanted. But in the related table you have to link to the PK whatever it is. So if you use Emp Number in the Emp table the related tables would have the EMP_Number_FK as the foreign key. You can never go wrong with autonumbers though. They are the simplest and always work. An example would be using an Emp Number. You go to enter a new employee at HR, but they have to wait for corporate to provide the Emp Number. Now you cannot create the record until that number is recieved. Or you would have to put a fake number and then update once the real number is recieved. Autonumbers do not have that problem.

If you can post your tables without data or sample only data it may help. If you learn well from visual vice written, you may want to google some videos on creating table relations. Sometimes seeing it helps.
 

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
If you have a natural key (required, unique, does not change, and known at creation of the record) then you can use a natural key. Employee number would be a fine natural. It is unique, and should never change, and you should know it when you create a record. It does not have to be automatic, but you can still have an autonumber PK and the Employee number not as a PK if you wanted. But in the related table you have to link to the PK whatever it is. So if you use Emp Number in the Emp table the related tables would have the EMP_Number_FK as the foreign key. You can never go wrong with autonumbers though. They are the simplest and always work. An example would be using an Emp Number. You go to enter a new employee at HR, but they have to wait for corporate to provide the Emp Number. Now you cannot create the record until that number is recieved. Or you would have to put a fake number and then update once the real number is recieved. Autonumbers do not have that problem.

If you can post your tables without data or sample only data it may help. If you learn well from visual vice written, you may want to google some videos on creating table relations. Sometimes seeing it helps.
I want to thank you so much with all this help. I have created some new tables and I will see how they work. If I use auto number then the FK can be employee number for relations for the Employee table PK(auto) to relation to Employee number (which I will key in) on the training table?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:46
Joined
May 21, 2018
Messages
3,620
(which I will key in) on the training table?
You rarely ever key in the FK. Normally you choose a value from a combobox or you have a subform. Imagine you have an employee form with a family member subform. When you add a family member in the subform the link happens automatically.

Or often you choose a value from the combobox. You normally show a readable value but store the FK. Look at the security level combobox. I show admin, editor but store the actual PK for security level into the FK of the employee table. This is done with a little trickery of hiding the value you plan to store.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:46
Joined
Feb 19, 2013
Messages
12,576
Just my opinion, but one of the issues with natural keys is that of confidentiality. You need to store the value somewhere - but is it a good idea to store it multiple times in other tables as a FK which you would have to do if the natural key is the primary key?
 

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
Just my opinion, but one of the issues with natural keys is that of confidentiality. You need to store the value somewhere - but is it a good idea to store it multiple times in other tables as a FK which you would have to do if the natural key is the primary key?
I want to thank you so much for all the help you've given me the last couple days. After following your table relationship in the example you gave me, that did it. I was very confused when I started but I did eventually get. I had to redo 2 of my tables - keying all information in (I've been up all night trying to get all the data into the tables I had to redo, but I'm getting there. I just wanted to let you know how much I appreciated all you did for me.
 

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
Just my opinion, but one of the issues with natural keys is that of confidentiality. You need to store the value somewhere - but is it a good idea to store it multiple times in other tables as a FK which you would have to do if the natural key is the primary key?
Thank you!!! I finally got that too. I'm slow at learning because I'm a very visual person, but once I get it - I have it!!!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:46
Joined
May 21, 2018
Messages
3,620
I had to redo 2 of my tables - keying all information in (I've been up all night trying to get all the data into the tables I had to redo, but I'm getting there.
Sorry, you should have came here first. Probably could have saved you a ton of time. There are a lot of tricks to do insert queries so you can automate moving data from one format to a more normalized format. Often this is done with a "normalizing query" where you take data in multiple fields and put this in a more traditional single column format.
 

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
Sorry, you should have came here first. Probably could have saved you a ton of time. There are a lot of tricks to do insert queries so you can automate moving data from one format to a more normalized format. Often this is done with a "normalizing query" where you take data in multiple fields and put this in a more traditional single column format.
I just got table relationship. I can't take "normalizing query" right now...LMAO!!! My next big project is to figure out how I'm going to do my training log. I have it in excel and I want to keep it there (I think) and link it to the database. However, I set it up years ago and it will not work with access the way it stands now. that will take me a log time to up date that...YIKES!!!
 

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
we help where we can - there may be ways that might make that easier
Let me send you a small sample of what my excel sheets look like. Maybe by looking at it you may be able to help me find a way to link it to my database and calculate all total hours into my database. I will send you about 5 employees to let you know what I'm working with. Keep in mind once I send you a sample, I have a total of 140 employees who have the same tracking.
 

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
we help where we can - there may be ways that might make that easier
we help where we can - there may be ways that might make that easier
Ok you asked fo rit. As I was breaking this tracking for you to see - I was thinking I probably have to mput that training categories for each staff indiviually. Well...Yu look at this and you'll see what I mean. also, remember at this point I have 140 staff to my actual list.
 

sribblett

Member
Local time
Today, 06:46
Joined
Aug 24, 2020
Messages
89
Sorry, you should have came here first. Probably could have saved you a ton of time. There are a lot of tricks to do insert queries so you can automate moving data from one format to a more normalized format. Often this is done with a "normalizing query" where you take data in multiple fields and put this in a more traditional single column format.
You are right about that. You have been so helpful. I have been looking trying to get help and I just came acros this forum. I am so glad that I had this help rather than further down the line.
 

Users who are viewing this thread

Top Bottom