Custom Grouping

  • Thread starter Thread starter kc
  • Start date Start date
K

kc

I need to group a report by dates that are within 45 days of the end of the
month, such as:
For the month of: Earliest Date Latest Date
January 12/17/2009 1/31/2009
February 1/14/2010 2/28/2010
Etc.

I also need the first and last month to have custom dates because it is for
a report that goes by the Federal Fiscal Year of Oct-Sept, so:
For the month of: Earliest Date Latest Date
October 10/1/2009 10/31/2009
November 10/16/2009 11/30/2009
…
September 8/16/2010 9/30/2010
October 9/16/2010 9/30/2010

The report is going to list clients to contact during the month and within
45 days of their birthday.
Because of the overlap, some clients will be in two groups, so personnel
will know to start trying to contact them as soon as possible, but they will
also be on the list for the next month (unless of course, contact has already
been made, in which case they will drop off the query) to ensure follow up if
not contacted.

Any ideas? Or is this possible?

Thanks for any help!
kc
 
THE FOLLOWING IS SPECULATION ON MY PART. I HAVE NOT TESTED the proposed
solution and cannot say that it will work without modification.

The best way I can see to handle this is to set up a table with records such
as the following to describe each period.

EarliestDate LatestDate GroupName
12/17/2009 1/31/2009 Dec 2009 (or whatever name you want to give
1/14/2010 2/28/2010 Jan 2010 the range 2009-1, 2009-2 etc.)

Now you can use a join in a query to get records and have them fall into
multiple ranges

SELECT C.*, G.GroupName
FROM ClientsTable As C INNER JOIN CalendarGroups as G
ON DateSerial(Year(Date()),Month(C.DOB),Day(C.DOB))>=G.EarliestDate
AND DateSerial(Year(Date()),Month(C.DOB),Day(C.DOB))<=G.LatestDate
WHERE G.GroupName In ("Dec 2009","Jan 2010")

That is likely to have problems in December and January. I will ponder what
else can be done to take care of the problem.

You may be able to use

ON (DateSerial(Year(Date()),Month(C.DOB),Day(C.DOB))>=G.EarliestDate
AND DateSerial(Year(Date()),Month(C.DOB),Day(C.DOB))<=G.LatestDate)
OR
(DateSerial(Year(Date())-1,Month(C.DOB),Day(C.DOB))>=G.EarliestDate
AND DateSerial(Year(Date())-1,Month(C.DOB),Day(C.DOB))<=G.LatestDate)

If your date ranges are consistent from year to year (including leap years)
then there may be a simpler way to solve this.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Using a table named CountNumber with field CountNUM containg numbers from 0
(zero) throurh 12 it will produce the results below --
SELECT
Format(DateAdd("m",[CountNUM],DateSerial(Year(Date()),Month(Date())-4,0)),"mmmm")
AS [Month],
DateAdd("m",[CountNUM],DateSerial(Year(Date()),Month(Date())-4,0))-45 AS
Earliest_Date,
DateAdd("m",[CountNUM],DateSerial(Year(Date()),Month(Date())-4,0)) AS End_Date
FROM CountNumber
WHERE
(((DateAdd("m",[CountNUM],DateSerial(Year(Date()),Month(Date())-4,0)))<DateSerial(Year(Date()),11,0)) AND ((Month(DateAdd("m",[CountNUM],Date())))<=12));


Month Earliest_Date End_Date
October 9/16/2009 10/31/2009
November 10/16/2009 11/30/2009
December 11/16/2009 12/31/2009
January 12/17/2009 1/31/2010
February 1/14/2010 2/28/2010
March 2/14/2010 3/31/2010
April 3/16/2010 4/30/2010
May 4/16/2010 5/31/2010
June 5/16/2010 6/30/2010
July 6/16/2010 7/31/2010
August 7/17/2010 8/31/2010
September 8/16/2010 9/30/2010
 
I am doing something similar (see my post that Duane Hookum responded to on
9-Mar entitled "Custom Grouping"). I decided to add a field which has the
name Month_Number. When I enter data into my form, I enter the appropriate
month based on the way the billing cycle falls. I then grouped the records
by these month numbers. It requires an extra step when inputting data and
the user must know which month they are in, but it's not too difficult.

*Glen*
 
Back
Top