Field value related to another field in another table?

Question

Field value related to another field in another table?

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

1

In MS Access I have two tables: tbCourses and tbPeople
tbPeople contain: ID , Nome , CursoID , CursoNome ;
tbCourses contains: ID , Nome ;

Is it possible to make the field CursoNome of the table tbPeople have the same value of the course name of the other table according to the value of CursoID ?

tbPeople :

tbCourses :

In the two images above, CursoNome is 'Right' because the value of CursoID is 2.

The question is, how do I change the field CursoID by changing the value of the CursoNome field according to the contents of the other table?

sql tabela ms-access

asked by anonymous 28.10.2015 / 02:53

3 answers

Instantiate an external directory Java class in JSP Problems inserting a datatime record

score 2 · Answer 1

A database consists of several tables, such as: Customers, Products, Orders, Order Details, etc. Although the information is separate in each of the Tables, in practice there must be relationships between the tables. For example: An Order is made by a Customer and in this Order there may be several items, items that are recorded in the Order Details table. In addition, each Order has a unique number (Order Code), but the same Customer can make several orders and so on.

In a database, we need some way to represent these real-life relationships in terms of the tables and their attributes. This is possible with the use of "Relationships between tables", which can be of three types:

One to One
One for Various
Various for Various

Type One-to-One Relationship:

This relationship exists when the fields that are related are both of the Primary Key type, in their respective tables. Each field has no repeated values. In practice there are few situations where we will use a relationship of this type. An example could be as follows: Imagine a school with a Student Master in the Students table, of which only a small part participates in the School Band. For reasons of database design, we can create a Second Table "Band Students", which relates to the Students table through a relationship of type One to One. Each student is only registered once in the Students Table and one only in the Band Students table. We could use the Student Enrollment Field as the Field that relates the two Tables.

Important: The field that lists two tables must be part of the structure of the two tables.

In the Band Students table, we could only place the student's Enrollment Number, as well as the information about the Instrument it plays, band time, etc. When it is necessary to search the information such as name, address, etc., these can be retrieved through the existing relationship between the two tables, thus avoiding that the same information (Name, Address, etc.) must be duplicated in the two tables , including increasing the likelihood of typing errors.

One-to-Many Relationship:

This is certainly the most common type of relationship between two tables. One of the tables (the one side of the relationship) has one field that is the Primary Key and the other table (the multiple side) relates through a field whose related values can be repeated several times.

Consider the example between the Customers and Orders table. Each Client is only registered once in the Customers table (so the Customer Code field in the Customers table is a primary key, indicating that two customers with the same code can not be registered), therefore the Customers table will be the side of the relationship. At the same time, each customer can make several requests, so the same Customer Code may appear several times in the Orders table: as many times as the customer requests. That is why we have a One to Many relationship between the Customers and Orders table, through the Customer Code field, indicating that the same Customer can make several (multiple) orders.

Multiple-to-Many type relationship:

This type of relationship would "happen" in a situation where on both sides of the relationship values could be repeated. Let's consider the case between Products and Orders. I may have Multiple Orders in which a particular product appears, in addition several Products may appear in the same Order. This is a situation where we have a Multiple-to-Many Type Relationship.

In practice it is not possible to implement such a relationship, due to a number of problems that would be introduced in the database model. For example, in the Orders table we would have to repeat the Order Number, Customer Name, Employee Name, Order Date, etc. for each Order item.

Here a good PDF for you to use some examples!

score 1 · Answer 2

I do not know if it helps a lot, but the situation you described, in real database environment ( Postgre ) for example, I would do trigger (trigger), after insert or update in table tbPessoas e this trigger would create a specific function for this field ( IF old.campo <> new.campo ) would do an update looking for the related course.

In Access, I do not know if I can create "trigger / triggers", but search so I have a way to believe that a trigger solves your problem. Example of trigger in postgre: Example Trigger

I hope I have helped!

score 0 · Answer 3

You should not do this. If it was a NoSQL database, it would even make sense, but in a relational database, it's certainly doing something wrong.

If you want the list of people and which courses they are applying, use JOIN :

SELECT *
  FROM tbPessoas P
  JOIN tbCurso C ON C.ID = P.CursoID

This will result in a query the way you expect.

And remove the CursoNome column from the tbPeople table, this is data redundancy, therefore, bad practice. Oh, and it also removes tb from the prefix of table names. It is not necessary and it is also another bad practice.