Skip to the content

Excel wat is een query

Excel heeft heel veel mooie mogelijkheden en 1 van die mogelijkheden is het selecteren van gegevens uit een database zodat je berekeningen of vergelijkingen uit kunt voeren op/met deze gegevens. Het ophalen van deze gegevens doe je met een query. Het woord query is de Engelse benaming voor vraagstelling. Bij gebruik van de query in Excel stel je dus de vraag aan de database om gegevens te selecteren en deze te tonen in je werkblad.

Een eenvoudig voorbeeld

Bij dit voorbeeld gebruik ik een kleine en eenvoudige Ms Access database die je hier kunt downloaden. In deze database zijn 2 tabellen opgenomen die een relatie hebben met elkaar. Het gaat wat te ver om het principe van de relationele database uit te leggen in dit blog maar in het kort komt het erop neer dat we gegevens maar 1 keer in willen voeren. Aangezien meerdere klanten hetzelfde adres zouden kunnen delen staan de klanten en de adressen ieder in eigen tabellen en zijn deze met elkaar verbonden door een zogenaamde relatie.

In de tabel klanten koppelen we de tabel adressen waardoor een veel op één relatie ontstaat, immers 1 adres kan door veel klanten worden gebruikt.

In het eerste voorbeeld wil ik alle klantnamen uit de database ophalen.

1
2
3
4
SELECT
	K.Klantnaam
FROM 
        Klanten AS K

 

Als ik de query letterlijk in het Nederlands zou vertalen dan staat er

SELECTEER Klantnaam UIT tabel Klanten.

Met een alias (Klanten As K) verkorten we de naam Klanten tot een K. Het gebruik van een alias heeft meerdere functies. Enerzijds zorgt het ervoor dat grotere query’s leesbaar blijven (lees verderop in dit artikel) en anderzijds zorgt het ervoor dat je query compact blijft wat de laadsnelheid ten goede komt. 

Gegevens uit verschillende tabellen samenvoegen

Als ik vervolgens alle klanten wil ophalen waar de plaatsnaam gelijk is aan Seattle dan wordt het al wat lastiger immers de plaatsnamen staan in een andere tabel dan de klanten.

Het samenvoegen (JOIN) van deze gegevens doe je als volgt;

1
2
3
4
5
SELECT 
K.KlantNaam, A.Straatnaam, A.Postcode, A.Woonplaats
FROM 
Klanten AS K JOIN  Adressen AS A  ON K.PostAdresId  = A.Id 
WHERE (((A.Woonplaats)="Seattle"));

 

Welke vraagstelling hebben we nu gedaan aan de database? Probeer de query eens letterlijk te vertalen naar het Nederlands.

Selecteer de velden Klantnaam, Straatnaam, Postcode en Woonplaats

Uit de tabel Klanten samengevoegd met de tabel Adressen waarbij de link tussen beiden het veld PostAdresId is uit de tabel Klanten en het veld Id in de tabel Adressen.

Waar Woonplaats is gelijk aan Seattle

Echter wanneer je bijvoorbeeld alle klanten wilt zien en alleen de adressen als deze ook echt zijn gekoppeld dan stel je de vraag aan de database iets anders.

1
2
3
4
5
SELECT 
K.KlantNaam, A.Straatnaam, A.Postcode, A.Woonplaats
FROM 
Klanten AS K LEFT JOIN Adressen AS A ON K.PostAdresId = A.Id
WHERE (((A.Woonplaats) Is Null));

 

We stellen de vraag aan de database om de velden te selecteren uit de tabellen Klanten samengevoegd met adressen waarbij we in de samenvoeging een LEFT JOIN gebruiken. Deze samenvoeging verteld de database dat je alle gegevens uit klanten wilt zien en alleen de gegevens uit Adressen als deze ook echt bestaan.

Als er dus geen adres is gekoppeld aan de klant zal je dit terug zien in het resultaat.

Aangezien we als voorwaarde (WHERE) hebben gesteld dat het veld Woonplaats niet is gevuld kunnen we nu alle klanten tonen waar de woonplaats leeg is.

Microsoft query

Gelukkig heeft Microsoft Excel een wizard die bovenstaande kennis overbodig maakt of ligt het toch wat genuanceerder?

Laten we Microsoft Query eens openen en de tweede query proberen na te bootsen.

Stap 1: Open Microsoft Query

Open Excel en klik op het tabblad [Gegevens]. In het tabblad gegevens klik je op [Van andere bronnen] en kiest voor [Van Microsoft Query].

selecteer microsoft query | Reinder.eu

Stap 2: Selecteer de gegevensbron

Selecteer voor ons voorbeeld in de wizard [Ms Access database]

selecteer een gegevensbrond | Reinder.eu

Stap 3: Selecteer de database

Zoek de voorbeeld database op (download hier als je die nog niet hebt opgeslagen)

selecteer een database microsoft query | Reinder.eu

Stap 4: Selecteer de veldnamen

Selecteer de velden klantnaam, straatnaam, postcode en woonplaats door met het plusje de tabel te openen en te dubbelklikken op de veldnaam.

selecteer kolommen microsoft query | Reinder.eu

Stap 5: Filter de gegevens

Filter de woonplaats met als voorwaarde dat woonplaats gelijk is aan Seattle

voorwaarde selecteren microsoft query

Stap 6: Voltooi de wizard

Sorteer eventueel de gegevens en klik op volgende. Nu krijg je de keuze om de gegevens direct te tonen in je Excel werkblad of om de gegevens te bekijken in Microsoft Query. Als we nu kiezen voor [Gegevens bekijken of query bewerken in Microsoft Query] dan zien we iets geks.

Je ziet dat er een lijntje loopt van het veld Id in de tabel Adressen naar het veld Id in de tabel Klanten. Deze relatie klopt niet want nu worden de adressen getoond bij de klanten waarbij in beide gevallen het veld Id gelijk is terwijl we de adressen willen zien waarbij het Id overeenkomt met het veld PostAdresId in de tabel klanten.

vreemde relatie microsoft query

Dubbelklik maar eens op de lijn tussen beide tabellen en pas, nadat je de huidige relatie hebt verwijderd, de relatie eens aan naar onderstaande voorbeeld. Je ziet nu dat er ineens hele andere adressen staan bij de klanten.

join aanpassen microsoft query | Reinder.eu

Het maken van query's is verslavend leuk en daarom zal ik in een volgend blog wat dieper ingaan op de relationele database en voor nu hoop ik dat je iets hebt gehad aan dit blog. Heb je vragen of wil je meer weten over dit onderwerp? Neem dan contact op met Reinder.

 

"Wie op zoek is naar een teamplayer, een man van zijn woord en een uitmuntende programmeur, dan kan ik Reinder zeker aanbevelen. Reinder bedankt en tot de volgende uitdaging."

Marijn Snoek - Fleetaccess