I'm new at this and I'm trying to resolve a problem I have. There is a good chance I have gone about setting up the files in the wrong way but the solution I have has been used for a while & has live data in it.
I have file which has serverel tables in it but i'm interested in 3 tables, Pastoral, Strategy & Targets.
The Pastoral table is an orignal one from FM version 5 so has been is use for a number of years with pupil data in it.
Last year I setup the Strategy & Targets tables to hold records for pupils who are given improvement strategies. My main table uses a filed called UPN which is a unique identifier to identify a pupil. I have also added the same field to the tables.
All layouts are in the Pastoral Table.
I have created a layout which contains a portal so that appropriate targets or strategies could be have data entered in to them. When a record is created for a student the unique identifier (UPN) for the student gets added to the table.
On my tabbed layout a student could have multiple targets set with multiple dates when these are set.
All works well when looking at the individual student, (below is image or working part) the problem I have is when printing the records for all students.
I created a new layout which has multiple fields on it.
So for example I have Eng-Target, Sci-Target, Ma-Target plus also Eng-Strat, Sci-Strat, Ma-Strat so that I can print all record on one report for each pupil.
The problem I have is when I have multiple entries in my tables with targets etc on them, I only want to show the latetst targets.
So I added the portal field with my field on top of it & set the portal to sort on creation date in assending order. This works well but I have 180 students in the year group but only 60 of them have strategies set for them. How do I filter it down easily to only show the pupils who have entries in the targets or startegys tables?
If I alter my layout to show records from Targets Table if will only show data for the 60 pupils but the target fields will not display when the layout uses this method.
Any help & suggestions that can we offered would be approciated.