Relationships between tables in access (3 Viewers)

[edo17]

Hi everyone, I have a question about relationships in Access. I'm new here and I just started using the program and I'm working on a small database with three tables. Table 1 and Table 2 have a field in common (suppose ID) and I want to understand the relationship between these two tables. Table 1 has 48 records with no duplicates in this field (ID). Table 2 has 46 records with no duplicates in the field (ID). Here some questions, please correct me if my reasoning is wrong. If the field doesn't have duplicates (both in Table 1 and Table 2) is impossible that a one-to-many / many-to-one relationship exists between these two tables, right? So, the only possible solutions should be a one-to-one relationship but in that case the two tables should have the exact same number of records, is that right? So in that scenario what's the relationship between Table 1 and Table 2? Thanks to everyone who will help me! Have a great Saturday!

 

[MajP]

Impossible to answer as phrased.
Can you give some notional non database explanation of how the information is related?
Example. Table Employees and Table Orders. An employee is responsible for multiple orders and only one employee is responsible.

True One to One tables do not exist often. These are cases where you relate a PK to a PK. Because if they are then they can often be combined into a single table. FYI, to make your life easier and people helping uniquely name your ID fields. EmployeeID, OrderID, StatusID ....
I make it easier on myself to then name the foreign key in another table with "FK" ."StatusID_FK" that realtes back to the status table.

 

[The_Doc_Man]

MajP has quite correctly noted that a true 1/1 relationship is very rare. Most of the time, if you HAD a true 1/1 case, it becomes mechanically difficult to manage because of issues in trying to create records in two tables at once (in order to avoid "orphan" record creation). Not impossible, but difficult. The theory behind 1/1 relationships also becomes a quagmire since true 1/1 tables are really just a subdivided larger single table with duplicated prime keys to identify the matching subdivisions. If you don't treat the two tables that way, you can have serious logic issues down the road. (Access isn't the only DB utility that has this trouble, by the way.)

Since you say you have no dups in either table, it LOOKS like it should be 1/1,... but it can't be. IF you want to turn on relational integrity in a declared 1/1 formal relationship for your case, Access would say that you have "orphans" in either table. The "establish RI" operation would fail. The reason is simple: Your 1/? relationship is not 1/1.

The way out of this is to realize that 1/many COULD simply mean "1/(0 or more)" so this is a 1/many relationship. What this REALLY means in practical terms is that you can never do anything with an INNER JOIN query UNLESS you only wanted to deal with the matched records. Otherwise, all your queries will be 1/many - and Access doesn't "care" if they are 1/many or many/1 situations.

In a query based on 1/many relationships, whichever table has the "1" side is the independent table and the one that COULD have no matches is the dependent table. But if these mismatches occur in BOTH directions, you might end up with TWO relationships, each 1/many, but starting from a different table as the independent side. Your real difficulty would then be that in a simple query there would be records you will miss where the DEPENDENT table has no matching record in the INDEPENDENT table, whichever side is which. It would take either TWO queries or a complex UNION query to show you all of your records.

We might be able to offer better guidance if we knew something about the real-world situation these two tables represent.

 

[GPGeorge]

Hi everyone, I have a question about relationships in Access. I'm new here and I just started using the program and I'm working on a small database with three tables. Table 1 and Table 2 have a field in common (suppose ID) and I want to understand the relationship between these two tables. Table 1 has 4998 records with no duplicates in this field (ID). Table 2 has 4939 records with no duplicates in the field (ID). Here some questions, please correct me if my reasoning is wrong. If the field doesn't have duplicates (both in Table 1 and Table 2) is impossible that a one-to-many / many-to-one relationship exists between these two tables, right? So, the only possible solutions should be a one-to-one relationship but in that case the two tables should have the exact same number of records, is that right? So in that scenario what's the relationship between Table 1 and Table 2? Thanks to everyone who will help me! Have a great Saturday!

Please don't hesitate to share the nature of the business being modelled by this database along with the actual entities for which you are creating tables. It can't hurt to be explicit, and can be really useful to those trying to offer help.

 

[MajP]

Table 2 has 4939 records with no duplicates in the field (ID). Here some questions, please correct me if my reasoning is wrong. If the field doesn't have duplicates (both in Table 1 and Table 2) is impossible that a one-to-many / many-to-one relationship exists between these two tables, right?

Definitely not impossible. IMO there is a difference between the structure and the data rules. People may disagree. You can build something that supports one to many, or many to many but apply rules limit it so you have only one item related to another item. If I build apartments that can hold up to 3 people but only one person just happens to live in an each apartment does not change the structure. But the landlord could enforce a policy where only one person is allowed.

Example
TblEmployees
--EmployeeID
--LastName
--FirstName
....

tblComputers
--CopmputerD
--Make
--Model
--EmployeeID_FK

The company has lots of money so people can get more than one computer. They can leave one at home and one in the office.
That is structurally a One to Many. You built it so that the employees can have many computers.
Then things are going bad and the company says only one computer per employee. You then can make the EmployeeID_FK unique. So you cannot assign more than one computer to an employee.
IMO that is not a one-to-one structural relationship but I am sure other would say that is.

 

[edo17]

I still cannot figure out what relationship exists between these two tables since if it were a one-to-many / many-to-one I would expect duplicates in the EmployeeID field in the first or second table. (For example, that a given Employee from the Employee Names Table holds two jobs and that this appears in the Payroll Table as two records with the same Employee ID and therefore there is a duplicate).
I'm sure I'm still not being clear enough but I've really just started using Access a few days ago and I really appreciate the answers you are giving me. Thanks everyone!

 

[GPGeorge]

Thank you for explaining the full context of the question, including the fact that it's an assignment for a university class. Perhaps a better source of assistance would have been the classroom teaching materials and the professor who assigned it to you.

 

[edo17]

Thank you for explaining the full context of the question, including the fact that it's an assignment for a university class. Perhaps a better source of assistance would have been the classroom teaching materials and the professor who assigned it to you.

Can I ask you some general questions? (Btw it's a non graded practice assignment that's the reason why I'm asking here and from the teaching materials I still can't understand). He explained to us that in a one-to-many relationship, assuming a simple case with 2 tables: 1) Each record in the first table can have many matching records 2) Each record in the second table can have only one marching record in the first table. Example:
tblCustomer.
Customer ID State
1 SC.
2. NY.
3. NC.

tblOrders
Order ID Customer ID
100. 1
101. 1
102. 2
103. 3
In that case that's a proper one-to-many relationship since customer 1 is responsible for two orders and consequently Customer Id 1 it's a duplicate in tblOrders. But I still can't understand how it's possible to have a one-to-many/ many-to-one if the field in common has no duplicates in both tables.

 

[mike60smart]

Can I ask you some general questions? (Btw it's a non graded practice assignment that's the reason why I'm asking here and from the teaching materials I still can't understand). He explained to us that in a one-to-many relationship, assuming a simple case with 2 tables: 1) Each record in the first table can have many matching records 2) Each record in the second table can have only one marching record in the first table. Example:
tblCustomer.
Customer ID State
1 SC.
2. NY.
3. NC.

tblOrders
Order ID Customer ID
100. 1
101. 1
102. 2
103. 3
In that case that's a proper one-to-many relationship since customer 1 is responsible for two orders and consequently Customer Id 1 it's a duplicate in tblOrders. But I still can't understand how it's possible to have a one-to-many/ many-to-one if the field in common has no duplicates in both tables.

Hi
Welcome to the Forum
Can you upload a copy of the database?

 

[GPGeorge]

I think you're focusing on the wrong factor in trying to evaluate one-to-many relationships on the basis of the presence or absence of duplicates in any field. That would be a result of actual data entry, rather than the nature of the relationship.

You could, in theory, have ten or ten thousand customers. Each customer could, in theory, purchase from you once and only once, or they could become loyal customers and purchase from you regularly. That's the nature of the relationship between you and your customers. It would be a very, very unusual business that decided to limit orders to one per customer. (Setting aside a specific promotion for a specific item that would be limited to one per customer for the purposes of that promotion.) In fact, the opposite is true, businesses thrive on repeat orders from customers.

So, the nature of the relationship determines the way the tables are set up. It is a one-to-many with the Primary Key of the Customer table being stored in the Foreign Key field in one or more Orders.

Now, it could be possible that the items you sell are horrible and customers decide they'll never buy from you again. In that case, as a consequence, you never get duplicate CustomerID's in the order table. That doesn't mean the relationship is not one-to-many. It means you have a lousy product to sell.

 

[MajP]

But I still can't understand how it's possible to have a one-to-many/ many-to-one if the field in common has no duplicates in both tables.

Customer ID State
1 SC.
2. NY.
3. NC.

tblOrders
Order ID Customer ID
100. 1
101. 5
102. 2
103. 3

The relationship is structurally one to many but as @GPGeorge stated there are no duplicates because of data entry.

That would be a result of actual data entry, rather than the nature of the relationship.

You could have an employee with many orders but so far you do not.

 

[MajP]

But if I was to put a unique requirement on CustomerID in table orders I can force it so that no record in Customer can have more than one order. I personally do not call that One-to-One, but others might. Your professor may disagree.
Normally we talk one to one where two tables have matching PKs. Done for security or other reasons. You could combine them into one table and each field would still uniquely identify a single entity.

But as your modified example shows you cannot just look at the data and see there are not duplicates thus one to one.

 

[Gasman]

Can I ask you some general questions? (Btw it's a non graded practice assignment that's the reason why I'm asking here and from the teaching materials I still can't understand). He explained to us that in a one-to-many relationship, assuming a simple case with 2 tables: 1) Each record in the first table can have many matching records 2) Each record in the second table can have only one marching record in the first table. Example:
tblCustomer.
Customer ID State
1 SC.
2. NY.
3. NC.

tblOrders
Order ID Customer ID
100. 1
101. 1
102. 2
103. 3
In that case that's a proper one-to-many relationship since customer 1 is responsible for two orders and consequently Customer Id 1 it's a duplicate in tblOrders. But I still can't understand how it's possible to have a one-to-many/ many-to-one if the field in common has no duplicates in both tables.

The one to many is a customer having many orders, so the customer pk is in many records in the order table as a fk. The order pk had to be unique to identify that, the same as the customer pk has to be unique to identify that.

BTW do not have spaces in table and field names. That will cause you so much extra work.

 

[edo17]

Hi
Welcome to the Forum
Can you upload a copy of the database?

Yes, here you go and here some doubts that I have.
1)Is it correct to use as PK EmployeeID in tblPayroll and in tblEmployeeNames and use AgencyID as PK in tbl AgencyNames?
2) What's the relationship between tblPayroll and tblEmployeeNames and between tblPayroll and tblAgencyNames?
IMO between tblPayroll and tblAgencyAgencyNames is a one to many since a single AgencyID can have different employees that are recorded in tblPayroll. For what everyone is saying above also between tblPayroll and tblEmployeeNames should be a one to many right? Thanks everyone for your help!

 

[edo17]

I think you're focusing on the wrong factor in trying to evaluate one-to-many relationships on the basis of the presence or absence of duplicates in any field. That would be a result of actual data entry, rather than the nature of the relationship.

You could, in theory, have ten or ten thousand customers. Each customer could, in theory, purchase from you once and only once, or they could become loyal customers and purchase from you regularly. That's the nature of the relationship between you and your customers. It would be a very, very unusual business that decided to limit orders to one per customer. (Setting aside a specific promotion for a specific item that would be limited to one per customer for the purposes of that promotion.) In fact, the opposite is true, businesses thrive on repeat orders from customers.

So, the nature of the relationship determines the way the tables are set up. It is a one-to-many with the Primary Key of the Customer table being stored in the Foreign Key field in one or more Orders.

Now, it could be possible that the items you sell are horrible and customers decide they'll never buy from you again. In that case, as a consequence, you never get duplicate CustomerID's in the order table. That doesn't mean the relationship is not one-to-many. It means you have a lousy product to sell.

Thank you George now it's more clear!

 

[edo17]

Customer ID State
1 SC.
2. NY.
3. NC.

tblOrders
Order ID Customer ID
100. 1
101. 5
102. 2
103. 3

The relationship is structurally one to many but as @GPGeorge stated there are no duplicates because of data entry.

You could have an employee with many orders but so far you do not.

Great so basically the relationship is independent from the subsequent data entry?

 

[edo17]

The one to many is a customer having many orders, so the customer pk is in many records in the order table as a fk. The order pk had to be unique to identify that, the same as the customer pk has to be unique to identify that.

BTW do not have spaces in table and field names. That will cause you so much extra work.

Clear and thanks for the advice. This forum is really helpful! Glad that I found it now that I'm starting so hopefully I'll be able to avoid many mistakes

 

[The_Doc_Man]

I tried to run the Query Wizard to find records that don't have a match in both directions and I get the following results: I have 131 records from Table Payroll without a matching in Table Employee Names and 72 records from Table Employee Names that don't have a match in Table Payroll.
I still cannot figure out what relationship exists between these two tables since if it were a one-to-many / many-to-one I would expect duplicates in the EmployeeID field in the first or second table.

One problem MAY be that you have incompleteness in your structure. For example, your tables show CURRENT employees but what happens if the employee is no longer an employee? To be complete, the employee record would show a termination date but would NEVER be deleted. Then in the other table, the link to that person would still be there but when you looked at it, you could tell it was a FORMER employee. If you delete records for former employees, you need to ALSO delete records in that other table that in your case is confusing you. If table 1 is a personnel "contact info" table and table 2 is a payroll table - which allows a person to hold two part-time jobs at once OR to no longer be employed, neither table is a problem.

Table 1 in this case would be the independent table and you just can't delete records for someone no longer employed. It is the incorrect deletion that causes the gaps. Table 2 is a dependent table and is the many side of a simple 1/many relationship - where a terminated employee is the case of "many includes 0".

If you have a payroll entry that is unmatched by a person in the personnel table, that is indicative of data mistreatment, not of any oddity in the table design or structure. This is data abuse and might well have been corrected with a Relational Integrity setting that would have prevented deletion of personnel entries that had payroll entries.

 

[edo17]

One problem MAY be that you have incompleteness in your structure. For example, your tables show CURRENT employees but what happens if the employee is no longer an employee? To be complete, the employee record would show a termination date but would NEVER be deleted. Then in the other table, the link to that person would still be there but when you looked at it, you could tell it was a FORMER employee. If you delete records for former employees, you need to ALSO delete records in that other table that in your case is confusing you. If table 1 is a personnel "contact info" table and table 2 is a payroll table - which allows a person to hold two part-time jobs at once OR to no longer be employed, neither table is a problem.

Table 1 in this case would be the independent table and you just can't delete records for someone no longer employed. It is the incorrect deletion that causes the gaps. Table 2 is a dependent table and is the many side of a simple 1/many relationship - where a terminated employee is the case of "many includes 0".

If you have a payroll entry that is unmatched by a person in the personnel table, that is indicative of data mistreatment, not of any oddity in the table design or structure. This is data abuse and might well have been corrected with a Relational Integrity setting that would have prevented deletion of personnel entries that had payroll entries.

Awesome, you're so clear and helpful with your explanations! What a great place I found!!!!! Thanks again

 

[MajP]

Those tables are not correct.

You should have
TblEmployees with information unique to employees
Title, Borough, Base Pay, Status ....
Those need to get moved out

But normally you would have multiple payroll items not a single. Lets say it is monthly. Unless you are saying these people get hired for one job and that is it. They get paid once then let go.
PayrollID (not employeeID)
PayDateStart
PayDateEnd
RegularHours
OverTimeHours
Paid ....
EmployeeID_FK
AgencyID_FK (likely goes in the Employee table unless on different pay periods they work for different agencies)

That way an employee can have many pay statements.

Also do employees work for an agency. Then that goes in the employee table and not the payroll table. Unless on different months they work for different agencies and you need history of which agency.

You cannot establish refential integrity until you fix the data. Your have Payroll not related to an Employee. That is called an orphan. It is bad data. You show a payment with no knowledge of who was paid. If you enforce referential integrity then every payment is tied to someone. You cannot create a payroll item without assigning a person.