[AccessD] pivot query

Hello Gustav,

Sum(Abs([datum]   did the trick.
Thanks again for your help.

Pedro


----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Thursday, January 01, 2009 11:27 PM
Subject: Re: [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
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>