Het samenstellen en gebruiken van query's in Access is soms zo eenvoudig als het selecteren van velden in een tabel, wellicht het toepassen van een aantal criteria en vervolgens het bekijken van de resultaten. Maar wat als de gegevens zijn verdeeld over meerdere tabellen, iets wat veel vaker voorkomt? Gelukkig kunt u een query opstellen die informatie uit meerdere bronnen combineert. In dit onderwerp worden enkele scenario's beschreven waarin gegevens uit meerdere tabellen worden gehaald, en wordt uitgelegd hoe dit in zijn werk gaat.
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
-
Zorg ervoor dat de tabellen een gedefinieerde relatie hebben in het venster Relaties.
Werkwijze
-
Ga naar het tabblad Hulpmiddelen voor databases en klik in de groep Weergeven/verbergen op Relaties.
-
Klik op het tabblad Ontwerp in de groep Relaties op Alle relaties.
-
Geef aan welke tabellen een gedefinieerde relatie moeten hebben.
-
Als de tabellen worden weergegeven in het venster Relaties, controleert u of er al een relatie is gedefinieerd.
Een relatie wordt aangegeven met een lijn tussen een gemeenschappelijk veld 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.
-
Als de tabellen niet zichtbaar zijn in het venster Relaties, moet u ze toevoegen.
Klik op het tabblad Ontwerpen in de groep Weergeven/verbergen op Tabelnamen.
Dubbelklik op elk van de tabellen 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 hetzelfde gegevenstype hebben.
Opmerking: Het is mogelijk een relatie tot stand te brengen tussen een veld met het gegevenstype gegevenstype AutoNumber en een veld met het gegevenstype gegevenstype Getal, maar alleen als de eigenschap Veldlengte voor beide velden is ingesteld op Lange integer. Dit is vaak het geval wanneer u een een-op-veel-relatie maakt.
Het dialoogvenster Relaties bewerken wordt 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 u kunt kiezen als u een relatie gaat maken.
-
Sluit het venster Relaties.
-
-
Klik op het tabblad Maken in de groep Query's op Wizard Query.
-
Klik in het dialoogvenster Nieuwe query op Wizard Selectiequery en klik op OK.
-
Klik in de keuzelijst met invoervak 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 de query en klik vervolgens op de knop met de pijl-rechts om dat 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 het queryresultaat 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 een keuze 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 dan ook raadzaam een back-up van de voorbeelddatabase Noordenwind te maken en hier de back-up te gebruiken.
De query maken met de wizard Query
-
Open de voorbeelddatabase Noordenwind. Sluit het aanmeldingsvenster.
-
Klik op het tabblad Maken in de groep Query's op Wizard Query.
-
Klik in het dialoogvenster Nieuwe query op Wizard Selectiequery en klik 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 Verzendkosten 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 en om ook dat veld naar de lijst Geselecteerde velden te verplaatsen. Klik op Volgende.
-
U wilt een lijst maken van alle orders en daarom kiest u een detailquery. Als u bijvoorbeeld de verzendkosten 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 het resultaat 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.
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. 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 waarbij drie tabellen zijn betrokken. U doet dit door een derde tabel te maken, de verbindings- of relatietabel genaamd, die een primaire sleutel en een refererende sleutel 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 Query's op Queryontwerp.
-
Dubbelklik op de twee tabellen die de gegevens bevatten die u in uw query wilt opnemen en ook op de verbindingstabel die ze koppelt en 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 veld wordt vervolgens weergegeven in het ontwerpraster 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 het queryresultaat 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.
-
Ga naar het tabblad Ontwerpen en klik in de groep Resultaten op Uitvoeren.
Het queryresultaat wordt weergegeven in de gegevensbladweergave.
Een voorbeeld waarin de voorbeelddatabase Noordenwind wordt gebruikt
Opmerking: Door het uitvoeren van dit voorbeeld wordt de voorbeelddatabase Noordenwind gewijzigd. Het is dan ook raadzaam een back-up van de voorbeelddatabase Noordenwind te maken en hier de back-up te gebruiken.
Stel dat u een nieuwe kans hebt: een leverancier in Rio de Janeiro heeft uw website gevonden en wil misschien zaken met u doen. Ze zijn echter alleen actief in Rio en het nabijgelegen São Paulo. Ze leveren elke categorie voedselproducten die u bemiddelt. Ze zijn een vrij groot bedrijf en willen uw garanties dat u hen toegang kunt geven tot voldoende potentiële verkopen om het de moeite waard te maken: minimaal R$ 20.000,00 per jaar in verkoop (ongeveer $ 9.300,00). Kunt u hen voorzien van de markt die ze nodig hebben?
De gegevens die u nodig hebt om deze vraag te beantwoorden, bevinden zich op twee plaatsen: een tabel Klanten en een tabel Ordergegevens. Deze tabellen zijn aan elkaar gekoppeld door een tabel Orders. Relaties tussen de tabellen zijn al gedefinieerd. In de tabel Orders kan elke bestelling slechts één klant hebben, gerelateerd aan de tabel Klanten in het veld CustomerID. Elke record in de tabel Orderdetail is gerelateerd aan slechts één order in de tabel Orders, in het veld OrderID. Een bepaalde klant kan dus veel orders hebben, die elk veel ordergegevens hebben.
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 voorbeelddatabase Noordenwind. Sluit het aanmeldingsvenster.
-
Klik op het tabblad Maken in de groep Query's op Queryontwerp.
-
Dubbelklik op Klanten, Orders en vervolgensOp Orderdetails.
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 queryontwerpraster.
-
Selecteer in de kolom Leverdatum in het ontwerpraster 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 Ontwerpen 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 Omzet op 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.
-
Ga naar het tabblad Ontwerpen en klik 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.
Alle records uit twee vergelijkbare 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. Kijk eens naar het volgende scenario.
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 eventuele wijzigingen in de gegevens van de andere school worden dan niet doorgevoerd in uw database. Een betere oplossing is om een koppeling te maken naar de tabellen van de andere school en vervolgens query's te maken waarmee de gegevens worden gecombineerd wanneer u ze uitvoert. U zou de gegevens als één set kunnen analyseren in plaats van twee analyses uit te voeren en ze te interpreteren alsof ze één zijn.
Als u alle records uit twee tabellen met een identieke structuur wilt weergeven, kunt u een samenvoegquery 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.
Een samenvoegquery maken met twee tabellen
-
Klik op het tabblad Maken in de groep Query's op Queryontwerp.
-
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 SELECT in de SQL-weergave, 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 een gelijkteken (=)) en het criterium. U kunt aanvullende criteria toevoegen aan het einde van de WHERE-component met behulp van het trefwoord AND en dezelfde syntaxis die wordt gebruikt voor het eerste criterium. bijvoorbeeld WHERE [ClassLevel]="100" AND [CreditHours]>2. Wanneer u klaar bent met het opgeven van criteria, drukt u op Enter.
-
Typ UNION en druk op Enter.
-
Typ SELECT, gevolgd door een lijst met de velden uit de tweede tabel die u in de query wilt opnemen. 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.
-
Ga naar het tabblad Ontwerpen en klik in de groep Resultaten op Uitvoeren.
De resultaten van de query worden weergegeven in de gegevensbladweergave.
