[AccessD] Reports question

Gustav,

Thanks, I transformed it to my table and it worked just fine.


Marcel

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: dinsdag 11 februari 2003 11:50
To: info
Subject: Re: [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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com