Relationships

[cclambie]

Hi,
I am positive this will be on this forum somewhere, so if someone can point me in the right direction, that would be great. I can't find the right keywords to deal with this on.

I have a tblCompany, tblContacts and tblRelationships

If I have an employee of Company X doing something for Company Y, I put it in the relationship Tbl.

My issue is I can't get a query to show the relationships based on the tblRelationships and the tblContacts.
ie. I have a field in Contacts called CompanyID to link them back to their parent company.

I would appreciate any help...

 

[carash77ash]

Have you related the tables so that access knows which information to draw from with (pk) and (fk)

Ash

 

[cclambie]

Ok

Have the Relationships set on the Rel form as follows

Company.CompanyID 1toM Contacts.CompanyID

Contacts.ContactID 1toM Relationships.ContactID
Company.COmpanyID 1toM Relationships.CompanyID

I think I need to create two querys to get the data, I am just getting lost in what to do I think....?
THanks

C

 

[wazz]

I have an employee of Company X doing something for Company Y

tblEmployee
EmpID (PK)
EmpData
CoID (FK)

tblJob
JobID (PK)
JobDesc
EmpID (FK)
CoID (FK)

tblCompany
CoID (PK)
CoName
CoData...

- an Employee belongs to a company (CoID in tblEmployee)
- a job (tblJob) is done by an employee (EmpID) for a company (CoID)
- could change tblEmployee to tblContact
- hth

 

[wazz]

oops. i think you already have all that. sry.

My issue is I can't get a query to show the relationships based on the tblRelationships and the tblContacts.

if you put both tables into query design and drag the fields you want to the grid you don't see what you want? don't drag the ID fields; instead of CompanyID, drag the name of the company; instead of the ContactID, drag the ContactName. does that help?

 

[cclambie]

Thanks, will give it a go.

One issue:
I use the result of the query in a form, which based filters based on the ID field (sub form)

Could I just clarify what the Dk, Pk too?

Hmmm, still having issues?, dont think i get it (probably the dk, pk thing)
Sorry...

 

[wazz]

FK = Foreign Key
PK = Primary Key

i think your relationships are set up ok. instead of what i said before, drag both the ID-fields and the 'name' fields (ContactID and ContactName; CompanyID and CompanyName, etc.) into the query. it's very possible i'm not understanding your problem.