Grouping in Access Report

[ian_gendreau]

I am writing a database for sales forecasting. All of my entries are
in a table, each entry has a Projecting Booking Date. I need to write
a report with 4 groupings:

0-30 Days Out
31-60 Days Out
61-90 Days Out
Over 90 Days Out

How do I set this up in my report? Do I need to write sub-reports for
each grouping and then combine them all on one report, or is there an
easier way to create these groups on a report?

[Tim Marshall]

ia**********@hermanmiller.com wrote:

I am writing a database for sales forecasting. All of my entries are
in a table, each entry has a Projecting Booking Date. I need to write
a report with 4 groupings:

0-30 Days Out
31-60 Days Out
61-90 Days Out
Over 90 Days Out

How do I set this up in my report? Do I need to write sub-reports for
each grouping and then combine them all on one report, or is there an
easier way to create these groups on a report?

Ian I do something quite similar in reports which show work backlogs
and, in fact, use the same groupings (plus another one for more than 365
days).

Presumeably, you are getting this information from a table which has a
booking date, which, for the example below, I'll refer to as
WO_REQUEST_DATE (since that allows me to paste soemthing out of my own
work!). In your report recordsource or the query that is the source for
your report, create a calculted expression similar to the one below
(substitute your field name for WO_REQUEST_DATE, of course!):

Projected_Date: Switch(DateDiff("d",[WO_REQUEST_DATE],Date())<=30,"0-30
Days Out",DateDiff("d",[WO_REQUEST_DATE],Date())<=60,"31-60 Days
Out",DateDiff("d",[WO_REQUEST_DATE],Date())<=90,"61 to 90
Days",DateDiff("d",[WO_REQUEST_DATE],Date())>90,"Over 90 Days Out")

Remember that the switch function evaluates in order as you write the
conditions, so it's not necessary to use "and" or between in the
conditions for 31-60 Days Out and 61-90 Days Out.

In your report, use groupings and headers (which I'm assuming you're
familiar with) and set Projected_Date as the first Field/Expression.

There ya go...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[igendreau]

Hey Tim, what does the "d" do in your code?

I copied and pasted your string exactly, then substituded
[WO_REQUEST_DATE] for [BookingDate] which is my date field. I created
a new query based on my table, and posted this to create a new
calculated field. It immediately gives me an error saying I have
invalid syntax, and when I hit "Okay" it highlights the "d". Here's my
code:

Projected_Date: Switch(DateDiff("d",[BookingDate],Date())<=30,"0-30
Days Out",DateDiff("d",[BookingDate],Date())<=60,"31-60 Days
Out",DateDiff("d",[BookingDate],Date())<=90,"61 to 90
Days",DateDiff("d",[BookingDate],Date())>90,"Over 90 Days Out")

[Tim Marshall]

igendreau wrote:

Hey Tim, what does the "d" do in your code?

I copied and pasted your string exactly, then substituded
[WO_REQUEST_DATE] for [BookingDate] which is my date field. I created
a new query based on my table, and posted this to create a new
calculated field. It immediately gives me an error saying I have
invalid syntax, and when I hit "Okay" it highlights the "d". Here's my
code:

Projected_Date: Switch(DateDiff("d",[BookingDate],Date())<=30,"0-30
Days Out",DateDiff("d",[BookingDate],Date())<=60,"31-60 Days
Out",DateDiff("d",[BookingDate],Date())<=90,"61 to 90
Days",DateDiff("d",[BookingDate],Date())>90,"Over 90 Days Out")

The "d" is the argument for the datediff function (not sure what version
of Access you're using but you can look this up in help). You say you
pasted it exactly, so, hopefully you haven't removed any qutation marks
that are in the switch expression.

Another thing to be aware of is to make sure any line breaks from
pasting the code are removed. I just copy and pasted from my news
reader your reply message and my first results only gave me "0-30"
instead of "0-30 Days Out". I got rid of the line breaks and everything
went tickity boo! 8)

An easy way of doing this is pasting the code in Word and removing line
breaks and then copying and pasting into the query design grid.

The other thing to be certain of - is BookingDate a date field? Stupid
question and I'm not trying to insult your intelligence, but it's easy
to overlook the easy things sometimes... 8)

Anther thing - did you paste this into the SQL view or the query design
grid? The expression is designed to be pasted into the design grid. If
you want to put it directly into SQL, then you'd place the following
after a comma in the select clause:

Switch(DateDiff("d",[BookingDate],Date())<=30,"0-30 Days
Out",DateDiff("d",[BookingDate],Date())<=60,"31-60 Days
Out",DateDiff("d",[BookingDate],Date())<=90,"61 to 90
Days",DateDiff("d",[BookingDate],Date())>90,"Over 90 Days Out") as
Projected_Date

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[igendreau]

Worked like a charm. Thanks Tim!