[AccessD] Reports question

Hi Marcel

> I am strugling with the following. I need a report which shows the following

> Sales until this month  Sales this month  Total Sales
> Supplier
>     Customer 1    1000               100         1100

> I have tried to do this with
>   1.. cross-tab query: this does not work because I do not know how many
> months there are.
>   2.. two reports in one: If a customer misses in a certain month the rows
> are not equal anymore
> Neither option works very well. I am sure there is a simple solution for
> this.

You could try something like this:

<SQL>

SELECT
  CustomerID,
  Sum([Sales])
    AS SalesTotal,
  Sum([Sales] * Abs(DateDiff("d",[SalesDate],Date()) < Day(Date())))
    AS SalesThisMonth,
  [SalesTotal]-[SalesThisMonth]
    AS SalesUntilThisMonth
FROM
  tblCustomerSales
GROUP BY
  CustomerID;

</SQL>

/gustav