[AccessD] A2K:CrossTab Question

Hi Stuart
Many thanks for the reply - times zones and all :-))
It is failing on the WeekDayName portion of the SQL.
Is that a function you have that I don't?

I managed to get the following working - promising, but not there yet

SELECT DISTINCT Weekday(OffenceDate) AS Expr1
FROM tblOffences Group By  Weekday(OffenceDate)
Order By Weekday(OffenceDate)

Many many thanks again

Darren


----- Original Message ----- 
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: "Access Developers discussion and problemsolving" <accessd at databaseadvisors.com>
Sent: Tuesday, August 03, 2004 3:38 PM
Subject: Re: [AccessD] A2K:CrossTab Question


> On 3 Aug 2004 at 11:34, Darren DICK wrote:
>
> > Hello all
> > Time for charts <sigh>
> > My Q has 2 parts
> >
> > I have a table that records the dates of occurences - so far so good. The table name is tblOffences, the Field is called
OffenceDate
> > (very original - I know:-)) Date /Time data format Date Format = dd/mm/yyyy
> > PART1
> > What I need is for the crosstab that is going to display my chart to break down all the
> > occurence dates into 'day' groups. IE show all the offences throughout the year that fall on a monday into the monday group,
tuesday
> > occurences into the tuesday group etc.
> > I want that bit to be my row heading. The column heading is to be Periods 1 through to 6.This
> > is also captured with the Offence date.
> > EG Each record captures Offence date - 1/1/2004, Period1
> >
> > PART2
> > Then I need a sum of 'em. EG over the whole year 100 occurences on Mondays, 200 on Tuesdays
> > 120 on Wednesdays etc.
> >
> > Of course I have dates that span the whole year, I just want to know what the syntax or procedure
> > is for putting a years worth of data into 1 of 7 groups (ie each dayo the week)
> >
>
> Select Distinct(Weekdayname(Weekday(OffenceDate)), count(OffenceID)
> from tblOffences
> Group By  Weekdayname(Weekday(OffenceDate))
> Order By Weekday(OffenceDate)
>
>
>
> -- 
> Lexacorp Ltd
> http://www.lexacorp.com.pg
> Information Technology Consultancy, Software Development,System Support.
>
>
>
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com