Query with one field linked to many

[nharrison]

Sorry for the awkward title, wasn't sure what to call this...

I am trying to create a query that will return all the projects that a contact is associated with. However a contact can be linked to a project in several different ways:

Contact ID ----- Owner ID
Contact ID ----- Architect ID
Contact ID ----- Developer ID
Contact ID ----- MEP Engineer ID
Contact ID ----- Structural Engineer ID
etc...

Project data is in the table "Project Info" and contacts are stored in "Contacts"

Is there a way I can get a query that produces Contact ID in one field, and then Project ID in another field, showing all projects that a contact is linked to?

All my attempts have been somewhat successful, but I often receive duplicate records because a contact can be both a Structural Engineer and a MEP Engineer.

Any suggestions?

 

[boblarson]

Add the contacts table in to the query the number of times you need it and link ONCE from one to the OwnerID, once from one to the Architect ID and so on.

 

[nharrison]

OK, I tried that but it didn't seem to work. I attached two screenshots, one of the query setup, and the other of the datasheet view of the query results. I also tried selecting option 1 in the Join Properties window ("Only include rows where joined fields from both tables are equal") and I got 0 results, a blank datasheet.

Not sure if I'm doing this wrong or what..

 

[boblarson]

OK, I tried that but it didn't seem to work. I attached two screenshots, one of the query setup, and the other of the datasheet view of the query results. I also tried selecting option 1 in the Join Properties window ("Only include rows where joined fields from both tables are equal") and I got 0 results, a blank datasheet.

Not sure if I'm doing this wrong or what..

You are doing it wrong. You should have the Contacts table in your query about 5 times. Not once linked to everything. Just double click to keep adding the contacts table the number of times for which you will need links. So, you should end up with something like:

Contacts

Contacts_1

Contacts_2

Contacts_3

Contacts_4


and then you link ONCE from the Contacts table to OwnerID and then you link from Contacts_1 to ArchitectID, and so on.

 

[nharrison]

First, I just want to thank you for walking me through all this, I know I'm not the most knowledgeable when it comes to queries.

I did what you said, but I got essentially data output that is already present in the Project Info table.

Attached are screenshots of the query I made, and the generated output. The problem with this is that it's essentially the opposite of what I want; it shows single project records, with associated contacts listed by ID. This data is already present in my Projects Info table. What I need is vice versa, with basically multiple records for each contact and project relationship.

So if John Doe has worked on Project 1, Project 2, and Project 8, it will look like:

Contact Project
John Doe Project 1
John Doe Project 2
John Doe Project 8

Does that make more sense?

I've tried doing the opposite of what you suggested, with one Contacts table, and multiple instances of the Project Info table, and those are the best results I get, but when one contact is associated in more than one way with a single project (i.e. MEP Engineer and Structural Engineer), I get duplicate records, i.e.

John Doe Project 1
John Doe Project 1
John Doe Project 2
etc.

Any ideas?

Thanks again for all your help.

 

[boblarson]

but when one contact is associated in more than one way with a single project (i.e. MEP Engineer and Structural Engineer), I get duplicate records, i.e.

John Doe Project 1
John Doe Project 1
John Doe Project 2
etc.

Any ideas?

Have you tried using the Grouping Button after you've gotten to this point?

 

[mlooman]

Try this.

In the Design View, link the Contact ID to the Owner ID, then switch to the SQL View for the query.

In the SQL View find the syntax for the join statement which should be something like

FROM [Contacts] INNER JOIN [Project Info] ON [Contacts].[Contact ID] = [Project Info].[Owner ID]

Change the Link that uses equals to use the "IN" operator and list the additional columns from Project Info that you want to use. (Make sure that the multiple column list is on the right side of the "IN" keyword.) After the change, it should look like this.

FROM [Contacts] INNER JOIN [Project Info] ON [Contacts].[Contact ID] IN ( [Project Info].[Owner ID] , [Project Info].[Architect ID] , [Project Info].[Developer ID] , [Project Info].[MEP Engineer ID] , [Project Info].[Structural Engineer ID] )

Run the query. You should get the results you are after. Note, that this will cause the query to produce an error if you try to open it in Design view. This is normal. You can either change the query in SQL View from now on, or change the Join statement back and forth in SQL View and make other changes in the Design view. (imho, use SQL View from this point forward.)

hth

 

[nharrison]

Wow, mlooman that worked absolutely perfect; exactly what I needed. For anyone who views the thread in the future, the exact SQL I used was:

SELECT Contacts.[Contact ID], [Project Info].[Project Name]
FROM Contacts INNER JOIN [Project Info] ON Contacts.[Contact ID] IN ( [Project Info].[Owner ID] , [Project Info].[Architect ID] , [Project Info].[Civil Engr ID], [Project Info].[Consultant ID], [Project Info].[Developer ID] , [Project Info].[Landscape Arch ID], [Project Info].[MEP Engr ID] , [Project Info].[Program Mngr ID], [Project Info].[Str Engr ID] )

Nice job for your first post ever

Thanks also boblarson, you were a big help.

*resolved*