Jump to content

find multiple values from found set and display in new layout for each record


JoeHardstaff
 Share

This topic is 2372 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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. 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by LaRetta
Link to comment
Share on other sites

This topic is 2372 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.