[AccessD] pivot query

Hi Pedro

Oh, I see, then it could be like this (air code again):

TRANSFORM Count(qryPerioden.AX) AS CountOfAX
SELECT qryPerioden.Soort_nr, qryPerioden.Kerkdorp, Count(qryPerioden.AX) AS [Total Of AX],
   Sum(Abs([datum]<#1994/01/01#)) As 1980_1993,
   Sum(Abs([datum]>=#1994/01/01#)) As 1994_2008
FROM qryPerioden
WHERE [datum] Between #1980/01/01#  And  #2008/12/31#
GROUP BY qryPerioden.Soort_nr, qryPerioden.Kerkdorp
PIVOT Format([datum],"yyyy");

/gustav


>>> pedro at plex.nl 01-01-2009 23:06 >>>
Hello Gustav,

this doesn't work for me.
I then get the extra field "Period" where it gives Oud or Nieuw (nice that 
you know the dutch words) as values.

I need two extra fields  "1980-1993" and "1994-2008" with the total count of 
the values of "AX" for each periodes.

Thanks

Pedro



----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Thursday, January 01, 2009 10:45 PM
Subject: Re: [AccessD] pivot query


> Hi Pedro
>
> How about this (air code):
>
> TRANSFORM Count(qryPerioden.AX) AS CountOfAX
> SELECT qryPerioden.Soort_nr, qryPerioden.Kerkdorp, Count(qryPerioden.AX) 
> AS [Total Of AX],
>   IIf([datum]<#01-01-1994#, "Oud", "Nieuw") As Period
> FROM qryPerioden
> WHERE [datum] Between #1980/01/01#  And  #2008/12/31#
> GROUP BY qryPerioden.Soort_nr, qryPerioden.Kerkdorp, 
> IIf([datum]<#01-01-1994#, "Oud", "Nieuw")
> PIVOT Format([datum],"yyyy");
>
> /gustav
>
>>>> pedro at plex.nl 01-01-2009 22:15 >>>
> Hello Group,
>
> first all the best for 2009.
>
> I have a pivot query.
>
> TRANSFORM Count(qryPerioden.AX) AS CountOfAX
> SELECT qryPerioden.Soort_nr, qryPerioden.Kerkdorp, Count(qryPerioden.AX) 
> AS [Total Of AX]
> FROM qryPerioden
> GROUP BY qryPerioden.Soort_nr, qryPerioden.Kerkdorp
> PIVOT Format([datum],"yyyy");
>
>
> I want in the same query a statement, so that i get also the count of two 
> periods;
> Between #01-01-1980#  and #31-12-1993#              and           Between 
> #01-01-1994#  and #31-12-2008#
>
> I can't have tried, but i can't get it done.
>
> Who can help me?
>
> Thanks
>
> Pedro