[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.