Hi everyone, thank you for any help you give...

I have a problem that I am finding difficult to explain, but here goes,

I have two tables, one that stores student details and one that stores effort scores. The effort scores are for each lesson in a week and each student has many effort scores. 

The effort scores records are weekly so week commencing is what separates each record. The tables are linked via studentID and EffortID. The entry of this data is fine and it all works really well. I'm trying to produce some analysis. I would like each student listing with the effort score total for each week in a term. (Week1, Week 2 etc) These can be defined by week commencing. I just cant seem to get anywhere with the find function, lookup or ExecuteSQL. 

Can anyone suggest a way of doing this for each record?

Any help would be hugely appreciated. 

27 minutes ago, JoeHardstaff said:

I would like each student listing with the effort score total for each week in a term. (Week1, Week 2 etc)

 

Are you after the total across all effort records for a given student?  That part can be done with a summary field that is total of the effort score then building a layout with a subsummary part when sorted by student and "week beginning".

However, that will give you a kind a list of view that shows:

Student A

-- week 1 - total

-- week 2 - total

Student B

-- week 1 - total

-- week 2 - total

 

If you want the result in a more cross-tab kind of way then you'll need another approach.

3 hours ago, JoeHardstaff said:

The tables are linked via studentID and EffortID

That doesn't look right. They should be linked by StudentID only.

 

3 hours ago, JoeHardstaff said:

I would like each student listing with the effort score total for each week in a term.

Wim's suggestion is the easiest and the most straightforward approach: produce a report of the Scores table, summarized by student and by week.

Thanks everyone, I can get it as a summarised report but need it with weeks across the top and then records displaying for each student. 

15 hours ago, comment said:

That doesn't look right. They should be linked by StudentID only.

It is linked via StudentID my mistake. 

Any other approaches? I tried a script to set the field based on a variable. This nearly works but it applies the variable to all records not each record individually.  

10 minutes ago, JoeHardstaff said:

need it with weeks across the top

That is more difficult. Is there a fixed and known number of weeks? Filemaker is not flexible in the horizontal direction and will not add columns dynamically.

Thanks Comment Consultant that is what i'm beginning to learn/fear! I'm am converting from Access so i'm having to rethink ways of doing things. I really do like Filemaker but this would be quite simple in Access. 

23 minutes ago, comment said:

That is more difficult. Is there a fixed and known number of weeks? Filemaker is not flexible in the horizontal direction and will not add columns dynamically.

There are a known number of weeks. 7 for most terms or 38 for the whole year

One possible way is to use filtered portals. This works quite well for modest amounts of records to filter. See the attached demo:

CrossTabListView.fp7

And, new in version 14, one can use the new Refresh Portal script step which refreshes the relationship and contents of the named object by simply object-naming the portal.  It is much lighter than Refresh Window [ Flush Cached ]. :-)

Edited January 3, 201610 yr by LaRetta