Databasefuncties in query

2.4 Databasefuncties in query's

In totaalquery's gebruik je op de rij 'Totaal' in het queryontwerpscherm statistische functies zoals Som, Min,Gem. Aangezien deze enkel in totaalquery's gebruikt kunnen worden is het bijvoorbeeld niet mogelijk een 'groepswaarde' (gemiddelde, som, ...) en een individuele waarde betreffende hetzelfde veld in één query samen te brengen.
Je wil bijvoorbeeld de titel tonen van alle boeken voor Aardrijkskunde die minder kosten dan het gemiddelde voor dat vak. In zo'n geval heb je statistische domeinfuncties of databasefuncties nodig. Er zijn 2 opvallende verschillen met statistische functies:
- de (steeds Engelse) naam begint met een 'D'.
- je kan het domein opgeven waarop ze moeten toegepast worden

Syntaxis	Naam(expr, domein[, criteria])
Gebruik	in berekende velden en in criteria

    * expr is een veldnaam uit een tabel of query
    * domein is een tabel- of querynaam
    * criteria of voorwaarden zijn facultatief

Opgave: lees in het helpscherm waarvoor de functies DAvg, DLookup, DMax, DMin en DSum dienen.

Je kan volgende voorbeelden testen in het venster 'Foutopsporing'. Let op het lijstscheidingsteken. Dit moet hetzelfde zijn als bij de Landinstellingen in het Windows-configuratiescherm!

Je typt	Je ziet
?DAvg("Eenh_prijs","tblBoeken","[Vakcode]='wis'")	de gemiddelde prijs voor de boeken met vakcode 'wis'.
?DSum("Eenh_prijs","tblBoeken","[Titel] like 'd*'")	de som van de prijzen der boeken met beginletter 'd' in de titel
?DLookup("titel","tblboeken","eenh_prijs=500 and vakcode<>'eng'")	de titel van het eerste boek dat 500 kost en niet de vakcode 'Eng' heeft

Let op:

alle argumenten staan tussen dubbele aanhalingstekens
namen met spaties erin moet je tussen vierkante haakjes plaatsen
heb je tussen de dubbele aanhalingstekens nog eens aanhalingstekens nodig, dan gebruik je enkele(')

Terug naar het voorbeeld nu:

De titel van de boeken voor Aardrijkskunde die minder dan de gemiddelde prijs voor dat vak kosten vind je met onderstaande query.

Merk op:

een statistische domeinfunctie kan gebruikt worden in een criterium bij een query

Onderstaande query toont de boeken voor een te kiezen vak (parameterquery) die duurder zijn dan het vakgemiddelde. Het vakgemddelde zelf wordt ernaast afgebeeld.

Merk op:

een statistische domeinfunctie kan gebruikt worden in een berekend veld
in de criteria ervan kan je verwijzen naar een ingevoerde parameterwaarde
een berekend veld met een statistische domeinfunctie erin levert een tekstwaarde op. Om opmaakkenmerken te kunnen toevoegen is ze omgezet naar de getalwaarde met de functie CDbl

Oefeningen query’s

Totaalquery’s

Zoek in ‘tblBoeken’:

aantal boeken per vak

aantal boeken per uitgeverij

aantal huurboeken/koopboeken

de prijs van het duurste boek per vak

de prijs v/h duurste boek per vak én het aantal boeken per vak

de prijs van het duurste boek

de voorraadwaarde van de boeken per vak

de vakken waarvoor het duurste boek minder dan 500 BEF kost

de prijs van het duurste boek per vak beneden 500 BEF (2 query’s).Je kan een query i.p.v. een tabel als basis voor een andere query gebruiken.

Zoek in ‘tblLeerlingen’:

het aantal leerlingen per klas

het aantal leerlingen per niveau (eerstes, tweedes, …)

het aantal leerlingen per graad (eerste, tweede, derde,vierde(voor het 7e jaar))

Kruistabelquery’s

Zoek in ‘tblBoeken’:

aantal boeken per vak en per soort (H/K)

idem vorige én het totaal aantal boeken per vak

aantal boeken per vak en per uitgeverij

idem vorige én het totaal aantal boeken per uitgeverij

aantal boeken per vak en per prijsklasse (0-200,200-400,..)

idem vorige én het totaal aantal boeken per vak

Zoek in ‘tblKlassen’:

aantal leerlingen per klas en per onderwijstype

aantal leerlingen per niveau per onderwijsvorm

idem vorige én het totaal per niveau

aantal klassen per niveau per onderwijsvorm

idem vorige én het aantal leerlingen per niveau

Zoek in ‘tblLeerlingen’:

g) aantal leerlingen per klas en per niveau

h) aantal leerlingen per klas en per graad

Oefeningen op te lossen mits toevoeging van het veld ‘Geboortedatum’ in ‘tblLeerlingen’

Het veld ‘Geboortedatum’ kan je laten invullen door een bijwerkquery uit te voeren en bij het vakje ‘Wijzigen in’ van het veld 'Geboortedatum'de functie ‘GebDat’ te laten uitvoeren. Deze moet eerst ingevoerd worden in een module. De functie heeft als parameter het eerste teken van de klascode van de betreffende leerling. Je vult dus bij Wijzigen in '=GebDat(Left([Klascode];1)' in.

Public Function GebDat(strX As String) As Single 'parameter=niveau vd leerling(1-7)
Dim intDubbelaar As Integer
Dim datVan, datTot As Date
datVan = DateSerial(Year(Now()) - (11 + CInt(strX)), 1, 1)
datTot = DateSerial(Year(Now()) - (11 + CInt(strX)), 12, 31)
GebDat = Int(Rnd * (datTot - datVan + 1)) + Int(datVan)
'10 procent kans op dubbelaars
intDubbelaar = CInt(Rnd * 10) + 1
If intDubbelaar >= 9 Then
GebDat = DateSerial(Year(GebDat) - 1, Month(GebDat), Day(GebDat))
End If
End Function

Bepaal zelf het soort query: selectiequery, totaalquery, kruistabelquery.

leerlingen uit een te kiezen richting

de naam van de leerlingen met hun verjaardag dit jaar.

aantal leerlingen per geboortemaand per niveau

aantal leerlingen per geboortemaand per niveau plus het totaal aantal per niveau

aantal leerlingen gegroepeerd per dag van de verjaardag (zondag, maandag, …)

idem vorige doch per klas en enkel voor de eerstes

alfabetische lijst der leerlingen met geboortedatum, verjaardag en exacte leeftijd

aantal verjaardagen per weekdag

op welke dagen verjaren meerdere leerlingen?

op welke dag van de week verjaart het grootst aantal leerlingen?

lijst van leerlingen met studie-achterstand

aantal leerlingen per klas met studie-achterstand

Databasefuncties

Wat wordt afgebeeld bij uitvoering van onderstaande query?
Merk op dat in de functie DMin bij de criteria verwezen wordt naar het veld 'Vakcode' uit de query.
Kan je hetzelfde resultaat met een totaalquery bereiken?

Maak een lijst met per klas de naam van de jongste leerling(e) en zijn/haar geboortedatum.