Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: Crosstab by Week

    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.

  2. #2
    Join Date
    Oct 2003
    From a book called Access 97 Power Programming by F Scott Baker (

    1. create a crosstab query to handle the detail (use format(theDate,"ww") for the week of hte year 1-54)

    2. Create a totals query to total the values for each week

    3. create a crosstab query from the totals query, giving it the ame layout as the first crosstab. This is to prepare for using the union query, which needs to have the fields be in the same order.

    4. crete a unionquery to combine the two crosstabs, grouping by the Display Order (0 for detail, 1 for totals) field. This makes sure that the total crosstab query values are displayed last

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts