Tabellen en SQL-query's
In dit practicum wordt gekeken naar de verschillende mogelijkheden, die ACCESS biedt bij het ontwerpen van tabellen. Ook leer je queries maken in de 'Ontwerpweergave'.
Tijdens dit practicum wordt de MEDIAGROEP-database gebruikt. Het ontwerp van deze database is aan de orde geweest tijdens de digitale practica over het onderwerp 'Een efficiënt database ontwerp'.
Het ontwerp van de tabel ABONNEMENT
De tabel ABONNEMENT bestaat uit de kolommen abonneenummer, tijdschrifttitel en ingangsdatum.
Omdat abonnementen alleen kunnen worden afgesloten door abonnee's, die zijn ingevoerd in de tabel ABONNEE, maken we van de kolom abonneenummer een opzoekkolom.
De gebruiker van de applicatie kan bij het invoeren van een nieuw abonnement alleen maar kiezen uit bestaande abonneenummers. Ter ondersteuning wordt ook de naam van de abonnee getoond naast het abonneenummer:
De gegevens, die in de keuzelijst komen te staan, worden m.b.v. een query uit de tabel ABONNEE gehaald:
Omdat abonnementen alleen kunnen worden afgesloten voor tijdschriften, die zijn ingevoerd in de tabel TIJDSCHRIFT, maken we ook van de kolom tijdschrifttitel een opzoekkolom:
Hieronder zie je de keuzelijst bij de kolom abonneenummer:
De kolom ingangsdatum is van het type 'Datum/tijd', en moet verplicht worden ingevuld.
Ook de wijze, waarop de ingangsdatum moet worden ingevoerd, ligt vast in een invoermasker dd-mm-jjjj:
Je kunt precies aangeven hoe het masker eruit moet komen te zien:
Het ontwerp van de query 'Overzicht afgesloten abonnementen in zelf te kiezen periode'
MEDIAGROEP wil een overzicht kunnen maken van alle abonnementen, die afgesloten zijn tussen een zelf te kiezen begindatum en einddatum.
Kies voor 'Query maken in de ontwerpweergave':
Selecteer vervolgens alle tabellen, die je nodig hebt om de query te maken:
Vervolgens kun je in de ontwerpweergave de query gaan bouwen:
- Achter 'Veld' staan de kolommen, die je nodig hebt in de query.
In deze query heb je de naam van de abonnee, de titel van het tijdschrift en de ingangsdatum van het abonnement nodig.
- Achter 'Tabel' staan de tabellen, waaruit de kolommen afkomstig zijn.
In deze query zijn dat de tabellen ABONNEE en ABONNEMENT.
- Achter 'Sorteervolgorde' kun je aangeven op welke kolom(men) er gesorteerd moet worden.
Deze query heeft geen sortering
- Achter 'Weergeven' kun je met een vinkje aangeven of de kolom wel/niet moet worden weergegeven in het resultaat van de query.
In deze query worden alle kolommen weergegeven.
- Achter 'Criteria' komen alle (samengestelde) voorwaarden, die van toepassing zijn op de kolom(men).
Tussen voorwaarden op dezelfde regel komt 'AND' te staan.
Tussen voorwaarden op verschillende regels komt 'OR' te staan.
In deze query is er een voorwaarde op de kolom ingangsdatum. De ingangsdatum van het abonnement moet liggen tussen 2 data.
Omdat de begindatum en de einddatum ingevoerd moeten kunnen worden door de gebruiker via een invoervenstertje, wordt in de voorwaarde gebruik gemaakt van blokhaken: [Geef begindatum periode:].
Terwijl je bezig bent in de 'Ontwerpweergave' maakt Access op de achtergrond alle benodigde SQL-code aan.
Deze SQL-code wijkt af van de code, die je geleerd hebt in het theorieboek.
Bij een query over 2 tabellen zouden wij de volgende code gebruiken:
SELECT naam, tijdschrifttitel, ingangsdatum
FROM abonnee, abonnement
WHERE ingangsdatum >= [Geef begindatum periode:] AND ingangsdatum <= [Geef einddatum periode:]
AND abonnee.abonneenummer = abonnement.abonneenummer;
Access gebruikt voor het koppelen van 2 tabellen de SQL-opdracht INNER JOIN ON. Ook wordt voor iedere kolomnaam de naam van de tabel geplaatst. Verder gebruikt Access erg veel haakjes:
Het ontwerp van de query 'Overzicht van het aantal abonnees per woonplaats'
Een query, waarbij wordt gegroepeerd (GROUP BY) of waarbij een functie (SUM, MAX, ...) wordt gebruikt, vereist in de ontwerpweergave een extra regel.
Je kunt deze extra regel krijgen door met de rechtermuisknop te kiezen voor 'Totalen'.
Er wordt een extra regel 'Totaal' aangemaakt:
In de extra regel 'Totaal' kun je aangeven, dat je wilt groeperen op plaats en wilt tellen op abonneenummer:
Verschillende querytypen
Access kent verschillende querytypen:
- Selectiequery en Kruistabelquery: Met een selectiequery kun je een aantal kolommen en rijen opvragen uit verschillende tabellen. Ook kun je m.b.v. een functie bijvoorbeeld totalen en gemiddelden berekenen.
Wanneer er twee kolommen worden opgevraagd gekombineerd met een functie, dan kan het gebruik van een kruistabelquery handig zijn.
- Tabelmaakquery: Met een tabelmaakquery kun je een nieuwe tabel maken, die opgebouwd is uit kolommen en rijen van bestaande tabellen en query's.
- Query bijwerken: Met een bijwerkquery kun je rijen uit een bestaande tabel wijzigen of aanvullen.
In de ontwerpweergave krijg je een extra regel 'Wijzigen in' tot je beschikking.
- Toevoegquery: Met een toevoegquery kun je extra rijen toevoegen aan een bestaande tabel.
In de ontwerpweergave krijg je een extra regel 'Toevoegen aan' tot je beschikking.
- Verwijderquery: Met een verwijderquery kun je rijen uit een bestaande tabel verwijderen.
In de ontwerpweergave krijg je een extra regel 'Verwijderen ' tot je beschikking.
Het ontwerp van de toevoegquery 'Rekeningen maken'
Abonnee's ontvangen van MEDIAGROEP ieder jaar een rekening voor hun tijdschrift in de maand, dat zij het abonnement op het tijdschrift hebben afgesloten.
De unieke rekeningnummers uit de tabel REKENING zijn allemaal op dezelfde manier opgebouwd: abonneenummer- maand- jaartal, met
abonneenummer: Dit is het unieke nummer van de abonnee, waarnaar de rekening verzonden moet worden.
maand: Dit is de maand, waarin de abonnee het abonnement op het tijdschrift heeft afgesloten.
jaartal: Dit is het jaar, waarin de rekening verstuurd wordt
Omdat er rijen moeten worden toegevoegd aan de tabel REKENING, maak je gebruik van een Toevoegquery:
En je kiest de tabel, waaraan de rijen moeten worden toegevoegd:
Met de functie Month( ) kun je uit een datum de maand selecteren.
Met de functie Year( ) kun je uit een datum het jaartal selecteren.
Met de functie Date( ) kun je de systeemdatum opvragen.
Met de functie Str( ) kun je een datum omzetten naar tekst.
Vervolgens worden alle abonneenummers opgezocht, die horen bij de abonnementen, die afgesloten zijn in de geselecteerde maand.
Tenslotte worden de nieuwe rijen toegevoegd aan de tabel:
Het ontwerp van de toevoegquery 'Rekeningregels maken'
Op de rekening, die abonnee's ontvangen van MEDIAGROEP, staan alleen rekeningregels voor de tijdschriften, waarop de abonnee in de betreffende maand een abonnement heeft afgesloten.
Rekeningregels, die geprint gaan worden op de nieuw aangemaakte rekeningen uit de tabel REKENING moeten dus voldoen aan een aantal voorwaarden:
- Het abonneenummer moet overeenkomen met het abonneenummer op de nieuw aangemaakt rekening.
- Er worden alleen rekeningregels aangemaakt voor tijdschriften, waarop in de betreffende maand een abonnement is afgesloten.
- Er worden alleen rekeningregels aangemaakt voor rekeningen, die in de betreffende maand en het betreffende jaartal zijn aangemaakt.
Natuurlijk is het mooier om de toevoegquery's 'Rekeningen maken' en 'Rekeningregels maken' direct na elkaar te laten uitvoeren.
In een volgend practicum komen we daar op terug.