Iemand vroeg mij vorige week of ik kon helpen:

“Ik heb een bestand met leningnummers. Daar staan ook polisnummers en poliswaarden in, maar ze staan per lening onder elkaar; ik wil voor iedere lening 1 regel met daarachter alle polisgegevens.”

Nog net voordat ik begon te lachen (“Dat is zo geregeld!”, dacht ik) hoorde ik dat het bestand in Access stond. Oeps, dat wordt lastiger.
Ik zal alvast verklappen: het heeft me wel wat hoofdbrekens gekost. Ik denk ook nog steeds dat het simpeler moet kunnen. Dus bij deze een oproep aan Access-kenners: kom met een betere oplossing!

Excel-oplossing

Voordat we het probleem in Access gaan aanpakken, eerst maar even een oplossing in Excel; dat is iets makkelijker!

Hiernaast staat een voorbeeldje van het bestand: zoals te zien is horen bij LenNr=1 meerdere Polisnr’s etc.

In het Excel-voorbeeldbestand zijn deze gegevens in het tabblad Basis terug te vinden.

In het tweede tabblad (Tussen) is een kopie gemaakt van deze gegevens en heb ik de kop van de tabel al uitgebreid met Polis1, Waarde1, Polis2 etc. In het voorbeeld is het maximaal aantal polissen per lening 4, maar ik heb al ruimte gemaakt voor 5 polissen:

Via wat handige formules kunnen we het bestand uitbreiden; maar zorg wel eerst dat alle regels van dezelfde lening onder elkaar komen, dus sorteren op LenNr.

1. onder het kopje Polis1 komt het polisnummer van de eerste lening; eigenlijk zouden we in die cel (E3) kunnen verwijzen naar C3, maar ik heb daar een formule geplaatst, die direct naar beneden gekopieerd kan worden:
   =ALS(B3<>B2;C3;””)
   Dus als in kolom B een ander LenNr staat dan in de vorige regel, dan de inhoud van kolom C kopiëren, anders de cel leeg laten (2 “-tekens achter elkaar).
2. onder Waarde1 (cel F3 dus) staat de formule
   =ALS(E3<>””;D3;””)
   Als in de vorige stap een polisnummer in ingevuld dan ook de waarde daarvan ophalen.
3. Dan onder Polis2 (cel G3):
   =ALS(E3<>””;ALS(B4=B3;C4;””);””)
   We willen alle polisnummers op eenzelfde regel krijgen, dus Polis2 mag alleen maar ingevuld worden als er ook een Polis1 is (ALS(E3<>””).
   Daarna vergelijken we het LenNr van de huidige regel met de volgende regel; als dat hetzelfde nummer is dan halen we het betreffende polisnummer op
4. Voor Waarde2 geldt hetzelfde als hiervoor bij Waarde1.
5. De formule onder Polis3 mag niet meer verrassend zijn:
   =ALS(G3<>””;ALS(B5=B4;C5;””);””)
6. Ook de andere kolommen krijgen vergelijkbare formules en dan alles naar beneden kopieren.

1. klik met de muis ergens in de tabel met gegevens
2. kies de menutab Gegevens
3. en binnen het vak Sorteren en filteren de optie Filter
4. klik op het ‘vinkje’ achter Polis1 (de filteropties voor deze kolom)
5. zorg dat helemaal onderaan het vinkje bij (Lege cellen) uit staat
6. selecteer de overblijvende gegevens en kopieer die naar een ander tabblad (zie Resultaat)

Access

Nu gaan we hetzelfde doen in Access.
In het Access-voorbeeldbestand (dit is gezipt, omdat de download anders vast kan lopen) staat hetzelfde basisbestand, in de tabel Basis.

Om problemen te voorkomen bij het wegschrijven van de tussenstappen, creëren we eerst de Tussen-tabel met per veld het juiste gegevenstype.
NB Wanneer we Access de tabel laten aanmaken via een Tabelmaak-query, gaat Access ‘gokken’ welke type het veld moet krijgen; dat strookt lang niet altijd met onze wensen!

Dan de eerste stap: via een query selecteren we van alle leningnummers het kleinste polisnummer (had ook de maximum-waarde kunnen zijn).
Via de button  wordt dit een Toevoeg-query; records toevoegen aan de hiervoor aangemaakte tabel Tussen.

NB Access groepeert zelf per leningsnummer; de records hoeven dan ook niet gesorteerd te zijn, in tegenstelling tot de Excel-oplossing.

Het ophalen van een mogelijk tweede polisnummer is al wat ingewikkelder:

Vanuit Basis selecteren we opnieuw de minimale waarde voor het polisnummer, maar via de zogenaamde outer-joins naar de Tussen-tabel wordt er voor gezorgd, dat combinaties van LenNr en PolisNr, die al voorkomen, niet meer meedoen (zie bij Criteria de voorwaarde Is Null).

Ook voor de volgende polisnummers zijn op vergelijkbare manier query’s gebouwd, waarbij meerdere koppelingen met dezelfde Tussen-tabel zijn aangemaakt.

De tussentabel bevat nu records, waarbij leningnummers nog wel in verschillende records voorkomen, maar waar wel al de polisnummers op de ‘juiste’ plaats staan.

Ook missen we de poliswaardes nog in de tussentabel. Dat is opgelost door 5 Bijwerk-query’s, waarmee vanuit de Tussen-tabel aan de hand van LenNr en PolisNr de overeenkomende waarde in Basis wordt opgehaald:

Om het gewenste overzicht te krijgen (alle leningnummers uniek met daarachter één of meerdere polisnummers) hebben we nog een laatste query nodig.

Transponeren

Voor diegene, die zich door de titel van het artikel (Van Rijen naar Kolommen) hebben laten misleiden, nog een toegift:
soms wil je in Excel gegevens, die onder elkaar staan, naast elkaar hebben of andersom. Hiervoor zijn geen ingewikkelde formules nodig: Excel heeft daar een ingebouwd commando voor:

1. selecteer in het Excel-voorbeeldbestand alle relevante gegevens in het tabblad Basis
2. klik met de rechter muisknop en kies Kopiëren
3. selecteer de cel waar het resultaat moet komen; dit kan ook in een ander tabblad of in een andere werkmap zijn.
4. klik daar opnieuw met de rechter muisknop
5. en kies nu Plakken speciaal…
6. in het vervolgscherm staat een optie Transponeren; zet het vinkje aan en kies OK