If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grouping report by calendar quarter
Several weeks ago, one of your experts (Steve Schapel) showed me how to
determine the quarter in which an employee becomes eligible for a particular benefit. The formula Steve showed me was: DateSerial(Year([HireDate])+1-(Month([HireDate])9),(DatePart("q",[HireDate])*3+1) MOD 12,1) It worked perfectly. (Thanks, Steve!) I set up a report to show only those employees who are becoming eligible in the next upcoming quarter by entering the next quarter's start date as a criterion in the query. Now, we need to construct a report showing ALL employees, grouped by the quarter in which they become eligible. I imagine I would need to set up the Sorting and Grouping to group by quarter, but I can't figure out what field to use as the basis of the group. Using the eligibility date doesn't give me the proper grouping...I need something like 1st Quarter of 2000, 2nd Quarter of 2000, etc. Can anyone give me a hand? Let me know if this isn't clear. Thanks in advance! |
#2
|
|||
|
|||
Wouldn't you just group on your formula...
DateSerial(Year([HireDate])+1-(Month([HireDate])9),(DatePart("q",[HireDate] )*3+1) MOD 12,1) Then in the group header, you could use if staments to say something like... If the quarter is "1" then print "1st Quarter of " & the year. "Susan" wrote in message ... Several weeks ago, one of your experts (Steve Schapel) showed me how to determine the quarter in which an employee becomes eligible for a particular benefit. The formula Steve showed me was: DateSerial(Year([HireDate])+1-(Month([HireDate])9),(DatePart("q",[HireDate] )*3+1) MOD 12,1) It worked perfectly. (Thanks, Steve!) I set up a report to show only those employees who are becoming eligible in the next upcoming quarter by entering the next quarter's start date as a criterion in the query. Now, we need to construct a report showing ALL employees, grouped by the quarter in which they become eligible. I imagine I would need to set up the Sorting and Grouping to group by quarter, but I can't figure out what field to use as the basis of the group. Using the eligibility date doesn't give me the proper grouping...I need something like 1st Quarter of 2000, 2nd Quarter of 2000, etc. Can anyone give me a hand? Let me know if this isn't clear. Thanks in advance! |
#3
|
|||
|
|||
Thanks, Rick! I'll give it a try!
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Report Sorting and Grouping | bdehning | General Discussion | 0 | August 12th, 2004 04:55 AM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |
Calendar Style Report | Sue M | Setting Up & Running Reports | 1 | May 19th, 2004 06:32 PM |
Calendar Object | Steve | Setting Up & Running Reports | 1 | May 18th, 2004 04:44 PM |