Het opstellen en gebruiken van query's bestaat soms enkel uit het selecteren van velden in een tabel, misschien het toepassen van een aantal criteria en vervolgens het bekijken van de resultaten. Maar wat moet u doen als de gegevens zijn verdeeld over meerdere tabellen, iets wat veel vaker voorkomt? Gelukkig kunt u een query opstellen die informatie uit meerdere bronnen kan combineren. In dit onderwerp komen scenario's aan bod waarin gegevens uit meerdere tabellen moeten worden gehaald, en worden de stappen beschreven die u moet uitvoeren om het gewenste resultaat te verkrijgen.
Wat wilt u doen?
Uw query uitbreiden met gegevens uit een gerelateerde tabel
Een query op basis van één tabel levert in sommige gevallen wel de benodigde informatie op, maar de resultaten zouden duidelijker en bruikbaarder worden als het resultaat ook nog bepaalde gegevens uit een andere tabel zou bevatten. Stel dat uw queryresultaat een lijst met werknemer-id's bevat. U komt er vervolgens achter dat het handiger zou zijn als ook de namen van de werknemers worden weergegeven, maar deze namen bevinden zich in een andere tabel. In dat geval moeten beide tabellen in de query worden opgenomen, zodat het queryresultaat ook de namen van de werknemers bevat.
De wizard Query gebruiken om een query te maken op basis van een primaire tabel en een gerelateerde tabel
- Controleer in het venster Relaties (relatie: een verbinding tussen gemeenschappelijke velden (kolommen) in twee tabellen. Een relatie kan een-op-een, een-op-veel of veel-op-veel zijn.) of de tabellen een gedefinieerde relatie (venster Relaties: een objecttabblad waarin u relaties tussen tabellen en query's kunt bekijken, maken en wijzigen.) hebben.
Werkwijze
- Klik op het tabblad Databasehulpmiddelen, in de groep Weergeven/verbergen, op Relaties.
- Klik op het tabblad Ontwerpen, in de groep Relaties, op Alle relaties.
- Bepaal welke tabellen een gedefinieerde relatie moeten hebben.
- Indien de tabellen zichtbaar zijn in het venster Relaties controleert u of er al een relatie is gedefinieerd.
Een relatie wordt aangegeven met een lijn tussen de gemeenschappelijke velden in de twee tabellen. U kunt dubbelklikken op een relatielijn om te zien welke velden in de tabellen met elkaar zijn verbonden door de relatie.
- Indien de tabellen niet zichtbaar zijn in het venster Relaties voegt u de tabellen toe.
Klik op het tabblad Ontwerpen, in de groep Weergeven/verbergen, op Tabelnamen.
Dubbelklik hiervoor op elke tabel die u wilt weergeven en klik vervolgens op Sluiten.
- Als u geen relatie ziet tussen de twee tabellen, kunt u een relatie maken door een veld in een van de tabellen naar een veld in de andere tabel te slepen. De velden op basis waarvan de relatie tussen de tabellen wordt gemaakt, moeten identieke gegevenstypen hebben.
Opmerking Het is mogelijk een relatie tot stand te brengen tussen een veld met het gegevenstype AutoNummering (gegevenstype AutoNumber: in een Microsoft Access-database is dit een veldgegevenstype waarmee automatisch een uniek getal wordt opgeslagen voor elke record die wordt toegevoegd aan een tabel. Er kunnen drie verschillende nummeringen worden gegenereerd: opeenvolgend, willekeurig en replicatie-id.) en een veld met het gegevenstype Numeriek (gegevenstype Getal: in een Microsoft Access-database is dit een veldgegevenstype voor numerieke gegevens die in rekenkundige berekeningen worden gebruikt. Gebruik echter het gegevenstype Valuta voor het weergeven of berekenen van geldbedragen.), indien de eigenschap Veldlengte voor beide velden is ingesteld op Lange integer. Dit is vaak het geval wanneer u een een-op-veel-relatie (een-op-veel-relatie: een verbinding tussen twee tabellen waarbij de primaire-sleutelwaarde van elke record in de primaire tabel correspondeert met de waarde in een of meer overeenkomende velden van veel records in de gerelateerde tabel.) maakt.
Vervolgens wordt het dialoogvenster Relaties bewerken geopend.
- Klik op Maken om de relatie te maken.
Zie het artikel Een relatie maken, bewerken of verwijderen voor meer informatie over de opties die beschikbaar zijn bij het maken van een relatie.
- Sluit het venster Relaties.
- Klik op het tabblad Maken in de groep Overige, op Wizard Query.
- Klik in het dialoogvenster Nieuwe query op Wizard Selectiequery en klik op OK.
- Klik in de keuzelijst Tabellen/query's op de tabel die de basisinformatie bevat die u wilt opnemen in uw query.
- Klik in de lijst Beschikbare velden op het eerste veld dat u wilt opnemen in uw query en klik vervolgens op de knop met de pijl naar rechts om het veld te verplaatsen naar de lijst Geselecteerde velden. Herhaal dit voor elk tabelveld dat u wilt opnemen in uw query. Dit kunnen velden zijn die u in het queryresultaat wilt weergeven, of velden waarmee u het aantal rijen in het queryresultaat wilt beperken door criteria toe te passen.
- Klik in de keuzelijst Tabellen/query's op de tabel die de gerelateerde gegevens bevat die u wilt gebruiken om uw queryresultaat uit te breiden.
- Voeg de velden waarmee u de queryresultaten wilt uitbreiden toe aan de lijst Geselecteerde velden en klik vervolgens op Volgende.
- Klik onder Wilt u een detailquery of een totalenquery? op Details of op Totalen.
Kies een detailquery als u niet wilt dat er statistische functies door de query worden uitgevoerd (Som, Gem, Min, Max, Aantal, StDev of Var). Kies een totalenquery als u wel wilt dat er een statistische functie door de query wordt uitgevoerd. Klik op Volgende als u uw keus hebt gemaakt.
- Klik op Voltooien om het resultaat te bekijken.
Een voorbeeld waarin de voorbeelddatabase Noordenwind wordt gebruikt
In het volgende voorbeeld gebruikt u de wizard Query om een query op te stellen voor het weergeven van een lijst met orders, de vrachtkosten voor elke order, en de naam van de werknemer die elke order heeft behandeld.
Opmerking Door het uitvoeren van dit voorbeeld wordt de voorbeelddatabase Noordenwind gewijzigd. Het is raadzaam een back-up van de voorbeelddatabase Noordenwind te maken en voor het voorbeeld de back-up te gebruiken.
De query maken met de wizard Query
- Open de voorbeelddatabase Noordenwind.
- Klik op het tabblad Maken in de groep Overige, op Wizard Query.
- Klik in het dialoogvenster Nieuwe query op Wizard Selectiequery en klik vervolgens op OK.
- Klik in de keuzelijst Tabellen/query's op Tabel: Orders.
- Ga naar de lijst Beschikbare velden en dubbelklik op Order-id om dat veld te verplaatsen naar de lijst Geselecteerde velden. Dubbelklik op Vrachtkosten en om ook dat veld naar de lijst Geselecteerde velden te verplaatsen.
- Klik in de keuzelijst Tabellen/query's op Tabel: Werknemers.
- Ga naar de lijst Beschikbare velden en dubbelklik op Voornaam om dat veld te verplaatsen naar de lijst Geselecteerde velden. Dubbelklik op Achternaam om ook dat veld te verplaatsen naar de lijst Geselecteerde velden. Klik op Volgende.
- U wilt een lijst maken van alle orders en daarom kiest u een detailquery. Als u bijvoorbeeld de vrachtkosten per werknemer wilt optellen of een andere statistische functie wilt gebruiken, kiest u een totalenquery. Klik op Details (alle velden van alle records weergeven) en klik op Volgende.
- Klik op Voltooien om de resultaten te bekijken.
De query geeft als resultaat een lijst met orders, elk met de verzendkosten en de voor- en achternaam van de werknemer die de order heeft behandeld.
Terug naar boven
De gegevens uit twee tabellen koppelen via hun relatie met een derde tabel
Gegevens in twee tabellen zijn vaak aan elkaar gerelateerd via een derde tabel. Dit is meestal het geval wanneer de gegevens in de eerste twee tabellen zijn gerelateerd in een veel-op-veel-relatie (veel-op-veel-relatie: een verbinding tussen twee tabellen waarbij één record in een van beide tabellen kan zijn gerelateerd aan veel records in de andere tabel. Als u een veel-op-veel-relatie wilt instellen, maakt u een derde tabel (verbindingstabel) en voegt u de primaire-sleutelvelden van beide tabellen aan deze tabel toe.). Over het algemeen is het een goede gewoonte om bij het ontwerpen van een database een veel-op-veel-relatie tussen twee tabellen te splitsen in twee een-op-veel-relaties (een-op-veel-relatie: een verbinding tussen twee tabellen waarbij de primaire-sleutelwaarde van elke record in de primaire tabel correspondeert met de waarde in een of meer overeenkomende velden van veel records in de gerelateerde tabel.) waarbij drie tabellen zijn betrokken. U doet dit door een derde tabel te maken, de verbindings- of relatietabel genaamd, die een primaire sleutel (primaire sleutel: een of meer velden (kolommen) waarvan de waarden elke record in de tabel uniek identificeren. Een primaire sleutel kan geen Null-waarden bevatten en heeft altijd een unieke index. Met een primaire sleutel relateert u een tabel aan refererende sleutels in andere tabellen.) en een refererende sleutel (refererende sleutel: een of meer tabelvelden (kolommen) die verwijzen naar het veld of de velden met de primaire sleutel in een andere tabel. Een refererende sleutel geeft de relatie tussen de tabellen aan.) voor elk van de andere tabellen heeft. Er wordt dan een een-op-veel-relatie gemaakt tussen elke refererende sleutel in de verbindingstabel en de corresponderende primaire sleutel van een van de andere tabellen. In dergelijke gevallen moet u alle drie de tabellen opnemen in uw query, ook als u alleen gegevens wilt ophalen uit twee van de tabellen.
Een selectiequery maken op basis van tabellen met een veel-op-veel-relatie
- Klik op het tabblad Maken in de groep Overige, op Queryontwerp.
Open het dialoogvenster Tabel weergeven.
- Dubbelklik in het dialoogvenster Tabel weergeven op de twee tabellen die de gegevens bevatten die u wilt opnemen in uw query en op de verbindingstabel via welke ze met elkaar zijn verbonden. Klik vervolgens op Sluiten.
Alle drie de tabellen worden weergegeven in de ontwerpwerkruimte van de query en zijn via de juiste velden met elkaar verbonden.
- Dubbelklik op elk veld dat u wilt gebruiken in uw queryresultaten. Elk geselecteerd veld wordt vervolgens weergegeven in het ontwerpraster (ontwerpraster: het raster waarmee u een query of filter ontwerpt in de ontwerpweergave van de query of in het venster Geavanceerde filter-/sorteeropties. Voorheen werd het ontwerpraster voor query's het QBE-raster genoemd.) van de query.
- Gebruik in het ontwerpraster van de query de rij Criteria om veldcriteria in te voeren. Als u een veldcriterium wilt gebruiken zonder het veld in de queryresultaten weer te geven, moet u het selectievakje voor dat veld in de rij Weergeven uitschakelen.
- Als u de resultaten wilt sorteren aan de hand van de waarden in een veld, klikt u in het ontwerpraster van de query op Oplopend of Aflopend (afhankelijk van de gewenste sorteervolgorde) in de rij Sorteervolgorde van dat veld.
- Klik op het tabblad Ontwerpen, in de groep Resultaten, op Uitvoeren.
De queryresultaten worden weergegeven in de gegevensbladweergave (gegevensbladweergave: een weergave waarin gegevens uit een tabel, formulier, query, weergave of opgeslagen procedure worden weergegeven in rij- en kolomindeling. In de gegevensbladweergave kunt u velden bewerken en gegevens toevoegen, verwijderen en zoeken.).
Een voorbeeld waarin de voorbeelddatabase Noordenwind wordt gebruikt
Opmerking Door het uitvoeren van dit voorbeeld wordt de voorbeelddatabase Noordenwind gewijzigd. Het is raadzaam een back-up van de voorbeelddatabase Noordenwind te maken en voor dit voorbeeld de back-up te gebruiken.
Stel dat er een nieuwe kans voor u ligt: een leverancier in Rio de Janeiro heeft uw website gevonden en wil misschien zaken met u doen. Zij werken echter alleen in Rio en São Paulo. Ze leveren elke productcategorie van de levensmiddelen waarin u handelt. Het is een vrij groot bedrijf, en ze willen de verzekering van u dat u hen toegang kunt geven tot een potentiële omzet die zo groot is dat het voor hen de moeite waard is om zaken met u te doen. Ze willen ten minste R$20.000,00 per jaar omzetten (ongeveer €9.000,00). Kunt u hun de markt bieden die zij verlangen?
De gegevens die u nodig hebt om deze vraag te beantwoorden, kunt u vinden op twee plaatsen: in een tabel Klanten en een tabel Detailgegevens orders. Deze tabellen zijn aan elkaar gekoppeld via een tabel Orders. De relaties tussen de tabellen zijn al gedefinieerd. In de tabel Orders kan bij elke order slechts één klant horen. Dit veld is gerelateerd aan het veld Klantnummer in de tabel Klanten. Daarnaast is elke record in de tabel Detailgegevens orders gerelateerd aan slechts één order in de tabel Orders, via het veld OrderId. Zo kunnen er voor een bepaalde klant veel orders zijn, terwijl er bij elke order weer veel detailgegevens kunnen zijn.
In dit voorbeeld bouwt u een kruistabelquery op waardoor de totale omzet per jaar in de steden Rio de Janeiro en São Paulo wordt weergegeven.
De query maken in de ontwerpweergave
- Open de database Noordenwind. Sluit het aanmeldformulier.
- Klik op het tabblad Maken in de groep Overige, op Queryontwerp.
Het dialoogvenster Tabel weergeven wordt geopend.
- Dubbelklik in het dialoogvenster Tabel weergeven op Klanten, Orders en Detailgegevens orders en klik vervolgens op Sluiten.
Alle drie de tabellen worden weergegeven in de ontwerpwerkruimte van de query.
- Dubbelklik in de tabel Klanten op het veld Plaats om dit veld aan het queryontwerpraster toe te voegen.
- Typ in het queryontwerpraster, in de kolom Plaats, in de rij Criteria de tekst In ("Rio de Janeiro","São Paulo"). Hierdoor worden alleen de records in de query opgenomen met klanten in een van deze twee plaatsen.
- Dubbelklik in de tabel Orders op de velden Leverdatum en PrijsPerEenheid.
De velden worden toegevoegd aan het ontwerpraster van de query.
- Selecteer in de kolom Leverdatum in het queryontwerpraster de rij Veld. Vervang [Leverdatum] door Jaar: Format([Leverdatum],"yyyy"). Hiermee maakt u een veldalias, Jaar, zodat u het jaartal uit de waarde van het veld Leverdatum afzonderlijk kunt gebruiken.
- Selecteer in de kolom PrijsPerEenheid in het queryontwerpraster de rij Veld. Vervang [PrijsPerEenheid] door Omzet: [Detailgegevens order].[PrijsPerEenheid]*[Hoeveelheid]-[Detailgegevens order].[PrijsPerEenheid]*[Hoeveelheid]*[Korting]. Zo maakt u een veldalias, Omzet, waarin voor elke record de omzet wordt berekend.
- Klik op het tabblad Ontwerp in de groep Querytype op Kruistabel.
In het queryontwerpraster worden twee nieuwe rijen weergegeven, Totaal en Kruistabel.
- Klik in de kolom Plaats in het queryontwerpraster op de rij Kruistabel en klik vervolgens op Rijkop.
Hierdoor worden de waarden van Plaats als rijkop weergegeven, dat wil zeggen: de query geeft één rij voor elke plaats als resultaat.
- Klik in de kolom Jaar op de rij Kruistabel en klik vervolgens op Kolomkop.
Hierdoor worden de waarden van Jaar als kolomkop weergegeven, dat wil zeggen: de query geeft één kolom voor elk jaar als resultaat.
- Klik in de kolom Omzet op de rij Kruistabel en klik vervolgens op Waarde.
Hierdoor wordt op elk snijpunt van een rij met de kolom een waarde weergegeven, dat wil zeggen: de query geeft één omzetwaarde als resultaat voor elke combinatie van Plaats en Jaar.
- Klik in de kolom Omzetop de rij Totalen en klik vervolgens op Som.
Daarmee geeft de query in deze kolom de som van de waarden.
U kunt de rij Totalen voor de twee andere rijen op de standaardwaarde Groeperen op laten staan, want u wilt van deze kolommen elke waarde zien, niet de cumulatieve waarde.
- Klik op het tabblad Ontwerpen, in de groep Resultaten, op Uitvoeren.
U hebt nu een query die de totale omzet per jaar in Rio de Janeiro en São Paulo retourneert.
Terug naar boven
Alle records uit twee gelijksoortige tabellen weergeven
Soms wilt u gegevens combineren uit twee tabellen die een identieke structuur hebben, terwijl een van deze tabellen zich in een andere database bevindt. Het volgende scenario zou zich kunnen voordoen:
U bent analist en u werkt met leerlinggegevens. U doet mee aan een initiatief voor gegevensuitwisseling tussen uw school en een andere school, zodat beide scholen hun leerplan kunnen verbeteren. Voor sommige vraagstukken die u wilt onderzoeken, zou het handiger zijn als u alle records van beide scholen gezamenlijk zou kunnen bekijken, in plaats van de records van elke school afzonderlijk.
U kunt de gegevens van de andere school importeren in nieuwe tabellen in uw database, maar in dat geval worden eventuele wijzigingen in de gegevens van de andere school niet doorgevoerd in uw database. Een betere oplossing zou zijn om een koppeling tot stand te brengen met de tabellen van de andere school. Hierna zou u query's kunnen opstellen waarin de gegevens gecombineerd in de resultaten worden weergegeven. U zou de gegevens als één set kunnen analyseren, in plaats van twee analyses uit te voeren en deze vervolgens te interpreteren alsof ze één geheel zouden zijn.
Als u alle records uit twee tabellen met een identieke structuur wilt weergeven, kunt u een samenvoegquery (samenvoegquery: een query die met de operator UNION het resultaat van twee of meer geselecteerde query's combineert.) gebruiken.
Samenvoegquery's kunnen niet worden weergegeven in de ontwerpweergave. U stelt een samenvoegquery op met behulp van SQL-opdrachten die u invoert in een objecttabblad van de SQL-weergave (SQL-weergave: een objecttabblad waarin de SQL-instructie voor de huidige query wordt weergegeven of waarmee een SQL-query (samenvoegquery, Pass Through-query of definitiequery) wordt gemaakt. Als u een query maakt in de ontwerpweergave, wordt in de SQL-weergave de corresponderende SQL-instructie gemaakt.).
Een samenvoegquery maken met twee tabellen
- Klik op het tabblad Maken in de groep Overige, op Queryontwerp.
Open een nieuw queryontwerpraster waarna het dialoogvenster Tabel weergeven wordt geopend.
- Klik in het dialoogvenster Tabel weergeven op Sluiten.
- Klik op het tabblad Ontwerpen, in de groep Type query, op Samenvoegquery.
De query schakelt over van de ontwerpweergave naar de SQL-weergave. In dit stadium is het objecttabblad van de SQL-weergave leeg.
- Typ in de SQL-weergave SELECT, gevolgd door de namen van de velden uit de eerste tabel die u in de query wilt opnemen. Typ veldnamen tussen vierkante haken, gescheiden door komma's. Druk op ENTER als u klaar bent met het typen van de veldnamen. De cursor gaat één regel omlaag in de SQL-weergave.
- Typ FROM, gevolgd door de naam van de eerste tabel die u wilt opnemen in de query. Druk op ENTER.
- Als u een criterium wilt opgeven voor een veld uit de eerste tabel, typt u WHERE, gevolgd door de veldnaam, een vergelijkingsoperator (meestal het gelijkteken (=)) en het criterium. U kunt aanvullende criteria toevoegen tot aan het einde van de WHERE-component. Verbind de criteria met het sleutelwoord AND en gebruik dezelfde syntaxis als voor het eerste criterium, bijvoorbeeld WHERE [Klas]="100" AND [Studiepunten]>2. Als u klaar bent met het opgeven van criteria, drukt u op ENTER.
- Typ UNION en druk vervolgens op ENTER.
- Typ SELECT, gevolgd door de namen van de velden uit de tweede tabel die u wilt opnemen in de query. Neem dezelfde velden op als uit de eerste tabel en in dezelfde volgorde. Typ veldnamen tussen vierkante haken, gescheiden door komma's. Druk op ENTER als u klaar bent met het typen van de veldnamen.
- Typ FROM, gevolgd door de naam van de tweede tabel die u wilt opnemen in de query. Druk op ENTER.
- U kunt eventueel een WHERE-component toevoegen, zoals beschreven in stap 6 van deze procedure.
- Typ een puntkomma (;) om het einde van de query aan te geven.
- Klik op het tabblad Ontwerpen, in de groep Resultaten, op Uitvoeren.
Uw resultaten worden weergegeven in de gegevensbladweergave.
Terug naar boven
