No unique index found for the referenced field of the primary table

[Hello1]

Hello guys,

My problem is next. When Im doing calculations for workers there will be a case when Im doing calculation at the same year/month and same type of calculation but for different company.

Example1:
YearMonth- 201901 TypeOfCalculation- PAYM
YearMonth- 201901 TypeOfCalculation- LOAN

And this works just fine as will the relationships as you can see on pictures number 4 and 5.

Example2:
YearMonth- 201901 TypeOfCalculation- PAYM
YearMonth- 201901 TypeOfCalculation- PAYM

And there I get the error that duplicates cant happen, thats good coz we dont want them.

In the exactly same example/table (Calculations) I want to add a CompanyCode field so I can do calculations based on all 3 fields.

It works like this:
Example3:
YearMonth- 201901 TypeOfCalculation- PAYM CompanyCode- CMP1
YearMonth- 201901 TypeOfCalculation- PAYM CompanyCode- CMP2

This is how it works after adding the CompanyCode, just like in the first example, just with CompanyCode in it, whats grate.

Example4:
YearMonth- 201901 TypeOfCalculation- PAYM CompanyCode- CMP1
YearMonth- 201901 TypeOfCalculation- PAYM CompanyCode- CMP1

And this is where I get the error for no duplicates again and thats what I want, cool.

Now to the problem. After making the fields YearMonth/TypeOfCalculation/CompanyCode, primary keys or so called composite keys that works fine, I dont get any errors but when I want to create a relationship between the table Calculation and PersonalIncomeOfWorker I get the error just like in the title and as you can see in pictures number 1,2 and 3, but I dont get the error when making the relationship with only 2 composite keys in the Calculation field which are YearMonth/TypeOfCalculation.

Edit: I assume I need the CompanyCode added in the PersonalIncomeOfWorker table too, because doing so I can make the relationship. So my relationship design is probably wrong or?

Thanks

 

[Hello1]

Relationships and table design wrong? Because I have a Worker table too which is connected to Company table.

 

[plog]

Relationships are wrong. You've created a spiderweb---everythings connected to everything else. When everything's related, you effectively don't have any relationships.

There should only be 1 way to move between 2 tables in your relationships. I count 3 ways to get from PersonalIncomeOfWorker and Company. You need to decide the 1 way they should be related and clear out the other 2 tables with the CompanyCode foreign key. Same for all foreign keys in multiple places.

Additionally, I'd suggest adding an autonumber primary key to PersonalIncomeOfWorker. Just makes things cleaner, especially if some of those fields are coming out.

Lastly, change Year month to an actual date\time field and store the first if the months date to represent that value. That will allow you the use of the built in Date functions so you can make better reports--e.g. Group years together, compare prior\current time frames.