Left outer join werkt niet??

[ArjanVos]

Hallo,

Ik heb in mijn database een tabel met inkooporderregels. Sommige producten zijn samengesteld uit losse onderdelen. Die samenstelling zit in een andere tabel. De onderdelen kunnen ook los in de inkooporder staan. Nu wil ik hiervan één tabel creëren, waarin ik het totaal aantal te bestellen componenten terug krijg.

Voor onderdelen die ook rechtstreeks in de order staan, gaat het goed. De onderdelen die alleen in de samenstelling zitten en niet rechtstreeks in de order ontbreken echter. Met een left outer join vanuit Orderregels op Samenstelling (veld Product), krijg ik alleen de waarden die ik ook uit de inner join krijg, met een extra regel waar de meeste velden blanco zijn met twee extra blanco regels.

In de bijlage een voorbeeld met de brontabellen, gewenst resultaat en verkregen resultaat om het allemaal wat duidelijker te maken.

 

[OctaFish]

Behalve die Som.Als formules zit er niet zo bar veel in je bestandje om mee te werken, Zo zie ik op het tabblad [Gewenst resultaat] een produkt (D) dat niet in je Orderregels voorkomt. Hoe kun je die waarde dan ophalen? Als ik op tabblad [Verkregen resultaat] er een D bijtyp, en de formules doortrek krijg ik exact hetzelfde resultaat. Maar een Outer Join heb ik dus al helemaal niet aangetroffen. Dit soort dingen kun je ook veel beter in Access doen, maar dat staat waarschijnlijk los van je vraag.

 

[ArjanVos]

Bedankt voor je reactie. Ik heb het lijstje nu handmatig ingetypt bij wijze van voorbeeld, maar wil in ieder geval de producten A t/m D vanuit een query op tabellen Orderregels en Samenstelling (die dus in een externe database zitten) in één kolom onder elkaar zien te krijgen. Dat is het punt dat mij niet lukt.

 

[ArjanVos]

Oh ja, ik zou dit zelf inderdaad ook liever met Access doen, maar dat wil de klant niet

 

[OctaFish]

Dan blijft dus de vraag: waar komen die produkten vandaan? Want jij zet dus een product in het overzicht waarvoor geen order is. En ik zie verder geen tabblad met een overzicht van producten.

 

[ArjanVos]

Er is een order voor producten A, B en C (tabel Orderregels). In tabel Samenstelling zie je dat A bestaat uit 1xB, 2xC en 1xD. Dat is m.i. genoeg info om mijn gewenste overzicht te maken, want ik kan ophalen hoe met welk het product direct op een orderregel voor komt en met welk aantal indirect, als onderdeel van een samengesteld product in diezelfde order. Dat krijg ik ook nog wel als twee losse tabellen in Excel. Maar nu nog de samenvoeging naar één tabel...

Los van bovenstaande, is er inderdaad nog wel een producttabel, waar omschrijvingen en allerlei andere attributen in staan.

 

[MarcelBeug]

Als ik het goed begrijp, gaat het er alleen nog om, om de Producten (A, B, C, D in dit voorbeeld) gevuld te krijgen in kolom A van blad 'Gewenst Resultaat'.
De rest is al geregeld.

Welnu: ik heb een oplossing, maar hij is niet eenvoudig, deels omdat hij dynamisch is en dat is dan wel weer een voordeel.

Ik heb een drietal namen gedefinieerd:
BOM: nu het bereik Samenstelling!A2:B4, maar als er regels aan toegevoegd worden, dan worden deze meegenomen in BOM.

=Samenstelling!$A$2:INDEX(Samenstelling!$A:$B;AANTALARG(Samenstelling!$A:$A);2)

RijKolom: een matrix met een samenstelling van de relatieve rij- en kolomnummers van BOM (nadere uitleg hieronder).

=100000*(RIJ(BOM)-RIJ(INDEX(BOM;1;1))+1)+KOLOM(BOM)-KOLOM(INDEX(BOM;1;1))+1

Sorteercodes: het aantal elementen in BOM <= het huidige element (nadere uitleg hieronder).

=AANTAL.ALS(BOM;"<="&BOM)

BOM is nu:
A B
A C
A D

Rijkolom is nu:
100001 100002
200001 200002
300001 300002
waarbij het eerste cijfer de relatieve rij is in BOM en het laatste cijfer het relatieve kolomnummer van BOM.

Sorteercodes is nu:
3 4
3 5
3 6
Er zijn 3 elementen <= "A"; 4 elementen <= "B" enzovoorts.

Ik heb een hulpkolom A toegevoegd in 'Gewenst resultaat', matrixformule in A2 (bevestigd met Ctrl+Shift+Enter) en gekopieerd naar beneden:

=ALS(A1="";"";ALS.FOUT(1/(1/MIN(ALS(SorteerCodes>SOM(A1);SorteerCodes)));""))

Dit geeft eerst de kleinste sorteercode (3), dan de kleinste sorteercode die groter is de kleinste (wordt dus 4), enzovoorts.
De constructie ALS.FOUT(1/(1/(formule));"") zorgt ervoor dat nullen uit de formule worden vervangen door een leeg veld.
Dat is nu het geval in 'Gewenst Resultaat'!A6 omdat daar geen Sorteercode meer gevonden wordt > 6.
In de regels daaronder wordt "" ingevuld omdat in de regel daarboven een "" staat.
Desgewenst kun je deze kolom verbergen.

Product, matrixformule in B2 (bevestigd met Ctrl+Shift+Enter) en gekopieerd naar beneden:

=ALS($A2="";"";INDEX(BOM;INTEGER(MIN(ALS(SorteerCodes=A2;RijKolom)))/100000;REST(MIN(ALS(SorteerCodes=A2;RijKolom));100000)))

Eigenlijk geeft dit gewoon de waarde van BOM terug waarvan de bijbehorende sorteercode gelijk is aan de waarde in kolom A.
In de formule worden de rij-index en de kolom-index herleid uit RijKolom.

Aan de formules in kolom C t/m E heb ik een test toegevoegd of de waarde in kolom A leeg is.
Ik heb nu de formules in 'Gewenst resultaat' gekopieerd t/m regel 30 (gemarkeerd met een kader), maar die kun je net zover doorkopiëren tot het maximum aantal dat je ooit nodig denkt te hebben.

LET OP: als je rijen tussenvoegt of verwijdert, dan moet je de formule in A2 opnieuw naar beneden kopiëren, omdat in de formule de waarde in de vorige rij wordt gebruikt. Het toevoegen/verwijderen van rijen verbreekt dat verband (Ref# errors bij verwijderen, verkeerde verwijzingen bij tussenvoegen).

Hier is-tie dan:
Bekijk bijlage Te bestellen onderdelen MB.xlsx

Edit: Orderregels!D2 mag leeg (testje blijven hangen).