A really complex query/report

[slaman]

A little background into the structure

Corporations.CorporationID
Corporations.CorporationName
Directors.PersonID
Directors.CorporationID
Officers.PersonID
Officers.CorporationID
Shareholders.PersonID
Shareholders.CorporationID
People.PersonID

Ok, that is the above structure/fields I am using. What I want to accomplish is this - a user enters a Person's name into a field (the PersonID is stored). I want to generate a REPORT doing the following:

Director Officer Shareholder
Corporation1 Yes No Yes
Corporation2 Yes No No
Corporation6 No No Yes

Basically, just a small chart saying if a person is a director/officer/shareholder of a corporation. The only thing I DONT want to display is the case where a person has no relation to the Corporation (above, by Corporations3-5)

This is the final query of my project and of course, I had to get stuck. My approach was to create a query that matched personID and created a temp_for_directors, temp_for_officers, and temp_for_shareholders and then use those to generate a report. That idea flopped... I'm open to suggestions...

 

[Databaseguy]

You're close.

In your relationship between person and ShareHolders, change the join to "Include all records from people and only those from ShareHolders where the joined fields are equal". Do the same for Directors and Officers tables.

 

[slaman]

so right now I have

temp_for_directors - Corporations.CorporationID, Directors.CorporationID, Directors.PersonID

and the same fields for Officers and Shareholders... How would I use that to make the report - I'm not understanding what you're saying... That join thing you mentioned is for a query no? Are you saying to put Officers/Shareholders/Directors into one table with a Corp ID and Person ID? If so, how?

 

[Databaseguy]

Paste this in the SQL view of a query desgin. You might have to modify it a little so your field names match. You'll get the ider of what I'm trying to do,

SELECT tblPerson.personID, IIf([tblOfficer].[CorpID] Is Null,"No","Yes&quot AS Officer, IIf([tblDirector].[CorpID] Is Null,"No","Yes&quot AS Director, IIf([tblShare].[CorpID] Is Null,"No","Yes&quot AS Share
FROM ((tblPerson LEFT JOIN tblShare ON tblPerson.personID = tblShare.PersonID) LEFT JOIN tblOfficer ON tblPerson.personID = tblOfficer.PersonID) LEFT JOIN tblDirector ON tblPerson.personID = tblDirector.PersonID;

 

[slaman]

Unfortunately, after trying many many different queries - I havent gotten one to work =/ I tried the one you posted, but it was full of syntax errors, so it didnt work =/

If anyone has anymore suggestions - I'm running out of time and really don't want to restructure things to make it work.