[AccessD] Grouping Query

Hi Tim

You can get pretty close:

<SQL>

  TRANSFORM
    Sum(CountOfPaid)
  SELECT
    PaymentTerms
  FROM
    tblPayments
  GROUP BY
    PaymentTerms
  PIVOT
    "Paid" & Format(([DatePaid]\7)*7,"00") & "-" & Format((1+([DatePaid]\7))*7-1,"00");

</SQL>

This will return:

PaymentTerms  Paid00-06     Paid07-13     Paid14-20
COD                  16                          16
Net 10-EOM           28            21

If you need all columns, you'll have to create a select query which
returns all fields, then outer join this to the crosstab above.

Off the head I don't know how to turn "Paid00-06" into "Paid01-06".

/gustav


> I have a query that returns the following:

> PaymentTerms            DatePaid        CountOfPaid
> COD                     1                       5
> COD                     2                       3
> COD                     4                       8
> COD                     14                      4
> COD                     16                      12
> Net 10-EOM              1                       3
> Net 10-EOM              3                       10
> Net 10-EOM              5                       15
> Net 10-EOM              10                      21

> DatePaid is the day of the month in which payments are made.  CountOfPaid is
> a count of the number of payments made on particular date.  What I need to
> do is to be able to group this data into the following:

> PaymentTerms            Paid1-6         Paid7-13        Paid14-20       Paid21-27       Paid28-31
> COD                     16              16              0               0               0
> Net10-EOM               28              21              0               0               0

> I know this is a cross tab query but how do I get the grouping in the first
> place?

> Tim Thiessen