Relationship problem

[Sharon Hague]

Hi All

I have created a db to log all employee details together with absence details and salary details. Therefore I have 3 tables

1) - Employee Details with a primary key set to Employee ID as AutoNumber
2) - Absence Details with no primary key set. Employee ID set to Number
3) - Salary Details with no primary key set. Employee ID set to Number

I have created a relationship between Employee Details and Absence Details through the Employee ID field as a one to many relationship.

At present I have no relationship with Salary Details. Again I want another one to many relationship from Employee Details which I can do from the same field (Employee ID), however in doing so I cannot create a query to get info from all 3 tables as in theory Salary Details is not connected to Absence Details.

How can I solve this?

 

[namliam]

This is not a problem since both secondary tables are related to your Employee Details table

However take into account that when linking two 1 to many tables you have the danger of doubling up... Look at it carefully!!!

Regards

 

[Sharon Hague]

Namliam

Thanks for your reply.

I didn't think it was the correct way to set up your relationships to have 2 one to many relationships going from one main table to 2 others.

This is why I asked the question to be sure I wasn't skipping something.

I will see if it causes probs with myqueries and let you know.

Cheers

 

[namliam]

I didn't think it was the correct way to set up your relationships to have 2 one to many relationships going from one main table to 2 others.

As long as you have a relationship from one table to another anything is posible.
You have A-B and A-C, you might just see it as B-A-C.

many to 1 to many

However you MUST take into account the many to many you end up with...
Table A (the 1)
1 He
Table B (many)
1 is
1 was
Table C (many)
1 Crazy
1 a Lonatic

Linking these three tables ends you up with:
He is Crazy
He was Crazy
He is a Lonatic
He was a Lonatic

Just some food for thought...

Regards

The Mailman

 

[Sharon Hague]

Mailman

I was reading the above seriously until I got to the bottom!

Access and relationships drive you insane at times until you think your going nuts! ha!

Speak to you soon.

 

[namliam]

What do you mean ?

I was reading the above seriously until I got to the bottom!

It was a serious post! just trying to explain Access (relational) DB logic to you....

Regards

 

[Sharon Hague]

Mailman

Sorry! When I read you explanation I didn't thnk you were being serious and thought you were joking.

Sorry!

 

[namliam]

I never joke when it comes down to Women and VBA !!!!

No the post was serious, tho with a minor twitch (I am kinda crazy after all)

Gotta get to know me i guess

Regards

 

[Sharon Hague]

Pat

Thanks for your reply.

The reason I want all 3 tables linked is because my absence details table also records overtime on a daily basis.

Therfore I want to design a report through a query comprising of i.e name from employee table, salary from the salary table and then depending on the hours overtime from the absence details table, work out how much overtime in £ we have paid.

This is why mailman said to be carefull with queries in case it duplicated records with how I have my relationships.

I still havn't tried it as yet but hopefully today I will be able to spend some time on it.

Cheers.

 

[Sharon Hague]

Thanks Pat

It seems quite straight forward

I will try to have a look at it today.

I will let you know how I get on.

Cheers.