Dates

T

Tom Hall

I am trying to query a db containing a long date for the
following information:
1. Weekday name for a particular date
2. The date for the first day of each week beginning on
Sunday

I ma trying to set up a crosstab query where the column
headings are the days of the week and the row headings
are the date of the first day of that week.

Thanks for the help!
 
A

Allen Browne

1. Create a query into your table(s).

2. Change to Crosstab (Crosstab on Query menu).

3. In the Field row, enter:
TheYear: Year([OrderDate])
and make it the Row Heading.

4. Next column in the Field row, enter:
WeekNum: DatePart("ww",[OrderDate])
and make it the Row Heading also.

5. Next column of the Field row:
DOW: Weekday([OrderDate])
and make it the Column Heading.

6. Next column, set up the amount to show,
and set this as the Value.

This example works with the Northwind sample database (ignoring discounts):

TRANSFORM Sum([Quantity]*[UnitPrice]) AS Amount
SELECT Year([OrderDate]) AS TheYear,
DatePart("ww",[OrderDate]) AS WeekNum
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Year([OrderDate]), DatePart("ww",[OrderDate])
ORDER BY Year([OrderDate]), DatePart("ww",[OrderDate])
PIVOT Weekday([OrderDate]) In (1,2,3,4,5,6,7);
 
Top