Do you know how to create summary reports?
You'd need to create two subsummary parts, one for Year_reg and specify Year_reg as the break or "Sorted by" field and another subsummary part for month_reg with month_reg specified as the break field.
Then you'd sort your records by Year_reg first, then by month_reg to group your records first by Year and then by month within the year.
Note: with versions of filemaker older than filemaker 10, the resulting report is only fully visible in preview mode or when you print the report.
Here's a tutorial on summary reports that may help you get this working:
I have created 2 subsummary parts for year_reg & month_reg, I got the form of the report. But, the data display only month JANUARY for all my records in my database, although the particular reg_date is FEBRUARY.
For your info, I use FMP ver 9.
Are all your records in your found set or just January records?
How have you sorted your records?
What "sorted by" field did you specify for the subsummary part where you located this field?
All records in found set are pun under JANUARY. If the starting record is April, all the records in found set
are pun under APRIL.
I sorted the records by year_reg, month_reg and register_date
On subsummary parts, I used field year_reg and month_reg
I have try to set the layout to read data from table student or registration, its still doesn't work.
What's the relationship between registration and student? Is it:
Registration::StudentID = Student::StudentID ?
With that relationship, you should be able to use a layout that refers to registration in Layout Setup... and then be able to put fields such as the student's name in the body part of the layout.
"If the starting record is April, all the records in found set are pun under APRIL."
I can't tell for sure, but that sounds like the layout part where you've put the month name field isn't a subsummary part, but is instead a header or grand summary part. Check to make sure that the field doesn't cross or touch the boundary line between the sub-summary part and any header or grand summary part.
By the way, instead of using a Month name field, you could just put the registration date field on your layout and format it to only show the name of the month. Also, what I've suggested should group your records by month name, but will sort those groups into alphabetical instead of calendar order.
To get them in calendar order, you could define a value list of month names in calendar order and refer to it as custom sort order or you could define this calculation field and sort by it. It takes the registration date and returns the date for the first day of that same month so that all records with dates from the same month have exactly the same month. This is a trick I learned from Comment--another member of this forum:
define cMonth as a calculation that returns date: reg_date - day ( reg_date ) + 1
Sorting your records by cMonth will group your records by month, sorted in Calendar order.
Yup, for relationship, I use studentID and set it as TEXT. If I set that key field as NUMBER, is it will give different result?
I have tried what you have recommend to me, thanks VM, but still give the same result. I have checked the year_reg and month_reg fields, its doesn't cross or touch the boundary line between the sub-summary part.
I have no idea how to resolve this, anyway, I will keep trying to get what I meant.
I have try to create cMonth and set that field as mentioned and sort by cMonth, but not sure what you mean by define a value list of month names in calendar order.
I'm talking about how to sort your record groups correctly.
I'm talking about the difference between sorting by MonthName as apposed to a date field such as cMonth.
If you sort by MonthName, your records will be sorted like this:
In alphabetical order.
If you sort by cMonth, you'll get:
In Calendar order--which is what I belive you'd want in this type of report.
The third option is to define a value list of month names--which you may already have--listed in the same order as they are listed in a calendar. When you sort your records, you can use the "custom order based on value list" option with this value list to sort your records by Month Name, but in calendar order instead of alphabetical.
All of this would be unnecessary if one simply creates a calculation cMonthDate of:
Date - Day ( Date ) + 1
... which turns all dates into generic first day of month (or better phrased, turns all dates into their month).
Then sorting will always work properly, as will sub-summaries based upon month, as will display of month/year for reports (handle it from date format layout level).
It also is used for portal filtering by month/year and relationships. Having the year separate from the month will never provide the full range of functionality that using a TRUE date will provide.