 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / February 2006Grouping in Access Report
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? > 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 [quoted text clipped - 8 lines] > 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...  SignatureTim http://www.ucs.mun.ca/~tmarshal/ ^o< /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me 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") > Hey Tim, what does the "d" do in your code? > [quoted text clipped - 9 lines] > 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 SignatureTim http://www.ucs.mun.ca/~tmarshal/ ^o< /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me Worked like a charm. Thanks Tim! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.