Multiple relations between two tables

[nande123]

Hi,

I'm new to Access and Databases in general so please have patiance.

Have linked Access with Outlook and our Global Address Book and I now want to build relationship with this table and another table called Exemptions.

The problem is that I need two fields (Requestor and Approver)in Exemptions to be related to the Global Address table. Is this possible or do I need to create two other tables between the global address book table and the exemption table?

Regards,

 

[Remou]

It is possible. You can use the relationship window to drag each field to create the relationship. It is not uncommon to want a relationship built on more than one field (column).

http://lessthandot.com

 

[dhookom]

You should be able to have multiple copies of the Global Address table in the relationships window. Join one to the Requestor and one to the Approver.

Duane
Hook'D on Access
MS Access MVP

 

[nande123]

Thanks,

A related problem (not sure if I should create a new post) is that it is running so slow and I'm not sure if my Global Address Book it to big for Access to manage or if I can make it run fatster.

My Global Address Book contains about ~70.000 entries so I created a query which filter it down to about ~20.000 rows.

Then I'm pointing Approver field in the Exemption table to this query using a combo list. Access is "Not Responsing" every time I try to pick something from the list.

Is there a better way I can manage this or is it just too much data for Access?

Regards,

 

[Remou]

You do need a new post. In the meantime, this may be of interest:

http://www.outlookbanter.com/outlook-vba/81606-link-outlook-address-book-slow.html

http://lessthandot.com

 

[nande123]

Read the link you posted but think that is over my head so I changed it to a copy insted of a link to Outlook and it is running much smoother.

Regarding having two links between Employee table (the new copy from Outlook) and my Exemption table it now looks like this

Employee
EmployeeID (Yes (No Duplicates))
EmployeeName

Exemption
ExemptionID (Yes (Duplicates OK))
RequestorID (Yes (Duplicates OK))
ApproverID

And both RequestorID and ApproverID have relations with EmployeeID.

For some reason it won't accept "Enforce Referantial Integrity" is that because I now have to relations between the same tables?

regards,

 

[dhookom]

Do you have two copies of the Employee table in the relationships as suggested? Are all tables in the same Access database?

Duane
Hook'D on Access
MS Access MVP

 

[nande123]

Went back and checked my relations and now I see it automatically created a second employee table and I can now check this "Enforce Referantial Integrity".

Is this second employee table just a virtual table because I can't see it in the list of all tables on the left side of access?

Regards,

 

[dhookom]

The second employee table is a virtual copy of the first. They are the same actual table.

Duane
Hook'D on Access
MS Access MVP

 

[nande123]

This is solved now thanks to the help for you guys!!!

Regards,