[AccessD] A2K:CrossTab Question

Hi Darren

> 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
>
> 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)

Stuart's WeekdayName() is a newer function from A2000 or A2002.

So, for you with A97, that would be:

  Select
    Format(OffenceDate, "dddd", 2) As DayOfWeek,
    Weekday(OffenceDate, 2) As DayNumber,
    Count(OffenceDate) As OffenceCount
  From
    tblOffences
  Where
    Year(OffenceDate)=2004
  Group By
    Format(OffenceDate, "dddd", 2),
    WeekDay(OffenceDate, 2)
  Order By
    WeekDay(OffenceDate, 2);

/gustav