Best way to insert Foreign key into tables in access form (1 Viewer)

Ihk

Member
Local time
Tomorrow, 00:38
Joined
Apr 7, 2020
Messages
280
Hi, I am learner have very basic question.
Correct me if I am wrong,
As I am experiencing more, I came to know also felt that Look Up field in a table from another table is not a good idea, it causes problem later and restricts.
While tables are linked only through relationship, one to multi with enforced referential integrity. Arrow pointing to a table with infinite sign, will have foreign key.
I have seen very good database tables have so many foreign IDs, for example picture below. This is school management system table of students. It contains student Name and so many foreign IDs.
1625604769629.png

I only know to insert this via when table fields are connected via look up fields from another table. But it causes problem later on.
Question is How these foreign should be inserted? what is the best way now days for a complex database?
For example: from a form User will information like
Student Name
Class Name
Last School name
Nationality
etc
Question is normal user is entering the text, and this text as above fields will go into relevant table and Student name will go into student table above, but How foreign keys of other controls will be entered into this student table automatically, while user was entering fields like last school name, class name etc. How foreign automatically should be inserted in access table? Best way for my future learning.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:38
Joined
Oct 29, 2018
Messages
21,358
Hi. The normal way to enter foreign keys in a table is by using a form/subform setup. Have you tried that?
 

Ihk

Member
Local time
Tomorrow, 00:38
Joined
Apr 7, 2020
Messages
280
Hi. The normal way to enter foreign keys in a table is by using a form/subform setup. Have you tried that?
hi, no I have not tried subform. Till now I inserted real text from one table to other table as foreign key (enforced) via form, for example like combo boxes. with respective row source and control source. then sometimes it causes problem, instead of real text, ID number field appears then I have to change the bound column number, it works that way. As I am learning, I felt this is not the best way. I saw many databases as above containing so many ID fields as foreign keys.
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:38
Joined
Jan 23, 2006
Messages
15,364
I recommend that you start with the description of the business/processes involved in your school management system.
I am suggesting that you work through this tutorial from RogersAccessLibrary to experience a procedure for identifying tables and relationships, and primary keys and foreign keys. It should take about 45-60 minutes and may be the best 60 minutes to learn and understand Normalization. And what you learn/experience can be used with your application.
The Database Planning and Design link in my signature has many articles in different formats that may be helpful to you.
Good luck with your project.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:38
Joined
Jul 9, 2003
Messages
16,245
hi, no I have not tried subform.

You might find my example and videos useful on my website here:-


It says "many to many" relationship but that's a bit of a red herring because a "many-to-many" is basically two subforms two forms setup sort of as a mirror of each other for want of a better description!
 

plog

Banishment Pending
Local time
Today, 18:38
Joined
May 11, 2011
Messages
11,613
Within the context given, the correct way to insert foreign key into a table is via a drop down. Let's take Students.ID_Guardian as an example.

On the student form you are allowing 1 guardian per student. You would have a drop down on the form to show all the names of the guardians in the Guardians table. However, the comboboxes control source would work with the ID field of the Guardians table. To the user it will be a name, to the database it will be the number. Here's a link on how to do that:


With that said, your Students table is overly normalized. New database users who just read up on normalization make this mistake often. They make every field numeric and make a table for every set of values. This is wrong.

Take ID_Religion as an example. My guess is that the Religion table has 2 fields--an autonumber primary key and then a text field which holds the actual value (Jewish, Christian, Spaghetti Monsterian, etc.). Instead, you change [ID_Religion] to [Religion] and store the actual text value in their instead of having a whole table for it.

I fear a lot of your fields in STudents are like this.
 
  • Love
Reactions: Ihk

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:38
Joined
Feb 19, 2002
Messages
42,981
Maybe the advice on lookups wasn't clear.

Lookups at the table level -- bad
Lookups on forms -- good.

Each of these FK's would be entered by choosing an option from a combo box. You choose the option Norwich (for place of birth) and 387 gets saved as the FK. The combo on the form shows Norwich. The table shows 387 (or whatever). To see the strings in a query, you would join the main table to the lookup table to select the string value. One caveat though. Unless the FK is required, it is best to use a left join rather than an inner join or you will loose records that have null values in that particular field.
 
  • Love
Reactions: Ihk

Ihk

Member
Local time
Tomorrow, 00:38
Joined
Apr 7, 2020
Messages
280
Within the context given, the correct way to insert foreign key into a table is via a drop down. Let's take Students.ID_Guardian as an example.

On the student form you are allowing 1 guardian per student. You would have a drop down on the form to show all the names of the guardians in the Guardians table. However, the comboboxes control source would work with the ID field of the Guardians table. To the user it will be a name, to the database it will be the number. Here's a link on how to do that:


With that said, your Students table is overly normalized. New database users who just read up on normalization make this mistake often. They make every field numeric and make a table for every set of values. This is wrong.

Take ID_Religion as an example. My guess is that the Religion table has 2 fields--an autonumber primary key and then a text field which holds the actual value (Jewish, Christian, Spaghetti Monsterian, etc.). Instead, you change [ID_Religion] to [Religion] and store the actual text value in their instead of having a whole table for it.

I fear a lot of your fields in STudents are like this.
Thank you very much for this explanation. I will consider this.
 

Ihk

Member
Local time
Tomorrow, 00:38
Joined
Apr 7, 2020
Messages
280
Maybe the advice on lookups wasn't clear.

Lookups at the table level -- bad
Lookups on forms -- good.

Each of these FK's would be entered by choosing an option from a combo box. You choose the option Norwich (for place of birth) and 387 gets saved as the FK. The combo on the form shows Norwich. The table shows 387 (or whatever). To see the strings in a query, you would join the main table to the lookup table to select the string value. One caveat though. Unless the FK is required, it is best to use a left join rather than an inner join or you will loose records that have null values in that particular field.
Dear @Pat Hartman , Yes now a days I have started using look up on forms. This gives more options.
I understand the concept, nicely explained. But In case if user wants to see all data in a tabulated shape (continuous form), with search and filter functionality. Because of this reason, From combo box instead of entering FK as ID number value, I change combo box bound column number, so in the table, this value also enters as real text value. (though in relation is foreign). Is this good practice for database?
I hope you got what I wanted to explain.
Will it be really necessary to enter FK as numeric value (for good database design), and not to change bound column.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:38
Joined
Feb 19, 2002
Messages
42,981
I didn't want to say anything about the table you posted because it wasn't clear to me whether it was actually from your database or a sample you picked up but, I have opinions:)

1. Guardian and Parent do not belong in the Student table. A student can have more than one parent and perhaps more than one guardian. I would put them in a table named tblStudentRelationships
2. Prefixing "ID" fields with ID_ doesn't do anything productive. It obscures the actual column names in the picture you posted by using the leading three characters for meaningless "noise". Using ID as a suffix is productive though since it makes it clear which fields are used in relationships and it doesn't interfere with intellisense or reading the names of the columns in DS view.
 

Ihk

Member
Local time
Tomorrow, 00:38
Joined
Apr 7, 2020
Messages
280
I didn't want to say anything about the table you posted because it wasn't clear to me whether it was actually from your database or a sample you picked up but, I have opinions:)

1. Guardian and Parent do not belong in the Student table. A student can have more than one parent and perhaps more than one guardian. I would put them in a table named tblStudentRelationships
2. Prefixing "ID" fields with ID_ doesn't do anything productive. It obscures the actual column names in the picture you posted by using the leading three characters for meaningless "noise". Using ID as a suffix is productive though since it makes it clear which fields are used in relationships and it doesn't interfere with intellisense or reading the names of the columns in DS view.
Thank you for your quick response.
1st of all, my main question of the post is answered, which you explained it in nice way with example.
But in my previous reply this was not my point. Yes this table just picked up table, not my table, just example.
May be I was not able explain, What I wanted to say in above reply. As you nicely explained, how Foreign entered into table via combo box, perfectly understood.
As you explained, through forms while tries to enter value, he see text (Norwich (for place of birth) and table gets 387 ID.

My question is, in this situation, if change combobox bound column number, so that instead Foriegn ID 387 entering int the table, the real string (Norwich), enter in the table as Foreign key. Because actually as per relationship, it will be foreign key. To do like this, will it be good practice. Meaning changing the bound column number of combo box.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:38
Joined
Feb 19, 2002
Messages
42,981
From combo box instead of entering FK as ID number value, I change combo box bound column number, so in the table, this value also enters as real text value. (though in relation is foreign). Is this good practice for database?
NO, and in fact, it is dangerous if you have not set the AllowEdits property of the form to No. It is not necessary if you are using Continuous or Data Sheet view. Both of those form views respect combos.

Technically, it isn't necessary for the lookups to be numeric. The reason we use two separate fields - a numeric ID and a text display field is so that if we change the value we want to display, we do NOT have to update all the related records to change the text value. It is changed only once in the definition table. Another question might be, why do I have to use tables for all my lookups, can't I just use combos with lists? Well you could BUT, if you use the list in multiple places, you would always have to keep track of all the places you needed to change if you wanted to add a value.

Since we are on this topic, I'm going to attach a mini-application I have been using since the early 80's. But Access didn't even exist then!!! Yep, the first implementation of this concept was COBOL with IMS as the database. Later, it was COBOL with DB2 (similar to SQL Server) and the most recent was Access. This example uses Jet/ACE tables but most of my apps have the data in SQL Server so if you ever want to do that, just convert the tables to SQL Server. I built the app because it is a real PITA to manage lists of values embedded in program code (COBOL) or in control Properties (Access). Also, with embedded lists, YOU, the programmer, always need to get involved in changes. Over time all lists change. A couple of years ago, you might have thought Male, Female, Unspecified would have been sufficient for Gender but you would have been wrong.

At first I thought of making a separate table for each list but that get's a little crazy when you have more than a few. It also requires separate forms to manage each list. So I settled on a two table concept. The parent table is a table of "tables" and the child table is the contents of each "table". This means that I can have hundreds of lookup tables and still use a single set of forms/reports/tables/queries to manage them.

I also attached a zip file with a PPT in it that explains some of the details
 

Attachments

  • TableMaintExample20210204.zip
    196 KB · Views: 547
  • Code Table Maintenance20210204PPT.zip
    188.4 KB · Views: 519

Ihk

Member
Local time
Tomorrow, 00:38
Joined
Apr 7, 2020
Messages
280
Within the context given, the correct way to insert foreign key into a table is via a drop down. Let's take Students.ID_Guardian as an example.

On the student form you are allowing 1 guardian per student. You would have a drop down on the form to show all the names of the guardians in the Guardians table. However, the comboboxes control source would work with the ID field of the Guardians table. To the user it will be a name, to the database it will be the number. Here's a link on how to do that:


With that said, your Students table is overly normalized. New database users who just read up on normalization make this mistake often. They make every field numeric and make a table for every set of values. This is wrong.

Take ID_Religion as an example. My guess is that the Religion table has 2 fields--an autonumber primary key and then a text field which holds the actual value (Jewish, Christian, Spaghetti Monsterian, etc.). Instead, you change [ID_Religion] to [Religion] and store the actual text value in their instead of having a whole table for it.

I fear a lot of your fields in STudents are like this.
Nicely explained. ID_religion has its table because user wants to have different options to select from religions. Now I have clear concept, how foreign key are inserted in a table from a form. In combobox property, there is option to change the bound column number, if change that I was able insert string/ text of source table into control source. (this string is actually foreign for this table). I am afraid this practice may be wrong, better is not to change change bound column of combo and add real nemeric foreign key. because later if want to change parent data, from source table then it will be easy to handle, as @Pat Hartman said.
That means foreign as numeric is always better.

Thank you very much. Today I learnt a lot.
 

Ihk

Member
Local time
Tomorrow, 00:38
Joined
Apr 7, 2020
Messages
280
NO, and in fact, it is dangerous if you have not set the AllowEdits property of the form to No. It is not necessary if you are using Continuous or Data Sheet view. Both of those form views respect combos.

Technically, it isn't necessary for the lookups to be numeric. The reason we use two separate fields - a numeric ID and a text display field is so that if we change the value we want to display, we do NOT have to update all the related records to change the text value. It is changed only once in the definition table. Another question might be, why do I have to use tables for all my lookups, can't I just use combos with lists? Well you could BUT, if you use the list in multiple places, you would always have to keep track of all the places you needed to change if you wanted to add a value.

Since we are on this topic, I'm going to attach a mini-application I have been using since the early 80's. But Access didn't even exist then!!! Yep, the first implementation of this concept was COBOL with IMS as the database. Later, it was COBOL with DB2 (similar to SQL Server) and the most recent was Access. This example uses Jet/ACE tables but most of my apps have the data in SQL Server so if you ever want to do that, just convert the tables to SQL Server. I built the app because it is a real PITA to manage lists of values embedded in program code (COBOL) or in control Properties (Access). Also, with embedded lists, YOU, the programmer, always need to get involved in changes. Over time all lists change. A couple of years ago, you might have thought Male, Female, Unspecified would have been sufficient for Gender but you would have been wrong.

At first I thought of making a separate table for each list but that get's a little crazy when you have more than a few. It also requires separate forms to manage each list. So I settled on a two table concept. The parent table is a table of "tables" and the child table is the contents of each "table". This means that I can have hundreds of lookup tables and still use a single set of forms/reports/tables/queries to manage them.

I also attached a zip file with a PPT in it that explains some of the details
Thank you very much, I learnt a lot today. very kind you. I will look into this app.
 

JMongi

Active member
Local time
Today, 19:38
Joined
Jan 6, 2021
Messages
802
@Pat Hartman - I know you post your sample table code db in a lot of different threads, but this is the first one where I saw the power point slide giving some extra details.

Really appreciate it, this will simplify my app and let me add even more lookup tables without grumbling so much. :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:38
Joined
Feb 19, 2002
Messages
42,981
Glad you found it helpful. As I said, it goes in ALL my apps even ones that need only a couple of lookups.
 

Users who are viewing this thread

Top Bottom