Relationships between tables in access

[edo17]

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.

Thanks @MajP! Yes when I started looking at the tables I thought that they were messy but I didn't receive any instructions on how to clean them. In that situation, without cleaning the tables do you think you can establish relationships between these? If you have to choose, what relationships would you say exists between the tables?

 

[MajP]

I will make my guess and send it.

 

[edo17]

I will make my guess and send it.

Thank you!

 

[mike60smart]

Thank you! 2) What's the relationship between tblPayroll and tblEmployeeNames and between tblPayroll and tblAgencyNames? That's the question and one of the option for both is that "Cannot be determined".

Hi

You can have the following relationship between these tables, but we do not know what your business process is.

With this relationship you can create a Form as shown in the attached screenshot.

I had to run a few update queries to get the correct values in the Payroll table for Employee and Agency.

 

[MajP]

Hi

You can have the following relationship between these tables, but we do not know what your business process is.

With this relationship you can create a Form as shown in the attached screenshot.

Not sure if you looked at the data, but that is definitely not normalized.

 

[mike60smart]

Thanks @MajP! Yes when I started looking at the tables I thought that they were messy but I didn't receive any instructions on how to clean them. In that situation, without cleaning the tables do you think you can establish relationships between these? If you have to choose, what relationships would you say exists between the tables?

Are you able to upload a PDF of the actual assignment?

 

[MajP]

My best guess is something like this.

This assumes we do not maintain history of an employee pays working for a different agency or a different borrogh. If so we have to move the keys into payroll.
TblEmployees have information unique to the employee not related to a pay period
tblPayroll has information about an employees pay unique to a pay period.

An employee can get many payroll entries (like the real world). OThours have to be related to a specific pay period, if not it makes no sense. So there has to be a payrolldate field

Now I would simplify this more if it was me.
I would make Agencies to be a specific Agency and Location. That table is then tblOffice.

OfficeID
AgencyID_FK
BorroughID_FK

Then an Employee is assigned to a specific Office instead of having to pick a borrough key and a AgencyKey.

This gets more complicated if you need to maintain history. Example Employee A changes agency or office. You now update that information and lose start date, and previous period of employement for different agencies. If you need history then I need to pull that information all into tblEmploymentRecord
-EmployeeID_FK
-OfficeID_FK (or AgencyID_FK, BorroughID_FK)
-EmploymentSartDate
-EmploymentEndDate

So now an employee can have history of employments for different agencies. This is real world around here where Govies move often.

There is a table that holds records showing payroll entries without employees. I deleted these records from the current payroll table.

 

[edo17]

Hi

You can have the following relationship between these tables, but we do not know what your business process is.

With this relationship you can create a Form as shown in the attached screenshot.

I had to run a few update queries to get the correct values in the Payroll table for Employee and Agency.

To do this I should need to create new fields, cleaning the data etc. right? Let's suppose that I can't do that, do you think it's still possible to establish relationships between tables ? If yes, what type of relationships?

 

[mike60smart]

To do this I should need to create new fields, cleaning the data etc. right? Let's suppose that I can't do that, do you think it's still possible to establish relationships between tables (specifically between tblPayroll and tblEmployeeNames, and between tblPayroll and tblAgencyNames)? If yes, what type of relationships? (A possible answer for these two is also "Cannot be determined"). Thank you for your help!

If you are not willing to give us a copy of the assignment set by your Tutor then we are just stabbing in the dark.

 

[mike60smart]

I try to explain the context. We just started using Access and in class he said to us that we just need to export these 3 tables from an excel that he created, choose the correct primary keys for each table and then he just posted this Description "Scenario: you have been tasked with summarizing and extracting information from the NYC payroll department. This data is a blend of simulated data along with a sample of real data from NYC Open Data" and the following two questions in a "Discussion" section: 1)What type of relationship do you find between tblPayroll and tblEmployeeNames? A) One-to-many (Or Many-to-One) B) One-to-One C) Cannot be determined D) Left Outer Join
2) What type of relationship do you find between tblPayroll and tblAgencyNames? A) One-to-many (Or Many-to-One) B) One-to-One C) Cannot be determined D) Left Outer Join.

I'd love to give you more info but that's all I have. He never specified that we need to clean the data, I suppose because we just started. Thank you if you can help me!

I can say that there is a 1 to Many relationship between tblAgencyNames and tblPayroll and both PK and FK are set as ShortText which does not conform to normal practice.

The relationship between tblPayroll and tblEmployeeNames is 1 to 1 and yet again the Keys are set as Short text. It is very rare to have a 1 to 1 Relationship.

 

[edo17]

I can say that there is a 1 to Many relationship between tblAgencyNames and tblPayroll and both PK and FK are set as ShortText which does not conform to normal practice.

The relationship between tblPayroll and tblEmployeeNames is 1 to 1 and yet again the Keys are set as Short text. It is very rare to have a 1 to 1 Relationship.

Great, thanks to you and everyone that helped me I'm realizing that there are a ton of problems with this database!

 

[MajP]

Scenario: you have been tasked with summarizing and extracting information from the NYC payroll department. This data is a blend of simulated data along with a sample of real data from NYC Open Data" and the following two questions in a "Discussion" section: 1)What type of relationship do you find between tblPayroll and tblEmployeeNames? A) One-to-many (Or Many-to-One) B) One-to-One C) Cannot be determined D) Left Outer Join

Sorry, this is so messed up that I do not think I could answer correctly. I can fix it and have it make sense. But it is like answering gibberish. It is unfortunate, to use this to try to teach someone.

If you cannot add additional fields, and assume that all tables have a PK then you are stuck with a 1 to 1, but makes no logical sense.

If you chose EmployeeID as the PK for Payroll then it is a 1 to 1 and since you cannot add a PayrollID you are stuck. You can only add one record for each employee.

If you leave EmployeeID as a foreign key then it is a 1 to Many. You can add many more payroll entries. But in this example there is not a PayrollID.


1. Even if it made sense. You cannot establish a relationship (enforce referential integrity) because there are orphan records in payroll. There are payroll items without Employees. In order to establish a relation you need to first get rid of orphan records. Again you can join in a query on anything that is the same datatype, a relation is when referential integrity is established.
2. Table are not normal making the question nonsensical. They combine Employee info and payroll period info.
3 Logically it should be a 1 to Many. An Employee should have many payroll entries. That means you need a PayrollID and a PayrollDate to uniquely identify an entry. (Also want to index

Show the prof my design in #28 and tell them that would make sense.

Create a left outer Join from Employees to Payroll if you want to summarize information for all employees and pay information for those that have it. You can create a left outer join from Payroll to Employees to summarize all payroll information and information about employees that have payroll information.

There are orphans both ways.
You have employees without payroll information (that is OK I guess, depending on what the data is supposed to show)
You have paryroll information without employees (normally this is bad)

 

[MajP]

I strongly suggest reading this. It will really help. People throw joins and relation around together but they are different things.

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

isladogs.co.uk

 

[edo17]

Sorry, this is so messed up that I do not think I could answer correctly. I can fix it and have it make sense. But it is like answering gibberish. It is unfortunate, to use this to try to teach someone.

If you cannot add additional fields, and assume that all tables have a PK then you are stuck with a 1 to 1, but makes no logical sense.

If you chose EmployeeID as the PK for Payroll then it is a 1 to 1 and since you cannot add a PayrollID you are stuck. You can only add one record for each employee.

If you leave EmployeeID as a foreign key then it is a 1 to Many. You can add many more payroll entries. But in this example there is not a PayrollID.


1. Even if it made sense. You cannot establish a relationship (enforce referential integrity) because there are orphan records in payroll. There are payroll items without Employees. In order to establish a relation you need to first get rid of orphan records. Again you can join in a query on anything that is the same datatype, a relation is when referential integrity is established.
2. Table are not normal making the question nonsensical. They combine Employee info and payroll period info.
3 Logically it should be a 1 to Many. An Employee should have many payroll entries. That means you need a PayrollID and a PayrollDate to uniquely identify an entry. (Also want to index

Show the prof my design in #28 and tell them that would make sense.

Create a left outer Join from Employees to Payroll if you want to summarize information for all employees and pay information for those that have it. You can create a left outer join from Payroll to Employees to summarize all payroll information and information about employees that have payroll information.

There are orphans both ways.
You have employees without payroll information (that is OK I guess, depending on what the data is supposed to show)
You have paryroll information without employees (normally this is bad)

Thanks so much for your help!

 

[Pat Hartman]

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.

Why are you assuming there is a relationship? I have thousands of tables. Most of them have an autonumber as the PK, are they all related simply because Access starts assigning autonumbers at 1 and so 1 occurs in every table unless i deleted it?

You are making us guess. Why not tell us about the secret data in these tables so we can stop wasting our time trying to figure out what you are talking about?

 

[MajP]

Why not tell us about the secret data in these tables so we can stop wasting our time trying to figure out what you are talking about?

The OP does not know that. This is a class assignment where you download excel files with no background context. The Prof asked what is the relationship. That post from the OP is no longer there for some reason explaining that. The example and question is so bad that you can not really answer the question. I do not think the Prof really knows rdms or they would have provided a much better example with logical structure.