1 Reply Latest reply on Jan 21, 2013 9:29 AM by philmodjunk

    CliveB

    CliveB

      Title

      CliveB

      Post

           I have a table containing various dates and a number of different names each of which is manually added. I have to produce a report that indicates the number of times a name appears in any given month in the current year (ie Jan, Feb, Mar etc). I have managed to produce a report that does this, however I can’t seem to get a total for each month.

           I have used COUNT function to get the number of times a name appears but using this overwrites any previous count for different name total fields.

           Is there a simple calculation method that could be incorporated in to a script to over come my problem?

        • 1. Re: CliveB
          philmodjunk

               It's actually a matter of how you design your layout, once you can successfully sort your records to group them by month/year.

               First, you need a field that will have the same value for all records of the same month and year, but which also sorts in correct order by month and year. Here's a calculation field--I'll call it cMonth--you can add to your table that makes that possible:

               YourDateField - Day ( YourDateField ) + 1

               Select Date as your return type. This calculation computes the date of the first day of the month for the month entered into "YourDateField".

               Now to your layout:

               Enter layout mode and use Part Setup... from the Layout menu to add a SubSummary layout part to your layout. Select cMonth as the "when sorted by" field for this layout part. You can put cMonth in this field and use data formatting to show only the month and year to use as a sub header if you want.

               Add another subsummary layout part "when sorted by" your name field and put the counting summary field in this layout part to show the count for one value in your name field for one month.

               Now, if you perform a find for the records you want (say all the records for the year 2012...), you can then sort your records first by cMonth, then by the name field to get a break down of how many times a record with a given value in the name field is present for each month.

               Note that you can delete the body layout part from such a layout if you only want to list the sub totals, and not include a list of the individual records.