This still doesn't help with the grouping that I have to do with the different years. Assume that the table has the following 5 rows:
Row 1: 1, Program Support, $2500, 11/1/1997
Row 2: 1, Program Support, $2500, 11/1/1998
Row 3: 1, Program Support, $2500, 11/1/1999
Row 4: 1, Program Support, $2500, 11/1/2001
Row 5: 1, Program Support, $2500, 11/1/2002
The report needs to show the following info:
$2500 2001 - 2002 Program Support
$2500 1997 - 1999 Program support
The left joins doesn't get me anycloser when I have to find the Mina and max dates for a range of dates. Still looking for help.
----- chris wrote: -----
You need to create a master list from three queries
Query1: all organisations (use group by)
Query2: all years (use group by)
Query3: orgs from query1 and years from query2 without any
joins between the queries. This will give you a master
list of all orgs for all years.
use query3 left joined to your existing summary query as
the basis for your report
-----Original Message-----
I have a client that gives grants on an anual basis.
They report to their board showing the grant history over
time. They group the grant history by Program and dollar
amount of the grant. I need to be able to handle years
when they don't give a grant to this particular
organization or the particular program then re-start
giving the same amount to the same program one to two
years later. They want to see the data come out like
$2500 Program Support - 1998 - 1999 then $2500 Program
Support - 2001 - 2003. No grant was given in 2000. The
grant history table contains the grantee id, program,
amount and date of the grant. One row per grant per
organization. How do I get my reports to show this gap or
a different program during the time.
