kruistabelquery?

[maurann]

Beste leden,
Ik ben op deze site reeds veel geholpen, waarvoor mijn dank.
Nu heb ik 't volgend probleem en waag ik 't er opnieuw op:
wil van een query een kruistabelquery maken/
In feite wil ik voor elk jaar de som laten berekenen per maand.
Nu heeft die query volgende resultaten:
Datum Som
31/10/2009 122,45
30/10/2009 69,8
28/09/2009 60
27/09/2009 63,8
14/09/2009 109,95

Maar dacht aan iets met een IIF statement
in de vorm van:
SELECT Query14.Datum, Sum(IIf([Datum]="2009",[som],0)) AS 2009
FROM Query14
GROUP BY Query14.Datum
ORDER BY Query14.Datum DESC;
Dit heeft natuurlijk geen resultaat.
't resultaat dat ik wil is:
Datum 2009
oct 192.25
sept 233.75
En dit ook voor de andere jaren...
Is dit mogelijk?


M.

 

[OctaFish]

Om een kruistabel te maken, verander je de query van Selectiequery naar Kruistabelquery via het menu <Query>.
Bij een kruistabel heb je drie elementen nodig: minstens één veld als Rijkop, één veld als Kolomkop en én veld als Waarde'. Als ik jou wens zo bekijk, dan is het veld Jaartal de rijkop, het veld Maand de kolomkop, en gebruik je het veld Som als waardeveld. Bij de laatste gebruik je dan de functie Som om de waarden op te tellen voor een totaalwaarde.
Het Jaartal kun je, als je geen apart getalveld hebt voor jaartallen, halen uit een datumveld met de functie Year([Datumveld]). De maanden maak je op dezelfde manier, maar dan met Format: Format([Datumveld];"mmm") bijvoorbeeld als je de maanden afgekort wilt. Voor het waardeveld dus het veld Som, met de functie Som.
Deze constructie zou al je resultaat moeten laten zien. De bovenstaande layout heeft één nadeel: (eigenlijk twee, maar de tweede laat ik even zitten). De maanden (kolomkoppen dus) worden alfabetisch gesorteerd, niet op maandvolgorde. Om dat op te lossen zet je het veld Month([Datumveld]) erbij, dat je geen kruistabeloptie geeft. Op dit veld zet je wel de sorteervolgorde Oplopend. Nu kloppen je maanden met de werkelijkheid!

 

[maurann]

Bedankt voor je reactie en antwoord,

probeerde het uit en 't werkt.
Echter voor de rijkop (het jaartal) geeft hij
't volgende:
Jaar
1/07/1905
30/06/1905
29/06/1905
28/06/1905
en, inderdaad, de maanden worden niet gesorteerd volgens de kalender, maar alfabetisch.
Indien ik uw voorschriften volg geraak ik er ook niet ,
hij sorteert nog steeds volgens alfabet en geeft als rijkop de jaartallen (1/7/1905 30/06/1905 29/06/1905 en 28/06/1905, maar telkens zoveel als hij maanden vindt.
Indien ik de query aanpas en als kolomkop Maand: Month([Datum]) gebruik dan sorteert hij de maanden maar geeft getallen weer

Jaar 1 2 3 4 5 6 7 8 9 10 11 12
1/07/1905
30/06/1905
29/06/1905
28/06/1905

Kan dit beter?

Txs,

M.

 

[OctaFish]

Ik denk wel dat ik je zo verder kan helpen.
eerst maar eens het probleem van de jaartallen: vermoedelijk is dat een kwestie van opmaak van het veld Jaar. De datum 1-7-1095 is namelijk, als je de opmaak zou veranderen naar Getal de waarde: 2009. Ziet er uit als een recent jaartal...
Het veld met de Rijkop selecteren (daar staat dus iets als: Year([Datum]), de knop <Eigenschappen> aanklikken, en bij <Notatie> kies je dan <Getal>.
Wat betreft de sortering van de maanden: Je hebt twee kolommen nodig om de maanden goed te sorteren. De eerste kolom is degene met de formule Format([Datumveld];"mmm") . Deze stel je in als Kolomkop voor de kruistabel.
Op dit veld moet je dus NIET sorteren!
De tweede kolom bevat de formule Month([Datum]). Deze kolom gebruik je WEL om te sorteren. Dit veld geef je geen Kruistabel instelling, want je wilt dit veld niet weergeven. Voor de kolomkoppen heb je immers al een ander veld. Dit is puur een sorteerveld.

 

[maurann]

Bedankt,

het jaartal wordt nu duidelijk.

De maanden echter, daar luk ik niet in,
maar ben al hele stap vooruit.
indien ik 't volgende uitprobeer
Jaar: Year([Datum]) Maand: Month([Datum]) Som
Query14
Group by Group by
Rijkop Kolomkop Som
Oplopend Oplopend Waarde

bekom ik reeds een mooi resultaat.
Mooier was 't natuurlijk de maanden in de kolomkop te krijgen in plaats van getallen 1-12
Indien ik uw voorstel volg krijg ik de maanden, alfabetische volgorde met veel lege velden en de jaartallen weergegeven zoveel maal als het aantal maanden die in 't respectievelijke jaar gevonden worden?.
Kan ik trouwens bekomen dat de som wordt afgerond tot op 2 getallen na de komma?
M.

 

[OctaFish]

Ik vrees dat ik je een beetje het bos in heb gejaagd....
In een eerder berichtje had ik het over twee nadelen. De oplossing voor beide problemen is eigenlijk dezelfde. Ik had de Month optie niet hoeven te bieden, want die werkt hier inderdaad niet. Ik ga de rest van de avond in een hoek staan met een spaans rietje...
Als je naar de afbeelding kijkt, zie je de oplossing.
Die is niet heel erg logisch, maar wel verklaarbaar.
Wat is er aan de hand?
Als je een kruistabel maakt, krijg je net zoveel kolommen als je gegevens hebt. Als je data hebt voor Mei, Juni, Juli en Augustus, zijn dat de maanden in je kruistabel. En ook nog eens verkeerd gesorteerd!
Om dat op te lossen, voeg je Kolomkoppen toe aan de eigenschappen van het specifieke kolomveld. Als je alle maanden van het jaar wilt zien, dan zul je alle maanden apart moeten benoemen. Vandaar dat je in de afbeelding de omschrijving ziet voor Jan-Dec.
En hiermee zijn twee problemen opgelost: ten eerste zie je altijd alle maanden van het jaar, en ten tweede ook nog eens in de goede volgorde!

TRANSFORM Count(tblMailing.Maildatum) AS AantalVanMaildatum
SELECT Year([Maildatum]) AS Jaar
FROM tblMailing
GROUP BY Year([Maildatum])
PIVOT Format([Maildatum],"mmm") In ("Jan","Feb","Mar","Apr","Mei","Jun","Jul","Aug","Sep","Okt","Nov","Dec");

 

[maurann]

Bedankt,

Ik expirimenteer ermee
en vind dat de jaartallen best in de kolomkoppen staan, de maanden in de rijkoppen.
Dit omdat het dan overzichtelijker lijkt.
1ste vraag:
Kan ik onderaan bekomen dat ik de totalen te zien krijg van de 12 maanden? En hoe doe ik dit (veronderstel met een unionquery)
2 de vraag: ik veronderstel dat ik voor de benaming van de rijkoppen tevreden moet zijn met de getallen of kan ik die ook benoemen volgens de maanden?

Alvast bedankt,

leerde veel bij:thumb:

M.

 

[OctaFish]

Nu wordt het erg lastig...
Even voor de duidelijkheid: je zegt dat je de maanden als rijkoppen wilt, en de jaren als kolomkoppen. En nu wil je ook voor de lege maanden een rij met gegevens? Of gaat het alleen om de weergave van de maanden?
Volgens mij kun je gewoon de maanden als rijkop gebruiken, en de jaren als kolomkop. Heel simpel ook: je hoeft alleen maar de functie Rijkop te veranderen in Kolomkop, en Kolomkop in Rijkop. Dan zie je al gelijk wat er gebeurt!
Ik vermoed overigens, dat ze dan weer gesorteerd worden op alfabet (de maanden uiteraard), in welk geval de truc met Month([Datum]) weer zou kunnen gaan werken! Die definieer je dan als Rijkop (dat mag wel, je mag een hoop rijkoppen definieren), en in de gegevensweergave verberg je vervolgens de kolom met maandnummers.

Vraag 2: de totalen? Kun je in Access 2003 en ouder vergeten; het schijnt dat 2007 wel een rij met totalen kan toevoegen, maar dat heb ik nog niet uitgeprobeerd. Ik heb het eigenlijk nooit gemist, want totalen bereken ik wel op rapport of formulier, dus dat hoeft niet zo nodig in een query.
Je kunt wel totalen definieren als Rijkop, maar dan moet je de maanden dus als kolomkop gebruiken. Je gebruikt voor Totalen op rijniveau dezelfde functie als voor de optie Waarden, alleen zet je de functie dan op Rijkop.

 

[maurann]

Allright!:thumb:
't is gelukt

Ik expirimenteer verder en maak er een mooi rapportje van.

Bedankt hé.

Maurann