Relationele databases bouwen en benaderen met Excel en Microsoft Query

Wat zijn relationele databases precies?

Een relationele database is opgebouwd uit twee om meer tabellen die in onderlinge relatie met elkaar staan. Dit onderscheidt een relationele database van een eendimensionale tabel waarmee we doorgaans in Excel werken.

 

Ten aanzien van de structuur en opbouw van individuele tabellen binnen een relationele database gelden verschillende regels. Binnen een relationele database dient ernaar te worden gestreefd om evenveel aparte tabellen te creëren als dat er entiteiten zijn. Iedere tabel dient zoveel mogelijk slechts één entiteit (gegevenssoort) te beschrijven.

 

Een andere belangrijke regel is dat elke tabel een identificerend uniek veld (de zogenaamde primaire sleutel) moet bezitten of anders minstens een combinatie van velden waarmee elk record uniek te identificeren is.

 

Om tenslotte de verschillende tabellen te kunnen verbinden zijn zogenaamde verwijssleutels nodig. Een verwijssleutel is het veld dat gekoppelde tabellen gemeenschappelijk bezitten.

In dit verband kun je enigszins een parallel trekken met de functie “verticaal zoeken” van Excel, waarbij je een gemeenschappelijk veld gebruikt om twee bereiken (“tabellen”) te verbinden.

 

Het voordeel van relationele databases ligt met name op het vlak van onderhoudbaarheid en gegevensintegriteit. Door het onderhoud en beheer van tabelstructuren en tabellen los te koppelen van de gewenste “vulling” (ook wel aangeduid als “query” of “view”) worden de gegevensintegriteit en de betrouwbaarheid binnen een relationele database vaak beter geborgd dan in een niet relationele omgeving.

 

Excel biedt om te beginnen een praktisch en breed beschikbaar platform om relationele databases te ontwerpen en te bouwen. Dat kan simpelweg door (bij voorkeur per tabblad) benoemde tabellen op te nemen. Om hier op een “relationele” manier mee aan de slag te kunnen halen we vervolgens Microsoft Query (MS Query) van stal. 

 

MS Query is een binnen Excel geïntegreerd programma waarmee je gegevens kunt ophalen uit externe bronnen, zoals bedrijfsdatabases gebaseerd op  Microsoft Office Access of Microsoft SQL Server. Maar met MS Query kun je ook gewoon gegevens binnenhalen uit een zelf gemaakte, relationeel gestructureerde Excel werkmap.  

 

  

 

Na het binnenhalen van de binnen Excel benoemde tabellen kunnen je in MS Query o.b.v. SQL selecties naar hartenlust selecteren en filteren. Hiervoor hoef je geen SQL deskundige te zijn. Je komt een heel eind op basis van de meest voorkomende opdrachten, die standaard beschikbaar zijn

 

Selecties en filteringen kun je vervolgens als query benoemen en opslaan voor meervoudig en terugkomend gebruik. Het resultaat van een query kun je tot slot op elk gewenst moment naar Excel retourneren, om er verdere bewerkingen en berekeningen op los te laten. Op het gebied van rekenen blijkt MS Query (maar dat geldt voor nagenoeg alle relationele databasesystemen) namelijk minder sterk. Een andere reden om gegevens regelmatig naar Excel te retourneren is gelegen in de beperkte opmaakmogelijkheden binnen MS Query.

 

Om te beginnen bouwen we in Excel een relationele database, waarbij we binnen het bestand “regioverkopen_ 2011.xls” de volgende vier tabellen op verschillende tabbladen plaatsen:

• Klanten

• Orders

• Verkopers

• Regio

 

De tabel “klanten” is opgebouwd uit klantrecords (rijen) en bevat de volgende 4 velden  (kolommen):

• Klantnummer;

• Klantnaam;

• Regio_ID;

• Betaalwijze
 

 

Binnen de tabel “Klanten” vormt het veld “Klantnr” de primaire sleutel. Het veld “Regio_ID” is de verwijssleutel naar de tabel “Regio”. Het totale bereik $A$1: $D$40 voorzien we, via Invoegen -> Naam definiëren (of vanaf Office 2007 via Formules -> Naam bepalen ) van de naam “Klanten”:

 

 

De tabel “Orders” is opgebouwd uit orderrecords en bevat de volgende 6 velden:

• Ordernr;

• Orderdatum;

• Afleverdatum;

• Klantnr;

• WerknemersID;

• bedrag
 

Binnen deze tabel is het veld “Ordernr” de primaire sleutel, en vormen de velden “Klantnr” en “WerknemersID” de verwijssleutels naar respectievelijk de tabellen “Klanten” en “Werknemers”. Het totale bereik $A$1: $F$200 voorzien we, via Invoegen -> Naam definiëren  (of binnen Office 2007 via Formules -> Naam bepalen ) van de naam “Orders”.

 

De tabel “Verkopers” is opgebouwd uit records met verkopers en bevat 3 velden met de volgende werknemerskenmerken:

• WerknemersID;

• Werknemersnaam;

• Bonus %;

 

Binnen deze tabel is het veld “WerknemersID” de primaire sleutel, en vormt tegelijkertijd de verwijssleutel naar de tabel “Orders”.

Het totale bereik $A$1: $B$7 voorzien we, via Invoegen -> Naam definiëren (of binnen Office 2007 via Formules -> Naam bepalen ) van de naam “Verkopers”.

 

De tabel “Regio” is opgebouwd uit records met regiogegevens en bevat de volgende velden:

• RegioID;

• Regionaam;

• Inwoneraantal;

 

 

 

Binnen deze tabel is het veld “RegioID” de primaire sleutel, en vormt tegelijkertijd de verwijssleutel naar de tabel “Klanten”.

Het totale bereik $A$1: $C$10 voorzien we, via Invoegen -> Naam definiëren  (of binnen Office 2007 via Formules -> Naam bepalen ) van de naam “Regio”.

 

Door in het vak “verwijst naar” gebruik te maken van de functie “VERSCHUIVING” kun je de benoemde bereiken in de verschillende tabellen variabel maken. Hiermee bereik je dat bij toevoeging van rijen aan een tabel de verwijzing in de pas blijft lopen met het actuele aantal rijen dat is opgenomen in de tabel.

 

Nemen we als voorbeeld de tabel “Verkopers” dan maken we deze variabel m.b.v. 

=VERSCHUIVING(Verkopers!$A$1;0;0;AANTALARG(Verkopers!$A:$A;3)

Op soortgelijke wijze kunnen de bereiken in de andere tabellen variabel worden gemaakt.

 

Om met de in Excel aangemaakte database aan de slag te kunnen, starten we MS Query in Excel 2003 op via de menukeuze Data -> Externe gegevens importeren -> Nieuwe databasequery.

 

Vanaf Excel 2007 is MS Query te bereiken via het tabblad Gegevens -> Van andere bronnen -> Van MS Query. In het verschijnende dialoogscherm kiezen we binnen het tabblad “Databases” voor het onderdeel “Excel-bestanden” en klikken, na het plaatsen van een vinkje bij het veld “Query’s maken/bewerken met behulp van de wizard Query” op OK.

(de eerste keer laten we ons bijstaan door de Wizard !).

In de eerste Wizard stap wordt gevraagd het bestand te selecteren met de database die we zojuist hebben aangemaakt.

 

We kiezen voor “regioverkopen_2011” en klikken op OK. Hierna verschijnt een dialoogvenster, waarbij we aan de linkerkant de aangemaakte tabellen herkennen.

Als we per tabel (hoeft niet te worden opengeklikt!) op het pijltje naar rechts klikken worden in het rechterblok alle tabelvelden zichtbaar. Dat doen we voor alle vier de tabellen.

 

Als we vervolgens op “Volgende >” klikken verschijnt een waarschuwingsvenster waaruit blijkt dat de Wizard niet automatisch tot een “join” (relationele verbinding) komt voor de vier tabellen. 

 

We klikken op OK en zien MS Query verschijnen met daarin de nog niet onderling gekoppelde tabellen in het bovenste deel.
 

 

In het scherm zien we van links naar rechts bovendien de volgende pictogrammen verschijnen waarmee we de belangrijkste handelingen kunnen uitvoeren:

 

 

We gaan eerst de nog ontbrekende koppelingen (“joins”) toevoegen op basis van de eerder aangegeven verwijsvelden tussen de vier tabellen. Dit doen we door de betreffende velden te verslepen tussen de tabellen.

 

Bovendien verslepen we de tabellen onderling, voor een beter overzicht, in de volgorde “Verkopers”, “Orders”, “Klanten, “Regio”. Als in het onderste (resultaat)deel al gegevens zijn opgenomen maken we dit deel eerst leeg door de kolommen te selecteren en op de “delete” knop te klikken, of na selectie van de kolommen te kiezen voor “bewerken”->”verwijderen”.

  

 

Vervolgens dubbelklikken we op de (verbindende) velden Werknemersnaam, Klantnr, Klantnaam, Regionaam en bedrag. In het middelste deel (het “criteriumdeel”) kunnen filtercriteria worden opgenomen. Dit deel vullen we nog niet. Tot slot drukken we op het uitroepteken om het resultaatscherm te vullen.

 

 

 

Voordat we daadwerkelijk aan de slag gaan met selecties en filteringen slaan we onze eerste resultaat alvast op als een (nog) ongefilterde query. Dit kan door op de bekende manier te kiezen voor bestand -> “opslaan als” of door op het  opslaan symbooltje te klikken.

We noemen deze eerste query “Regioverkopen_2011”. Queries krijgen binnen MS Query altijd standaard de extensie “.dqy” mee. 

 

Om met de zojuist aangemaakte Query aan de slag te gaan roepen we MS Query weer op via Data -> Externe gegevens importeren -> Nieuwe databasequery. We gaan nu direct naar het tweede tabblad met het opschrift Query’s en zien daar de zojuist aangemaakte Query “Regioverkopen_2011” tussen staan.

 

 

 

Als we de Query openen komen het eerder aangemaakte relatiediagram en de aangemaakte resultaatset weer tevoorschijn.

 

Wil je al direct met het query resultaat aan de slag dan kun je op het vierde symbooltje van links klikken.

 

 

Hiermee worden de gegevens uit het resultaatdeel direct naar Excel getransporteerd en sluit MS Query zich automatisch.

 

We gaan nu filteren binnen de database door in het criteriumdeel onder werknemersnaam “Willem” en onder klantnaam “bevers” in te vullen.

Hiermee krijgen we, als we op het uitroepteken klikken, uitsluitend de verkoopgegevens te zien voor verkoper “Willem” in relatie tot de klant “bevers”. Ofwel een “EN” relatie tussen verkoper “Willem” en klant “bevers”.

 

 

 

Wat we achter de schermen met  SQL gedaan hebben blijkt als we op de SQL button klikken:

 

 

De opgenomen SQL instructies (waarvan alleen het onderste deel in het SQL scherm staat weergegeven) kunnen we grofweg in 4 hoofdmoten hakken:

 

M.b.v. de “SELECT” opdracht maken we allereerst een selectie binnen de verschillende tabellen en velden. 

 

SELECT Verkopers.Werknemersnaam, Orders.Klantnr, Klanten.Klantnaam, Regio.Regionaam, Orders.bedrag.

 

Vervolgens wordt met de opdracht “FROM” naar de bron van de verschillende tabellen binnen de Excelmap “regioverkopen_2011” verwezen. 

 

FROM `C: regioverkopen_2011`.Klanten Klanten, `C: regioverkopen_2011`.Orders Orders, `C:regioverkopen_2011`.Regio Regio, `C:regioverkopen_2011`. Verkopers Verkopers.

 

Met de opdracht “Where” wordt gekeken naar de joins tussen de corresponderende velden van de verschillende tabellen.

 

WHERE Klanten.Klantnr = Orders.Klantnr AND Klanten.RegioID = Regio.RegioID AND Orders.WerknemersID = Verkopers.WerknemersID.

 

Met de opdracht “GROUP BY” wordt standaard op veldniveau van links naar rechts gegroepeerd.

 

GROUP BY Verkopers.Werknemersnaam, Orders.Klantnr, Klanten.Klantnaam, Regio.Regionaam, Orders.bedrag.

 

en tot slot herkennen we de toegepaste EN filter in de vorm van de “HAVING” opdracht.

 

HAVING (Verkopers.Werknemersnaam='Willem’) AND (Klanten.Klantnaam='bevers').

 

De SQL opdrachten kunnen binnen het SQL scherm eventueel handmatig aangepast worden. Dit raden we vooralsnog alleen aan voor gebruikers die voldoende kaas gegeten hebben van SQL.

 

Om de werking van een “OF” opdracht te tonen gaan we verder binnen de al bestaande query en voegen daar op het tweede niveau, binnen het criteriumvenster, de werknemersnaam “Ine” en (wederom) de klantnaam “bevers” aan toe. Om het resultaat te zien drukken we weer op het uitroepteken op de menubalk. We zien nu het resultaat waarbij “Willem” of “Ine” een order hebben afgesloten bij klant “bevers”.

 

 

 

Omdat we benieuwd zijn naar de tegenhanger in SQL, klikken we weer op de SQL button. We zien dat bij het “HAVING” onderdeel de OR opdracht is toegevoegd in de vorm van een laatste regel.

 

HAVING (Verkopers.Werknemersnaam='Willem') AND (Klanten.Klantnaam='bevers') 

OR (Verkopers.Werknemersnaam='Ine') AND (Klanten.Klantnaam='bevers').

 

We slaan tot slot het gefilterde resultaat op onder de naam Willem_Ine_bevers.dqy.

 

Aan het begin van dit artikel hebben we handmatig verbindingen aangebracht tussen de corresponderende velden van tabellen, in de vorm van joins.

Voor de uitleg van de belangrijkste soorten joins concentreren we ons voor het gemak even op de join tussen het veld “klantnr” in de tabellen “Orders” en “Klanten”.

 

 

 

Als we dubbelklikken op deze join verschijnt een dialoogscherm waarbij  standaard het eerste keuzerondje van de “Inner Join” geselecteerd is.

 

 

De overige joins onder het veld “Joins in query” hebben we verwijderd, via de gelijknamige knop.

 

Bij het eerste keuzerondje verschijnt in het onderste deel van het venster de omschrijving “Inner Join”. Bij een inner join worden uitsluitend corresponderende records getoond. Om de werking van de inner join te tonen hebben we vooraf de velden Klanten.klantnummer, Orders.Ordernr en Orders.klantnummer geselecteerd in het resultaatvenster.

Deze inner join leidt in ons voorbeeld tot 188 corresponderende records.

 

De keuze voor het tweede of derde rondje heeft een “Outer Join” tot resultaat. In het geval van een outer join worden ook de klantnummers getoond waarbij geen sprake is van een corresponderende waarde in het “middelste” veld (“ordernr”). Een outer join kan zich ter linker of ter rechterzijde bevinden. In dit verband spreekt met in de praktijk ook wel van een “left”- of “right outer” join.

 

Als we kiezen voor het tweede keuzerondje worden alle klantnummers uit de tabel “klanten” getoond. 

 

 

 

Dit betreft enerzijds de klantnummers waarvoor een ordernummer bestaat (de inner join), maar bovendien ook de klantnummers waarvoor een ordernummer ontbreekt. Dit zien we ook als we naar het resultaatvenster kijken.

 

In het bovenste deel zien we ter rechter zijde de 5 klantnummers (1000, 1010, 1013, 1019, 1021) waarvoor geen ordernummers bekend zijn. Voor het daaronder gelegen deel, dat volledig overeenkomt met de inner join, is wel sprake van een verbinding. 

 

Het gaat in dit geval om 193 records: het resultaat van de inner join plus de 5 cliënten waarvoor geen ordernummer bekend is.

 

Als we kiezen voor het derde keuzerondje worden alle klantnummers getoond waarvoor een ordernummer bekend is (de inner join) plus de  klantnummers waarvoor in de tabel “klanten” geen klantnummer bekend is. (dit doet vermoeden dat de afdeling verkoop zelfstandig klantnummers kan aanmaken zonder deze direct te verwerken in de tabel klanten!)

 

In het bovenste deel van het resultaatvenster zien we deze 11 klantnummers ter linkerzijde. Het resultaat bedraagt nu 199 regels: het aantal regels van de inner join plus de 11 cliënten waarvoor nog geen klantnummer is aangemaakt in de tabel “klanten”.

 

We spreken tenslotte van een “volledige Outer join” of “Union” als we de resultaten van het tweede en derde keuzeronde combineren. Daarmee krijgen we in ons voorbeeld 188 plus 5 plus 11 resultaatregels regels te zien. Dit omvat alle cliënt- en alle ordernummers

Binnen MS Query kan een Union alleen handmatig in het SQL scherm verwerkt worden. Dit valt buiten het bestek van ons artikel.

 

De rekenmogelijkheden binnen MS Query houden niet over. Dit onderscheidt MS Query niet van andere databasesystemen. Binnen MS Query is het niet mogelijk om snel even een formule in een resultaatveld te plaatsen en deze te kopiëren naar onder- of naastgelegen velden. Berekeningen vinden altijd plaats op veldniveau en dienen gedefinieerd te worden in de vorm van een berekend veld.

 

Om dit te illustreren gaan we terug naar de eerder opgeslagen Query “Regioverkopen_2011”. We voegen hier het veld “bonusperc” aan toe door hierop te dubbelklikken in de tabel “Verkopers”. Het veld verschijnt daarmee als toegevoegd veld aan de rechterkant van de resultaatset. Als we vervolgens op het uitroepteken klikken wordt het veld ook direct gevuld met de bijbehorende bonuspercentages per verkoper.

 

Maar we willen graag dat de kolommen “bedrag” en “bonuspercentage” met elkaar vermenigvuldigd worden om daarmee de bonus per order te bepalen. Daarvoor voegen we een nieuw veld in, rechts van het veld “bonusperc”. We gaan in de kop van het nieuwe veld staan en vullen in: bedrag*bonusperc

 

Als we vervolgens op het uitroepteken klikken worden de waarden van de kolommen “bedrag” en “bonusperc” vermenigvuldigd in de nieuwe kolom. Omdat we de naam van het veld willen veranderen in “bonus” (i.p.v. “bedrag*bonusperc”) dubbelklikken we tot slot op de kolomkop en vullen we “bonus” in op het dialoogvenster.

 

 

Hiermee is de exercitie voltooid en verschijnt als het goed is het volgende resultaatvenster:

 

 

Maar veel handiger is het (zie de tip “resultaat transporten naar Excel”) om geen rekenveld toe te voegen, de query naar Excel te transporten, en daar het rekenwerk te verrichten. 

 

De meeste Excel gebruikers benutten Excel primair als geavanceerd rekenplatform.

Daarnaast valt met Excel ook prima te sorteren en te filteren, zolang het aantal kolommen binnen een “eendimensionale” tabel maar binnen de perken blijft. In dit verband kunnen de schier onbeperkte mogelijkheden van een draaitabel niet onvermeld blijven.

 

Word echter de achterliggende gegevensstructuur complexer en omvangrijker, dan ontstaat vroeg of laat de noodzaak om relationeel te gaan denken.

 

Daarmee hoef je nog geen afscheid te nemen van Excel.

 

Door gebruik te maken van MS Query combineer je het beste van twee werelden: én je kunt tabellen relationeel gaan benaderen én je kunt blijven rekenen (met name in de vorm van de retourneerknop waarmee je op elk gewenst moment query resultaten naar Excel kunt transporteren).

 

In dit artikel hebben we, ook om het inzicht in relationele databases te vergroten, een database vanaf de grond in Excel opgebouwd. Vanzelfsprekend hoef je deze (om)weg niet te bewandelen als er op de achtergrond al een externe database aanwezig is. In de meeste gevallen heeft MS Query hiervoor wel een stuurprogramma aan boord.

 

In dit geval laadt je de externe basis aan de voorkant in MS Query en transporteer je de resultaten van gemaakte queries via de retourneerknop naar Excel.