Samenvattingsgegevens beter leesbaar maken met een kruistabelquery

Als u de ontwerpweergave gebruikt om een kruistabelquery te maken, kunt u zo veel recordbronnen (tabellen en query's) gebruiken als u wilt. U kunt het ontwerp echter eenvoudig houden door eerst een selectiequery te maken die alle gewenste gegevens ophaalt en vervolgens die query op te geven als de enige recordbron voor uw kruistabelquery. Zie de sectie Zie ook voor meer informatie over het maken van een selectiequery.

Wanneer u een kruistabelquery maakt in de ontwerpweergave, gebruikt u de rijen Totaal en Kruistabel in het ontwerpraster om aan te geven welke veldwaarden kolomkoppen worden en voor welke veldwaarden een totaal, gemiddelde, aantal of iets anders moet worden berekend.

1. De instellingen in deze rijen bepalen of het veld een rijkop, kolomkop of samenvattingswaarde is.

2. Met deze instelling worden de veldwaarden weergegeven als rijkoppen.

3. Met deze instelling worden de veldwaarden weergegeven als kolomkoppen.

4. Met deze instellingen verschijnen de samenvattingswaarden.

De query maken

1. Klik op het tabblad Maken in de groep Query's op Queryontwerp.

   

2. Dubbelklik in het dialoogvenster Tabel weergeven op elke tabel of query die u als gegevensbron wilt gebruiken.

   Als u meer dan één recordbron gebruikt, moeten de tabellen of query's zijn gekoppeld via velden die ze gemeenschappelijk hebben. Raadpleeg de sectie Zie ook voor meer informatie over het koppelen van tabellen en query's.

3. Sluit het dialoogvenster Tabel weergeven.

4. Klik op het tabblad Ontwerp in de groep Querytype op Kruistabel.

5. Dubbelklik in het queryontwerpvenster op elk veld dat u wilt gebruiken als bron voor de rijkoppen. U kunt maar liefst drie velden voor de rijkoppen selecteren.

6. Selecteer Rijkop voor ieder rijkopveld in de rij Kruistabel in het queryontwerpraster.

   U kunt een expressie invoeren in de rij Criteria om de resultaten voor dat veld te beperken. Ook kunt u de rij Sorteren gebruiken om een sorteervolgorde op te geven voor een veld.

7. Dubbelklik in het queryontwerpvenster op het veld dat u wilt gebruiken als bron voor de kolomkoppen. U kunt slechts één veld selecteren voor de kolomkoppen.

8. Selecteer Kolomkop voor het kolomkopveld in de rij Kruistabel in het queryontwerpraster.

   U kunt een expressie invoeren in de rij Criteria om de resultaten voor de kolomkop te beperken. Wanneer u echter een criteria-expressie gebruikt voor het kolomkopveld, wordt het aantal kolommen niet beperkt dat wordt gevonden door de kruistabelquery. In plaats daarvan wordt het aantal kolommen beperkt dat gegevens bevat. Stel, u hebt een kolomkopveld met drie mogelijke waarden: rood, groen en blauw. Als u het criterium ='blauw' toepast op het kolomkopveld, wordt in de kruistabel nog steeds een kolom weergegeven voor rood en een kolom voor groen, maar bevat alleen de kolom voor blauw bevat gegevens.

   Als u de waarden wilt beperken die worden weergegeven als kolomkoppen, kunt u een lijst opgeven met vaste waarden met behulp van de eigenschap Kolomkoppen van de query. Zie de volgende sectie voor meer informatie.

9. Dubbelklik in het queryontwerpvenster op het veld dat u wilt gebruiken om de samenvattingswaarden te berekenen. U kunt slechts één veld selecteren voor de samenvattingswaarden.

10. Selecteer in het queryontwerpraster, in de rij Totaal voor het samenvattingswaardeveld, een statistische functie voor het berekenen van de waarden.

11. Selecteer in de rij Kruistabel voor het veld met de samenvattingswaarden de optie Waarde.

    U kunt geen criteria opgeven voor een samenvattingswaardeveld of een dergelijk veld sorteren.

12. Ga naar het tabblad Ontwerp en klik in de groep Resultaten op Uitvoeren.

Vaste waarden opgeven voor kolomkoppen

Als u vaste waarden wilt opgeven die moeten worden gebruikt voor de kolomkoppen, kunt u de eigenschap Kolomkoppen van de query instellen.

1. Open de kruistabelquery in de ontwerpweergave.

2. Als het eigenschappenvenster niet zichtbaar is, drukt u op F4 om het te openen.

3. Controleer in het eigenschappenvenster, net boven het tabblad Algemeen, of bij Selectietype de optie Queryeigenschappen is geselecteerd. Is dat niet het geval, klik dan op een lege plek in de ruimte boven het queryontwerpraster.

4. Voer in het eigenschappenvenster op het tabblad Algemeen, in de eigenschap Kolomkoppen, een door komma's gescheiden lijst in met waarden die u wilt gebruiken als kolomkoppen.

   Bepaalde tekens (zoals de meeste leestekens) zijn niet toegestaan in kolomkoppen. Als u deze tekens gebruikt in uw waardenlijst, worden deze elk vervangen door een onderstrepingsteken (_).

SQL-syntaxis voor een kruistabelquery

Een kruistabelquery wordt in SQL uitgedrukt met een TRANSFORM-instructie. De instructie TRANSFORM heeft de volgende syntaxis:

Aggfunction-selectstatement

transformeren Draaitabelveld [IN (waarde1[, waarde2[, ...]])]

De TRANSFORM-instructie bevat de volgende onderdelen:

Onderdeel	Beschrijving
aggfunction	Een statistische SQL-functie die op de geselecteerde gegevens wordt toegepast.
selectstatement	Een SELECT-instructie.
pivotfield	Het veld of expressie dat u wilt gebruiken om de kolomkoppen inde resultatenset van de query te maken.
waarde1, waarde2	Vaste waarden die worden gebruikt om kolomkoppen te maken.

In de SQL-weergave kunt u een onbeperkt aantal tabellen of query's gebruiken als recordbron voor een kruistabelquery. U kunt het ontwerp echter eenvoudig houden door eerst een selectiequery te maken die alle gewenste gegevens ophaalt voor uw kruistabelquery en vervolgens die query te gebruiken als de enige recordbron. Zie de sectie Zie ook voor meer informatie over het maken van een selectiequery.

1. Klik op het tabblad Maken in de groep Overige op Queryontwerp .

2. Sluit het dialoogvenster Tabel weergeven.

3. Ga op het tabblad Start naar de groep Weergaven, klik op Weergave en klik vervolgens op SQL-weergave.

4. Typ of plak de volgende SQL op het tabblad SQL object:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. Op de eerste regel, na TRANSFORM, typt u een expressie voor het berekenen van de totaalwaarden, bijvoorbeeld Sum([Amount]).

   Als u meer dan één tabel of query als gegevensbron gebruikt, voegt u de naam van de tabel of query toe als onderdeel van elke veldnaam, bijvoorbeeld Sum([Uitgaven].[Amount]).

6. Op de tweede regel, na SELECT, typt u een lijst met velden of veldexpressies die u wilt gebruiken als rijkoppen. Gebruik een puntkomma om de items in de lijst van elkaar te scheiden, bijvoorbeeld: [Budget].[Afdelings-id]; [Uitgaven].[Type].

7. Op de derde regel, na FROM, typt u een lijst met de tabellen of query's die u als recordbronnen wilt gebruiken, bijvoorbeeld Budget; Uitgaven.

8. Op de vierde regel, na GROUP BY, typt u dezelfde lijst met velden als in de SELECT-component in stap 6.

9. Op de vijfde regel, na PIVOT, typt u een veldnaam of expressie die u wilt gebruiken voor de kolomkoppen, bijvoorbeeld PIVOT [Budget].[Jar].

Soms kan het handiger zijn om niet elke waarde van een veld of rij te gebruiken voor rij - of kolomkoppen, maar de waarden van een veld te groeperen in bereiken en vervolgens die bereiken te gebruiken voor rij - of kolomkoppen. Stel dat u een veld Leeftijd gebruikt voor de kolomkoppen. U kunt nu voor elke leeftijd een kolom gebruiken, maar wellicht geeft u er de voorkeur aan kolommen te gebruiken die leeftijdsgroepen vertegenwoordigen.

U kunt de functie IIf in een expressie gebruiken om bereiken te maken voor de rij- of kolomkoppen.

1. Open de kruistabelquery in de ontwerpweergave.

2. Klik met de rechtermuisknop in het queryontwerpraster, in de rij Veld, op een lege kolom en klik vervolgens op In- en uitzoomen in het snelmenu.

3. Typ in het vak In- en uitzoomen een veldalias, gevolgd door een dubbele punt (:).

4. Typ IIf().

5. Typ tussen de haakjes na IIf een vergelijkingsexpressie om het eerste veldwaardebereik te definiëren.

   Stel, u maakt bereiken voor het veld Leeftijd en u wilt elk bereik twintig jaar laten beslaan. De vergelijkingsexpressie voor het eerste bereik is [Leeftijd]<21.

6. Typ een komma achter de vergelijkingsexpressie en typ vervolgens een naam voor het bereik tussen aanhalingstekens. De naam die u opgeeft, is de kruistabelkop voor waarden die binnen het bereik vallen.

   Typ bijvoorbeeld achter [Leeftijd]<21 een komma, gevolgd door "0-20 jaar".

7. Typ een komma achter de naam van het bereik (buiten de aanhalingstekens) en ga dan op een van de volgende manieren te werk:

   • Als u een ander bereik wilt maken, typt u IIf() en herhaalt u vervolgens stap 5, 6 en 7.

   • Typ voor het laatste bereik gewoon een bereiknaam.

     Zo kan een volledig geneste IIf -expressie waarmee het veld Leeftijd wordt gescheiden in groepen van twintig jaar, er als volgt uit komen te zien (voor de leesbaarheid zijn regeleinden toegevoegd):

     IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))
     

     Opmerking: Wanneer de expressie in Access wordt geëvalueerd, wordt dit gestaakt zodra een van de IIf -instructies waar blijkt te zijn. U hoeft de laagste waarde voor de bereiken niet op te geven, want elke waarde die onder de laagste waarde van een gegeven bereik valt, is reeds geëvalueerd als waar.

8. Selecteer in het queryontwerpraster Groeperen op in de rij Totaal.

9. Geef in de rij Kruistabel op of u de bereiken wilt gebruiken als rijkoppen of als kolomkoppen. Denk eraan dat u tussen de één en drie rijkoppen en slechts één kolomkop kunt opgeven.

U kunt uw kruistabelquery om gegevens laten vragen wanneer deze wordt uitgevoerd. Stel, u gebruikt verschillende rijkoppen en één met de naam Land/regio. U kunt natuurlijk altijd alle gegevens voor elk land of elke regio weergeven, maar wellicht wilt u liever uw query om een naam laten vragen, waarna de gegevens afhankelijk van de invoer van de gebruiker worden weergegeven.

U kunt aan elk rijkopveld een parametervraag toevoegen.

1. Open de kruistabelquery in de ontwerpweergave.

2. Typ de vraagtekst tussen vierkante haken in de rij Criteria voor het rijkopveld waarvoor u de gebruiker wilt vragen gegevens in te voeren. De vraagtekst wordt als een prompt weergegeven wanneer de query wordt uitgevoerd.

   Als u bijvoorbeeld [Welk land of welke regio?] typt in de rij Criteria, wordt wanneer de query wordt uitgevoerd een dialoogvenster weergegeven dat de vraag 'Welk land of elke regio?', een invoervak en de knop OK bevat.

   Tip: Als u wilt dat uw parameter flexibel is, voegt u de expressie samen met jokertekens met behulp van de operator Like . In plaats van bijvoorbeeld [Welk land of welke regio?] als criterium te gebruiken, kunt u Like [Welk land of welke regio?] gebruiken. &"*" om ervoor te zorgen dat de parameter overeenkomt met een groter invoerbereik. Als u Like gebruikt, wordt het uiterlijk van de parameterprompt niet gewijzigd.

3. Klik op het tabblad Ontwerp in de groep Weergeven/verbergen op Parameters.

4. Voer in het dialoogvenster Queryparameters , in de kolom Parameters , dezelfde parameterprompt in die u hebt gebruikt in de rij Criteria. Neem ook de vierkante haken op maar gebruik geen gecombineerde jokertekens of de operator Like.

5. Selecteer in de kolom Gegevenstype het gegevenstype voor de parameter. Het gegevenstype moet overeenkomen met het gegevenstype van het rijkopveld.

Als het veld dat u gebruikt voor het berekenen van de samenvattingswaarden in uw kruistabelquery null-waarden bevat, worden die waarden genegeerd door de statistische functies die u gebruikt. Voor bepaalde statistische functies kunnen uw resultaten worden beïnvloed. Als u bijvoorbeeld een gemiddelde wilt berekenen, telt u alle waarden op en deelt u de uitkomst door het aantal waarden. Maar als het veld null-waarden bevat, worden deze null-waarden niet meegerekend in het aantal waarden.

In sommige gevallen kunt u er de voorkeur aan geven alle null-waarden te vervangen door nullen zodat de waarden worden meegerekend bij statistische berekeningen. U kunt de functie Nz gebruiken om null-waarden door nullen te vervangen.

Syntaxis van Nz

Nz ( variant [, waardealsnull ] )

De syntaxis van de functie Nz bevat de volgende argumenten:

Argument	Beschrijving
variant	Vereist. Een variabele van het gegevenstype Variant.
waardealsnull	Optioneel (tenzij gebruikt in een query). Een variant waarmee een waarde wordt geleverd die als resultaat moet worden gegevens als het argument variant de waarde Null heeft. Met behulp van dit argument kunt u een andere waarde dan nul of een tekenreeks met lengte nul als resultaat geven. Opmerking: Als u de functie Nz gebruikt in een expressie in een query zonder het argument waardealsnull te gebruiken, resulteert dit in een tekenreeks met lengte nul in de velden die waarden null bevatten.

1. Zorg ervoor dat de query is geopend in de ontwerpweergave en klik met de rechtermuisknop in het queryontwerpraster op het veld Waarde.

2. Klik op In- en uitzoomen in het snelmenu.

3. Zet in het vak In- en uitzoomen de veldnaam of expressie tussen haakjes en typ vervolgens Nz vóór de haakjes.

4. Typ , 0 voor het haakje sluiten.

   Wanneer u Nz met het veld Verloren uren gebruikt om null-waarden om te zetten in nullen, ziet de voltooide expressie er bijvoorbeeld als volgt uit:

Nz([Hours Lost], 0)

• Houd het eenvoudig    Kruistabelquery's kunnen moeilijker leesbaar worden naarmate het aantal rijcombinaties groeit. Gebruik niet meer kolomkoppen dan u nodig hebt.

• Overweeg uw kruistabellen te maken in stappen    Beperk uzelf niet tot alleen tabellen. Vaak kunt u beginnen met het maken van een totalenquery, waarna u die query gebruikt als gegevensbron voor een kruistabelquery.

• Kies uw kolomkopveld zorgvuldig    Kruistabelgegevensbladen zijn vaak beter leesbaar wanneer het aantal kolomkoppen relatief klein blijft. Zodra u hebt bepaald welke velden u als koppen wilt gebruiken, kunt u overwegen het veld met de minste verschillende waarden te gebruiken voor de kolomkoppen. Als u bijvoorbeeld met uw query een waarde berekent op leeftijd en geslacht, kunt u beter geslacht dan leeftijd gebruiken voor de kolomkoppen omdat geslacht normaal gesproken minder mogelijke waarden kent dan leeftijd.

• Gebruik een subquery in een WHERE-component    U kunt een subquery als onderdeel van een WHERE-component gebruiken in een kruistabelquery.

Naar boven