How To Create This Report

[panache]

Sorry if this is the wrong location for this post. Feel free to move it.

I've been asked to create a report that will track students' height and weight over time. I have a 'students' database that holds the height and weight information. I could easily create a list report that just lists the students' names plus their height and weight, but I've been asked to have the report display historical data on this. Specifically month by month. Attached is a quick sketch of an idea of the layout of the report.

At first I thought I'd need a wt/ht db where one month was one record, but that seems wrong. Any ideas? Thanks in advance.

[Himitsu]

are the students and their info located in the same table (file)? or is each month, each student gets a new record with that months weight and height in it? So there is a relationship to that file by the student_ID or name? Are you using FMP6 or 7? this matters and if you are using 6, then you should fork out the dough and get seven before you start. Because some day, you will need it, and then you will be stuck with two files, one for the students and one for the weight/height records. In 7, all you need to do is add a new table for the weight/height records, then in that table, create kind of the same fields you have for the students, name, ID, ect.. plus your new fields, weight, height, date made/updated, ect... through then on the student table(file) put a portal using the relationship you made using student_ID. But on the relatioship properties, click on the boxes that will allow them to create new records in the weight/height table(file). that way, from the student table(file) you can add new records each month without having to change different layouts. It sounds like a lot, but if you tinker with it, and let us know step by step, we can walk you through it.

[Ender]

Hi panache,

Using regular fields (or repeating fields) in Student to hold each month's height and weight may be the easiest to get the output in the format you showed.

If, however, you also need to do other things with this data (collect more than one year's data, graph it, or compare it to the average height/weight for a student of that age,) then I would recommend putting each month's height and weight data in individual records in a related file. It will be a little trickier to show 12 months of related records in the format you indicated, but it will be easier to do those other things with the data. Data entry can be done using portals to the related file.

Although FM7 has many great features, this project can certainly be accomplished with FM6. Converting to FM7 later will be no more difficult because of this additional module.

[Brian C]

If I understand the scenerio correctly, you can handle this one of 2 ways:

1) Have 2 tables, 1=Student, 2=Statistics.

The Students File could link to the records in Statistics via a relationship using the ID Number.

Use a portal in the student file to view a history of each student as you flip thru them.

Use a relationship in the Statistics file using the ID to bring across the student name and relevant student information. Reports would be run in Statistics.

Adding a new student in this fashion is simple if you use a the Serial Number option for Auto Enter on the ID field.

Adding new records for the Statistics can be done either thru the portal to the statistics file (make sure you allow creation of related records in the relationship defination) or by scripts if you want to make it a little cleaner on data entry to keep any mistakes from being made.

2) You could do this completely in one table but it tends to get more complex for managing the data. Make sure you have an ID for each student so you have something to link with.

New record creation scripts would need to determine if a student already exists to insure the student gets assigned the same ID.

Use a self relationship where you link the file to itself using the ID to match to itself.

You can then use a portal using this relationship to view a summary of all data for a student while you are viewing and of the records for that student. Make sure the fields you place in the portal also use the relationship. (this is a common mistake many people make)

In either of the 2 cases:

Create a report layout containing the fields you want displayed.

Use summary fields in the Layout Parts: Sub-summary parts and Trailing Grand Summary.

Use the sort criteria of (Name, ID).

For the subsummary select the sort by ID. This will allow you to display a subtotal for each student with the use of the summary fields.

Using the same summary fields in a Trailing Grand Summary to give you Totals of everything if that is needed.

[Himitsu]

That sounds like it. If you just follow the add layout and use the step by step wizard it will hit it pretty close, you will just have to move somethings around.

[Ender]

I don't believe a sub-summary report would work well for seeing the data in the format panache indicated, however it would be simpler to create and may suit panache's needs. The nice thing about a sub-summary report, is it would show if a student had more than one screening in a month. Unfortunately, it would NOT show data on a month if there was no screening that month, it would just skip to the next month with data. This may not matter. On the other hand it might be important to quickly see when a month has missing screenings.

By using explicit pidgeon holes (either with separate relationships or repeating fields,) you can get an overview that looks like panache's sample, showing when a month is missing data.

It just depends on what panache really needs.