Generating a report I want!

[slaman]

Ive posted this problem once already and didn't get a good response. Maybe this forum will be able to help a bit better.

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)

These are the queries I am using right now:
DoCmd.RunSQL ("SELECT Officers.[Corporation ID] INTO temp_for_officers FROM Officers WHERE (((Officers.[personID])= Forms![Search Page]![personSearch]));&quot

DoCmd.RunSQL ("SELECT Directors.[Corporation ID] INTO temp_for_directors FROM Directors WHERE (((Directors.[personID])= Forms![Search Page]![personSearch]));"

DoCmd.RunSQL ("SELECT Shareholders.[Corporation ID] INTO temp_for_shareholders FROM Shareholders WHERE (((Shareholders.[personID])= Forms![Search Page]![personSearch]));&quot

These are adequate queries imo - so now all I have to do is the Report. These are the fields I display.

[corpIDTag]=[Corporation ID]
=Not IsNull(DLookUp("[Corporation ID]","temp_for_directors","[Corporation ID] = [corpIDTag]&quot)
=Not IsNull(DLookUp("[Corporation ID]","temp_for_officers","[Corporation ID] = [corpIDTag]&quot)\=Not IsNull(DLookUp("[Corporation ID]","temp_for_shareholders","[Corporation ID] = [corpIDTag]&quot)

Now if I manually assign [corpIDTag], then the report displays perfectly. It's just that I don't know what to set the CONTROL SOURCE of [corpIDTag] to! Once I get this, my project is done!!!! Yippee!

Please please help me out. I'm so close - I can taste it.

 

[Lozere]

There is a completely different way to achieve that:
It use 3 requests that you merge with a Union request.
One for Directors, one for Officers, one for Shareholders.

The 1st, said "Req_Dir" includes the Corps, Directors and Persons tables
with the fields CorporationId, CorporationName, PersonID and a calculated fields with constant value What_IS: 1

The 2nd "Req_Off" includes the Corps, Officers and Persons tables
with the SAME fields but with value What_IS: 2

The 3rd "Req_Share", you guess, includes Corps, Shareholders and Persons tables with value What_IS: 3

The Union request will be something like
SELECT * from Req_Dir UNION SELECT * from Req_Off UNION SELECT * from Req_Share ORDER BY PersonID, CorporationID

Try it step by step, you will understand. Note that the fields name must be the SAME in these 3 requests.

Then you use a PivotTable request to generate the Data you need, filtered by the PersonID:

Lines: CorporateName
Column: What_IS
Value: 1, operation=Count

This request will give you something like
1 2 3
ZYXCorp 0 1 1
Acme 1 0 1 and so on

You could then use it to generate a report, changing the values 0 or 1 to YES or NO with the format:
"YES";;"NO"

I hope my explanations are enough and the translation to English I made for keyworks are OK...

 

[Lozere]

Probably the right name for PivotTable is CrossTab

Sorry