2 verschillende verwijzingen uit dezelfde tabel in een query

Ik heb de volgende tabellen:
- Bedrijfsgegevens (primary key BedrijfsID)
- Persoonsgegevens (primary key PersoonsID)
- Projecten

Deze laatste bevat onder meer de gegevens van de bedrijven voor wie ik projecten uitvoer.
In het veld "contactpersoon" staat de PersoonsID en die linkt naar de tabel "Persoonsgegevens" - werkt prima
In het veld "klant" staat de BedrijfsID en die linkt naar de tabel "Bedrijfsgegevens" - werkt prima

Nu kan het ook zijn dat de klant een tussenpersoon is, b.v. adviesbureau "McKinsey". In dat geval wil ik ook de eindklant, b.v. "Heineken" in een apart veld vermelden.
In het veld "opdrachtgever" komt dan de BedrijfsID van McKinsey te staan en die linkt naar de tabel "Bedrijfsgegevens".
(Om allerlei redenen is het niet mogelijk om tussenpersonen en eindklanten in verschillende tabellen te zetten, informatie van beide moet dus uit Bedrijfsgegevens komen.)

Op formulieren werkt dit prima, maar in queries kom ik in de problemen.
[Persoonsgegevens]![Naam] kan ik prima weergeven
maar hoe kan ik aangeven dat [Bedrijfsgegevens]![cmp_name] 1x de naam van de klant dient weer te geven en 1x de naam van de opdrachtgever?

guidothys zei:

(Om allerlei redenen is het niet mogelijk om tussenpersonen en eindklanten in verschillende tabellen te zetten, informatie van beide moet dus uit Bedrijfsgegevens komen.)

Klik om te vergroten...

Niet mogelijk? Ik zou het veel sterker willen zeggen: totaal ongewenst! Je zou dat namelijk nooit moeten willen. Een tussenpersoon is in dit geval, vermoed ik, altijd een bedrijf, gezien ook dat je zegt dat de 'tussenpersoon' uit de tabel [Bedrijfsgegevens] wordt gehaald. Lijkt mij een prima zaak, want er is essentieel gezien geen verschil tussen een bedrijf en een tussenpersoon; een tussenpersoon komt in dit geval ook als een bedrijf in het systeem te staan. De vraag is: hoe leg je de relaties tussen de tussenpersonen en de klanten waarvoor ze werken? Heeft elke tussenpersoon maar één bedrijf waar hij/zij tussenpersoon voor is, of mag een tussenpersoon meerdere bedrijven vertegenwoordigen? In het tweede geval heb je een andere constructie nodig.
Je zegt dat je het op het formulier wél voor elkaar hebt, maar in een query niet. Dat vind ik dan weer vreemd, want bij bij zou die query de basis zijn van het formulier, en dus zouden alletwee bij mij werken, of geen van beide.

Octafish, dank voor de reactie.

We zijn het er dus over eens: 1 tabel met bedrijfsgegevens.

De projectentabel bevat o.m.:
in het veld "Contactpersoon" een PersoonsID, deze is gelinkt met PersoonsID in de tabel Persoonsgegevens en genereert dus op het formulier de naam "Henk Jansen" (b.v.)
in het veld "Klant" het BedrijfsID van McKinsey, deze is gelinkt met BedrijfsID in de tabel Bedrijfsgegevens en genereert dus op het formulier de naam "McKinsey"
in het veld "Opdrachtgever" het BedrijfsID van Heineken, deze is gelinkt met BedrijfsID in de tabel Bedrijfsgegevens en genereert dus op het formulier de naam "Heineken"
daarom gaat het goed in de formulieren

in een query kan ik alle velden van de projecttabel probleemloos oproepen, maar wanneer ik daar [Bedrijfsgegevens]?[Bedrijfsnaam] aan toevoeg, dan volgt hij 2x de link in het veld "klant" en krijg ik dus 2x "McKinsey" te zien
hoe kan ik in een query het onderscheid maken tussen [projecten]?[klant]->[Bedrijfsgegevens]?[Bedrijfsnaam] en [projecten]?[opdrachtgever]->[Bedrijfsgegevens]?[Bedrijfsnaam] ?
waarschijnlijk iets heel stoms, maar ik vind het niet......

lees ik het goed dat je suggereert om de link naar de klantnaam (McKinsey) vanuit de tabel Persoonsgegevens te leggen? Het is inderdaad zo dat 1 contactpersoon altijd bij 1 bedrijf werkt.

Je hebt het nu over 'contactpersoon' waar je eerder over 'tussenpersoon' sprak. Is dat één en dezelfde entiteit? Laten we daar even vanuit gaan, omdat ik het zo ook heb geïnterpreteerd. Dus in dat geval heb je een bedrijf en een bedrijf kan via een tussenpersoon werken, die dan ook weer een bedrijf is. In dat geval krijg je dezelfde constructie als in de vraag van tweela, en dan is de oplossing dus: maak in de bedrijfstabel een extra veld ParentID, waarin je voor de tussenpersoon de waarde opneemt van het bedrijf dat ze vertegenwoordigen. Dus McKinsey met ID 123 vertegenwoordigt "De Kaapse Brouwers" met ID 78 (ik hou het natuurlijk liever Rotterdams) en in het veld ParentID van mcKinsey vul je dan 78 in. Daarmee is er een directe koppeling tussen de tussenpersoon en het bedrijf. In de query neem je dan zowel BedrijfID als ParentID op, en bij rechtstreeks contact blijft dat veld leeg, bij tussenpersonen zie je dus zowel Bedrijf (Tussenpersoon) als ParentID (bedrijf dat vertegenwoordigd wordt).
Ik kan me nog voorstellen dat je de zaak op je formulieren etc. nog wilt omdraaien, dus altijd het bedrijf wil zien bij Bedrijf, en contactpersoon als dat van toepassing is. In dat geval gebruik je voor het veld Bedrijf_ID in je query een IIF, die er dan zo uitziet:
In de query koppel je dan de tabel [Bedrijven] aan het rekenveld [Bedrijf_ID] en dan heb je altijd het bedrijf daar staan. De tussenpersoon krijg je dan op deze manier:
En ook hier koppel je de tabel [Bedrijven] aan. De tabel [Bedrijven] wordt dus twee keer gekoppeld, en laat als het goed is ook twee verschillende bedrijven zien. Wel zorgen dat de tweede relatie een Outer Join is, anders zie je alleen bedrijven met een tussenpersoon. En dat zal niet de bedoeling zijn.

Octafish: sorry, nog geen tijd gehad om er verder in te duiken. Wordt ten vroegste volgende week.

Je hoeft je voor (en aan) mij niet te verontschuldigen; het is jouw vraag . Doe het vooral in je eigen tempo, zou ik zeggen.