Hope someone can help.

Table structure: Name : tbl_Runs

Fields are:

Run_ID
Run_Date
Run_Distance (km)
Run_Duration (secs)

I want to create/design a report whereby columns are the days of the
week and the rows are the week numbers of the year.

Within each cell I want to merely print "Run_Distance".

I'm going around in circles with this one so any advice would be
appreciated.

Steven Taylor
Melbourne, Australia.

Create a query into this table.

Change it to a Crosstab query (Crosstab on Query menu.)
Access adds Total and Crosstab rows to the design grid.

In a fresh column in the Field row of the grid, enter:
TheYear: Year([RunDate])
In the Crosstab row under this field, choose Row Heading.

In the next column, Field row, enter:
TheWeek: DatePart([RunDate]), "ww")
Again set the Crosstab to Row Heading.

In the Field row, enter:
DOW: Weekday([RunDate])
In the Crosstab row under this field, choose Column Heading.

Drag the Run_Distance into the grid.
In the Total row, choose Sum.
In the Crosstab row, choose Value.

The query will generate:
- a column for the year;
- a column for the week of the year;
- a column for each day of the week, containing the sum of the distance.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Taylor" <newslist (AT) superjacent (DOT) net> wrote

Thanks Allen, it works a treat.

Steve.

Allen Browne wrote: