Report on nonconsistent date ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Assume that the table has the following 5 rows: (Grantee ID, Program, Amount, Date)
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

I currently look for the MAX and MIN dates for the matching combination of program and amount. This will not reflect the missing year, in the example. I could also have another amount or program in that missing year. The combination of queries currently looks for the MAX, MIN, and unique Program/Amount combinations. I have a feeling I may have to do this programatically, but if there is any way to get this done via queries, it would be easier.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not really sure what you're trying to achieve.

Perhaps a GROUP BY clause on Program, Amount, GrantDate (don't use
"Date" as a column name - it is also a VBA keyword & can cause
confusion)? Do you want to know the total amount per GranteeID, per
Program over a specified period of time? You'd SUM(Amount) and GROUP
BY Program, Year(GrantDate).

If you want the output to include a list of years per program, in one
line, JET SQL doesn't have that capability (I believe Oracle has it &
calls it the LIST() function). E.g.:

Amount Years Program
- ------ ---------------- ---------------
$2500 1997, 1998, 1999 Program Support


- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBbdW4echKqOuFEgEQLCRgCfSG2YSTjXGWo/Q2mnl4oeUxrWZGIAn3DG
9oYJpPqJPurl59WiO5V88XsF
=vhoA
-----END PGP SIGNATURE-----
 
Back
Top