Count date records grouped anually formula

[Schnieds]

Hey,

I have a few reports that I need to write that need to show the growth statistics for people joining given groups over time. I have the following tables (I simplified the columns to the relavent ones for the sake of this formula):

Groups table
-GroupID
-GroupName

MemberGroups table
-MemberID
-GroupID
-DateAdded

I need a formula that will calculate the total number of memberships per year, including previous years, for each group.

For example, if the following records existed:

Groups Table
- 1, "Employees"

GroupMembers Table
- 1, 1, "1/12/04"
- 2, 1, "3/14/04"
- 3, 1. "1/13/05"

I need a formula that would produce 2 memberships total in the "Employees" group for 2004 and 3 memberships total in the "Employees" group for 2005.

I have been racking my brain, but can't figure this out. Can anyone help me out?

Thanks!!!

 

[dgillz]

Can you not simply insert another group, by year, within employee?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[lbass]

Insert a running total in the group footer for the year group. You should have a group on Group ID, and a second group on year. For the running total, select Member ID, distinctcount, evaluate for each record, reset on change of group (Group ID). In the above example, if you had a group on year, the running total in the group footer for year, would show 2 for 2004, and 3 for 2005.

-LB