Aggregatie query.

Met aggregeren kan men gegevens tellen, sommeren, bepalen van een gemiddelde, maximun en minimum......enz bepalen . Kenmerkend hierbij is het GROUP BY sleutelwoord in combinatie met één van bovenstaande. Aggregatie queries kan men in de regel niet bewerken, ze zijn dus read-only.

SELECT tblWat.IDCategorie, Count(tblWat.IDWat) AS TotaalWat
FROM tblWat
GROUP BY tblWat.IDCategorie
ORDER BY tblWat.IDCategorie;

Ophalen oudste of recentste info van veel-zijde tabel.

Men wil gegevens van twee relatie gebonden tabellen zien waarbij men aan de veel-zijde tabel enkel de recenste of oudste gegevens beoogt. Bijvoorbeeld wil de klanten gegevens van de laatste order-datum. Dit kan met subqueries , maar ook met een aggregatie query. Hier het voorbeeld met een aggregatie query met tblKlant aan de één-zijde en tblOrders aan de veel zijde .

Hier is de SQL-clausule :

SELECT tblKlant.IDKlant, tblKlant.KlantNaam, tblKlant.KlantVoornaam, tblKlant.KlantGemeente,
tblKlant.KlantPCode, Max(tblOrders.OrderDatum) AS LaatsteOrder
FROM tblKlant INNER JOIN tblOrders ON tblKlant.IDKlant = tblOrders.IDKlant
GROUP BY tblKlant.IDKlant, tblKlant.KlantNaam, tblKlant.KlantVoornaam, tblKlant.KlantGemeente, tblKlant.KlantPCode;

bewaard als qagLaatsteOrder
En hier de SQL-clausule voor de eerste OrderDatum :

SELECT tblKlant.IDKlant, tblKlant.KlantNaam, tblKlant.KlantVoornaam, tblKlant.KlantGemeente, tblKlant.KlantPCode,
Min(tblOrders.OrderDatum) AS EersteOrder
FROM tblKlant INNER JOIN tblOrders ON tblKlant.IDKlant=tblOrders.IDKlant
GROUP BY tblKlant.IDKlant, tblKlant.KlantNaam, tblKlant.KlantVoornaam, tblKlant.KlantGemeente, tblKlant.KlantPCode;

bewaard als qagEersteOrder

Door een SELECT querie met qagLaatsteOrder en qagEersteOrder kan men bepalen hoelang een persoon reeds klant is, hier uitgedrukt in dagen, met volgende SQL-clausule:

SELECT qagEersteOrder.IDKlant, qagEersteOrder.KlantNaam, qagEersteOrder.KlantVoornaam, qagEersteOrder.KlantGemeente,
qagEersteOrder.KlantPCode, DateDiff("d",[EersteOrder],[LaatsteOrder]) AS DagenKlant
FROM qagEersteOrder INNER JOIN qagLaatsteOrder ON qagEersteOrder.IDKlant=qagLaatsteOrder.IDKlant
ORDER BY DateDiff("d",[EersteOrder],[LaatsteOrder]) DESC;

INNER JOIN.

Een Inner Join verenigt de gegevens (data) in twee of meerder tabellen waar de geschakelde velden gelijk zijn. De rijen waarvan de geschakelde velden ongelijk zijn worden niet weergegeven.
Syntaxis :

SELECT tblA.Veld1, tblA.veld2, tblB.Veld1
FROM tblA INNER JOIN tblB ON tblA.Veld1 = tblB.Veld1

LEFT en RIGHT OUTER JOIN

Waar bij een Inner Join enkel rijen met gelijke geschakelde velden weergegeven worden, worden bij een Outer Join, naast dezelde rijen die bij een Inner Join weergegeven worden, ook de rijen van één tabel weergegeven waar de geschakelde velden niet gelijk zijn.
De rijen van de andere tabel die geen overeenkomstige waarden hebben worden weergegeven met de NULL waarde. Left (Links) en Right (Rechts) hebben betrekking op de tabel waarvan alle rijen worden weergegeven. De positie van de tabel Left of Right heeft betrekking op de positie van de tabel in de SQL-clausule. De tabel aan de linker zijde van JOIN verklaring is de LEFT tabel deze aan de rechter zijde is de RIGHT tabel.
Syntaxis :

SELECT tblA.Veld1, tbla.veld2,tblB.Veld1
FROM tblA LEFT OUTER JOIN tblB ON tblA.Veld1 = tblB.Veld1

Men krijgt alle rijen van tblA.

SELECT tblA.Veld1, tbla.veld2,tblB.Veld1
FROM tblA RIGHT OUTER JOIN tblB ON tblA.Veld1 = tblB.Veld1

'Men krijgt alle rijen van tblB.

Select Queries - Staal van willekeurige records.

' men heeft een tabel, tblData met een aanzienlijk aantal records
' men wil een staal van x records van willekeurige records zien
' in een gewone module voorziet men volgende functie

Public Function RandomGetal(varVeld as Variant)
Randomize
RandomGetal = Rnd
End Function

' voorzie de volgende query indien men vb 20 willekeurige records wil ophalen :

SELECT TOP 20 tblData.*
FROM tblData
ORDER BY RandomGetal([EenVeld]);

Select Query, Welke klanten hebben de laatste 60 dagen geen order geplaatst?

Stel een tabel tblKlant, met klanten gegevens

En een tabel tblOrders , met gegevens van de orders

Om deze vraagstelling op te lossen maakt men eerst een SELECT query van de klanten die de laatste 60 dagen wel een order geplaatst hebben, met volgende SQL-clausule :

SELECT tblOrders.IDKlant, tblOrders.OrderDatum
FROM tblOrders
WHERE (((tblOrders.OrderDatum)>=Date()-60));

bewaard als qselOrder60

Met de volgende SELECT query zoekt men de klanten die geen “match” met qselOrder60 via een LEFT JOIN waarbij alle rijen van tblKlant betrokken worden en enkel deze van qselOrder60 waar IDKlant niet voorkomt (NULL waarde) :

SELECT tblKlant.IDKlant, tblKlant.KlantNaam, tblKlant.KlantVoornaam, tblKlant.KlantPCode
FROM tblKlant LEFT JOIN qselOrder60 ON tblKlant.IDKlant=qselOrder60.IDKlant
WHERE (((qselOrder60.IDKlant) Is Null));

Een subquery is een SELECT clausule in de WHERE clausule van een andere SELECT query. Subqueries zijn in de regel trager als gelijkwaardige SELECT queries die het zelfde resultaat weergeven.

Subquery: Om een interval weer te geven.

Stel dat men wil nagaan hoeveel dagen er verlopen zijn tussen de laatse orderdatum en de vorige orderdatum van de tabel tblOrders. Dit kan met volgende SQL-clausule :

SELECT tblOrders.IDKlant, tblOrders.OrderDatum, (SELECT MAX(OrderDatum)
FROM tblOrders AS tblOpvolging
WHERE tblOpvolging.OrderDatum < tblOrders.OrderDatum
AND tblOpvolging.IDKlant = tblOrders.IDKlant ) AS VoorgaandeOpvDat,
[OrderDatum]-[VoorgaandeOpvDat] AS IntervalOpv
FROM tblOrders
ORDER BY tblOrders.IDKlant, tblOrders.OrderDatum DESC;

de geneste subquery (tussen haakjes) is hier :

(SELECT MAX(OrderDatum)
FROM tblOrders AS tblOpvolging
WHERE tblOpvolging.OrderDatum < tblOrders.OrderDatum
AND tblOpvolging.IDKlant = tblOrders.IDKlant )

De subquery kan voorkomen in een veld (gemakkelijk vast te stellen in de Access query grid, maar ook in een WHERE of HAVING clausule, waar een reeks van één of meerder waarde geëvalueerd worden.

In een veld moet de subquery één enkele waarde weergeven, normaal door het gebruik van een aggregatie zoals MAX of SUM.
In een WHERE of HAVING clausule zijn er 3 mogelijke vormen voor de subquery:
vergelijking [ANY|ALL|SOME] (subquery)
uitdrukking [NOT] IN (subquery)
[NOT] EXISTS (subquery)
Met de eerste vorm kan men een query filteren op basis van de resultaten van een andere query.

Een subquery is een SELECT clausule in de WHERE clausule van een andere SELECT query. Subqueries zijn in de regel trager als gelijkwaardige SELECT queries die het zelfde resultaat weergeven.

Subquery : Ophalen oudste of recentste info van veel-zijde tabel.

Stel een tabel van klanten tblKlant (één) en een tabel orders tblOrders (veel)

Men wil die klanten kennen met wie er nog geen order geplaast hebben. De subquery bevindt zich tussen de haakjes (). SQL-clausule :

SELECT tblKlant.IDKlant, tblKlant.KlantNaam, tblKlant.KlantVoornaam, tblKlant.KlantGes,
tblKlant.KlantGemeente, tblKlant.KlantPCode
FROM tblKlant
WHERE (((tblKlant.IDKlant) Not In (SELECT IDKLANT FROM tblOrders)));

Bij veel rijen in tblOrders duurt het relatief lang alvorens de rijen opgehaald worden. Een ‘unmatch’ SELECT query is gewoonlijk veel sneller :

SELECT tblKlant.IDKlant, tblKlant.KlantNaam, tblKlant.KlantVoornaam, tblOrders.IDKlant
FROM tblKlant LEFT JOIN tblOrders ON tblKlant.IDKlant = tblOrders.IDKlant
WHERE (((tblOrders.IDKlant) Is Null));

Bedoeling Union Query : het combineren van data uit twee tabellen of queries met dezelfde (of gelijkaardige) velden in één union query. Het aantal velden in beide bron tabellen/queries moeten dezelfde zijn. Een union query kan niet gebouwd worden via de QBE-grid in Access ( QBE = query by example).

Basis union Query.

Veronderstel een tabel tblVerkoper

En een tabel tblLeveranciers

Men wenst de persoonsgegevens van beide tabellen in een enkele queryset dat kan door volgende SQL te type in de SQL-view van de QBE :

SELECT DISTINCTROW VerkoperNaam, VerkoperVNaam
FROM tblVerkoper
UNION
SELECT DISTINCTROW LeverContNaam, LeverContVNaam
FROM tblLeveranciers
ORDER BY VerkoperNaam;

Deze union query kan bewaard zoals gebruikelijk .

Bedoeling Union Query : het combineren van data uit twee tabellen of queries met dezelfde (of gelijkaardige) velden in één union query. Het aantal velden in beide bron tabellen/queries moeten dezelfde zijn. Een union query kan niet gebouwd worden via de QBE-grid in Access ( QBE = query by example).

Een Union query omvormen naar Actie query.

Wenst men van een union query een "maak tabel" query maken moet men in Access twee stappen nemen. Men bouwt de Union query als hier boven beschreven en bewaren vb uniVerkLever. Vervolgens bouwt men de ‘maak-tabel’ query

SELECT * INTO tblUnieVerkLever
FROM uniVerkLever

Bedoeling Union Query : het combineren van data uit twee tabellen of queries met dezelfde (of gelijkaardige) velden in één union query. Het aantal velden in beide bron tabellen/queries moeten dezelfde zijn. Een union query kan niet gebouwd worden via de QBE-grid in Access ( QBE = query by example).

Union All.

Een basis union query geeft unieke rijen terug, wil men alle rijen weergeven dan moet men gebruik maken van het ALL sleutelwoord :

SELECT VerkoperNaam, VerkoperVNaam
FROM tblVerkoper
UNION ALL
SELECT LeverContNaam, LeverContVNaam
FROM tblLeveranciers
ORDER BY VerkoperNaam;

Bedoeling Union Query : het combineren van data uit twee tabellen of queries met dezelfde (of gelijkaardige) velden in één union query. Het aantal velden in beide bron tabellen/queries moeten dezelfde zijn. Een union query kan niet gebouwd worden via de QBE-grid in Access ( QBE = query by example).

Union binnen dezelfde tabel.

Deze techniek wordt soms toegepast indien men een query read-only wil maken.
Stel een tabel tblKlant

Men wenst de gegevens van de klanten van de postcode 8900 en 8400 ophalen en de gegevens mogen niet worden bijgewerkt ( wat wel het geval is met een SELECT query ) die kan door de volgende union query :

SELECT IDKlant, KlantNaam, KlantVoornaam, KlantGes, KlantGemeente, KlantPCode, KlantGebDat FROM tblKlant
WHERE KlantPCode=8900
UNION
SELECT IDKlant, KlantNaam, KlantVoornaam, KlantGes, KlantGemeente, KlantPCode,KlantGebDat
FROM tblKlant
WHERE KlantPCode =8400
ORDER BY KlantPCode;

Indien men enkel de gegevens van postcode 8900 wil ophalen terwijl deze ook niet bewerkbaar mogen zijn moet men er voor zorgen dat de tweede SELECT expressie geen rijen ophaalt :

SELECT IDKlant, KlantNaam, KlantVoornaam, KlantGes, KlantGemeente, KlantPCode, KlantGebDat
FROM tblKlant
WHERE KlantPCode=8900
UNION
SELECT IDKlant, KlantNaam, KlantVoornaam, KlantGes, KlantGemeente, KlantPCode,KlantGebDat
FROM tblKlant
WHERE KlantPCode =0
ORDER BY KlantPCode;

Actie query

Naast het opvragen van data is het soms noodzakelijk om data te wijzigen of bij te werken, te verwijderen, of rijen van één tabel naar een andere te kopiëren.
Daartoe zijn er in Ms Access 4 Actie Queries.
Opgepast alvorens men een actie query uitvoert krijgt men altijd de vraag als men met het uitvoeren wil doorgaan, eens uitgevoerd is er geen aanpassing meer mogelijk, dus bezin eer je begint !

Update of Bijwerk Query.

Om de prijs in een orders-tabel aan te passen voor orders die na een bepaalde datum geplaatst werden. De data kunnen zowel in een tabel als query bijgewerkt worden. Voorwaarde is echter dat de query bijwerken toelaat.

UPDATE tblOrders SET Prijs = [Prijs]*1.1
WHERE OrderDatum >#12/31/2010#;

De uitdrukking in de SET clausule kan zowel een constante als een berekening zijn. Bij Access zijn ook Joins bij de UPDATE clausule toegelaten.

UPDATE tblOrders INNER JOIN tblVerkoper ON tblOrders.IDVerkoper = tblVerkoper.IDVerkoper SET Prijs = [Prijs]*1.1
WHERE tblVerkoper.IDVerkoper=1;

Naast het opvragen van data is het soms noodzakelijk om data te wijzigen of bij te werken, te verwijderen, of rijen van één tabel naar een andere te kopiëren.
Daartoe zijn er in Ms Access 4 Actie Queries.
Opgepast alvorens men een actie query uitvoert krijgt men altijd de vraag als men met het uitvoeren wil doorgaan, eens uitgevoerd is er geen aanpassing meer mogelijk, dus bezin eer je begint !

Delete of Verwijder Query.

Om rijen uit een tabel te verwijderen, vb alle rijen :

DELETE tblOrders.*
FROM tblOrders;

Men kan ook voorwaarden stellen bijvoorbeeld enkel de rijen van één verkoper.

DELETE tblOrders.*
FROM tblOrders INNER JOIN tblVerkoper ON tblOrders.IDVerkoper = tblVerkoper.IDVerkoper
WHERE tblVerkoper.IDVerkoper=2;

Naast het opvragen van data is het soms noodzakelijk om data te wijzigen of bij te werken, te verwijderen, of rijen van één tabel naar een andere te kopiëren.
Daartoe zijn er in Ms Access 4 Actie Queries.
Opgepast alvorens men een actie query uitvoert krijgt men altijd de vraag als men met het uitvoeren wil doorgaan, eens uitgevoerd is er geen aanpassing meer mogelijk, dus bezin eer je begint !

Insert Into of Toevoeg Query.

Men kan met de INSERT INTO clausule om:

1. data van één tabel (of een query ) kopiëren in een andere tabel
2. of één rij aan een tabel toe te voegen op basis van een lijst met waarden voor de betrokken velden.

Veronderstel dat men gegevens tblOrders wil toevoegen in een archiveringstabel tblOrders_Archief

INSERT INTO tblOrders_Archief
SELECT tblOrders.*
FROM tblOrders;

Veronderstel dat men één rij wil toevoegen op basis van een reeks waarden :

INSERT INTO tblVerkoper (IDVerkoper,Naam,Voornaam)
VALUES (152,"Depuydt","Oswald");

Naast het opvragen van data is het soms noodzakelijk om data te wijzigen of bij te werken, te verwijderen, of rijen van één tabel naar een andere te kopiëren.
Daartoe zijn er in Ms Access 4 Actie Queries.
Opgepast alvorens men een actie query uitvoert krijgt men altijd de vraag als men met het uitvoeren wil doorgaan, eens uitgevoerd is er geen aanpassing meer mogelijk, dus bezin eer je begint !

Select InTo of Maak tabel Query.

De SELECT INTO clausule is enkel geldig voor MS Access, men maakt er een nieuwe tabel van op basis van een andere tabel of een query.

SELECT tblOrders.*, tblOrders.IDVerkoper INTO tblOrdersVerkoper5
FROM tblOrders
WHERE tblOrders.IDVerkoper=5;

Hier maakt men een nieuwe tabel tblOrdersVerkoper5 met gegevens uit tblOrders voor verkoper 5.