Date Group and Totals

[Guest]

My database in Access tracks investments. In addition to other fields, I have
the following:

Maturity Date [MatureDate]
Amount Invested [AmtInv]


I would like to create a report that groups and totals the Amounts Invested,
using the current date Now() as reference, by:

30 days or less to maturity
31-60 days to maturity
61 – 90 days to maturity
91 – 120 days to maturity
121 Days or More

TIA
Janna

 

[Al Campagna]

Janna,
Better to use Months than 30, 60 ,90 days etc...
Using the query design grid, create a calculated column...
Within30 : If MatureDate Between DateAdd("m", -1, Date()) And Date(), AmtInv, 0)
and another column...
Within30_60 : If MatureDate Between DateAdd("m", -2, Date()) And DateAdd("m", -1, Date(),
AmtInv, 0)
etc... for all your ranges.....up to...
After120 : If MatureDate <= DateAdd("m", -6, Date()), AmtInv, 0)

Use a Select query to report on the details if desired.
TotInv 30 30/60....etc..... Over6Mo Total
IBM 10,000 1,250 2,400....etc.........4,300 10,000
XYZ 12,000 500 800....etc........ 6,500 12,000

Or Totals Query for Sums only
22,000 1,750 3,200....etc.........10.800 22,000