Table Relationship Troubles

[GSTAR]

I hope this hasn't been asked on here before. I'm too frustrated to search anymore...



I have a table that is a list of clients Each Client has the possibility of being seen by 1 or more of 4 classes of employee EI, PT, SLP, and/or OT.

the information for each employee is then in a second table,

So it looks like this Simplified

Table 1 Clients
-----------
ClientID (key)
Client Name
Client Adress
EI ID
PT ID
SLP ID
OT ID

Table 2 Employees
-------------------
ID (key)
Title (EI, PT, SLP, OT etc.)
Employee Name
Address

My trouble is Access Won't let me create 4 relationships from the ID's from the first table to the Emplyee table ID.

I tried to to use Junction tables but that won't work. The only thing that works is to create four of the same table in the relationship window. Employees, Employees_1 etc. but when I go to make a report or a querry i have to make 4 different versions of the same report or querry for each of the 4 classes of employess, super anoying. I haven't been able to find a solution anywhere other than creating 4 different tables for employees. But that doesn't make sense since there are about 10 employees. One table would actually have only person in it!

Thinking about suing Microsoft for my newly developed anxiety disorder haha j/k

Thanks for the help

[pbaldy]

You're having trouble because the first table isn't normalized. The client/employee relationships should be in a related table (likely the junction table you mentioned). The workaround is the aliased copies of the table you mentioned. You shouldn't need multiple versions of the same report. You'd base the report on a query that had the same thing; aliases of the table joined to each of the fields.

[GSTAR]

Can you explain how to create that query?

[pbaldy]

Same way you did the relationships. Add the table to the design grid 4 times. Access will alias 3 of them. Join one of each field to one of the copies.

[GSTAR]

OK I must be an idiot, which of the tables should I be aliasing? also when I open the query it already has my relationships in place. How Would I then do the joining? there is no way to do it in the query design field

thanks for your patience...

[pbaldy]

The employee table, once for each of the non-normalized fields. If the query already has it, what's the issue? If not, you can certainly create them in design view, by clicking and dragging. Can you post the db here?