crosstab by week

[Kevin Tippett]

I have a table with the following fields: customer number,
customer name, amount billed, amount paid, amount due and
expected due date.

There can be several records for a single customer.

What I need is, in effect, a reverse aging report: row
headings of the customer (no duplicate records), and column
headings of up to ten sequential weeks (expected due
dates), and total amount due for that specific week per
customer. Two other columns will be for total paid prior
to the range of selected weeks, and the total amount paid
for the entire year.

Using a crosstab query I have been able to get a column for
each expected due date. I've also tried using parameters in
the crosstab query (from the query menu 'start day' 'end
day'), but the results are the same as if I had just run a
simple crosstab query (e.g. - columns for each specific
expected due date for the year). Additionally I have also
been able to use a crosstab query to get a column header by
month using the wizard, but not down to a week period.

I am using Access 97.

Any and all help will be greatly appreciated.

 

[John Vinson]

Additionally I have also
been able to use a crosstab query to get a column header by
month using the wizard, but not down to a week period.

Try using a calculated field

WeekDue: DatePart("ww", [Due Date])

This will contain a number 1 to 54, for the week of the year.