Contacts w/ related projects, difficult relationships

[nharrison]

I am programming a database for a mid-level construction company, and in the program I have a Contacts table, and a Project Info table. The key field of Contacts is Contact ID, and for Project Info it is Project ID.

There is multiple one-to-many relationships between multiple instances of the Contacts table and fields within Project Info: Architect ID, Owner ID, MEP Engr Id, etc. (8 total). For my Contact Entry form I would like to have a tab that displays all projects which is associated with that contact, similar to the Open Opportunities tab in Contact Details for the Sales Pipeline template. The only difference is, in my database, there are 8 possible fields in Project Info which could correspond to any given Contact ID.

I'm thinking some sort of complicated query might be the solution, but this is slightly beyond me...

Any suggestions?

 

[Guus2005]

This is a normalisation issue.

You need to add a table (ProjectContacts) to store the N:M relationship: ProjectID and ContactID. The amount of contacts you can now add is endless.

HTH

 

[nharrison]

Previously, this is how I had the database set up, with tables Project Info and Contacts. I also had 8 intermediary tables named Architects, Owners, Developers, etc... with only two fields: Project ID, Contact ID. This would store the relationship for each type of specialist for each project. However this cause sticky relationships and a very complicated database structure, which I abandoned in favor of my previously discussed, streamlined version. My situation is not a textbook M:M relationship, or O:M or O:O, so I kind of had to branch out on my own. But if I wanted to retain my current table/relationship structure, would there be a way to do what I asked? And if not, could you explain your solution in further detail? Thanks.

 

[David Eagar]

Is it something along these lines you're after?

 

[David Eagar]

This might be a better version

 

[nharrison]

Actually, I managed to resolve the problem myself.

I created a query called Contacts Projects, in which I show the Contacts table, with Contact ID linked to 8 instances of Project Info. Contact ID is linked to the fields Architect ID, Owner ID, Str Engineer ID, etc. in the instances Project Info, Project Info_1, Project Info_2, etc.

Then to my actual query, I added two fields: Contact ID, and a conditional statement:

Project:
IIf([Contacts Extended].[Type]="Architects",[Project Info Extended].[Project Name],
IIf([Contacts Extended].[Type]="Owners",[Project Info Extended_1].[Project Name],
IIf([Contacts Extended].[Type]="Civil Engineers",[Project Info Extended_2].[Project Name],
IIf([Contacts Extended].[Type]="Consultants",[Project Info Extended_3].[Project Name],
IIf([Contacts Extended].[Type]="Structural Engineers",[Project Info Extended_4].[Project Name],
IIf([Contacts Extended].[Type]="Program Managers",[Project Info Extended_5].[Project Name],
IIf([Contacts Extended].[Type]="MEP Engineers",[Project Info Extended_6].[Project Name],
IIf([Contacts Extended].[Type]="Developers",[Project Info Extended_7].[Project Name],""))))))))

and the criteria <>""

This now returns a list of all projects for any contact. It functions because any contact can only fall under one of the 8 categories, and therefore would only ever be linked to a record in Project Info under one of the 8 fields. The field "Type" in Contacts stores which category the contact falls under.


Thanks for all the help though guys!

 

[Guus2005]

Glad you got it working.
You might consider the SWITCH function instead of the IIF function.

Architects and Consultants are professions perfomed by people. You could have created a lookup table to store these professions.
The Contacts table could then reflect each users profession.

In your Contacts Projects table you could then attache projects to persons and subsequently their profession.

Setup this way your database is easier to maintain and the query you show would be a lot easier and faster.

select * from contact c inner join [contact project] cp on c.id = cp.contactid inner join project p on p.id = cp.projectid

Enjoy!

 

[nharrison]

actually that's the way it is...my database has developed more. I now have a Specs table, a Companies table, and Contacts. Each contact is linked to a company, and each company is linked to a spec. If you look at the code I posted, the source for that query is actually another query, Contacts Extended, which pulls the profession for the contact through this relationship. This has really streamlined the database nicely.

Good minds think alike apparently

Oh, and that's actually a great point. I use the SWITCH CASE function often in C++ and wasn't aware that there existed one in VBA. I'll note that for future reference!