Jump to content

Counting items in related tables


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

Recommended Posts

  • Newbies

Hi all,

I'm new to Filemaker. I've always worked with access and now switching to Filemaker. But I'm lost. I have a lot of exams records in two tables. Each table from a different school. I have, among others, two fields: subject and qualification (and only two items in it: passed and failed). I have thousands of student records. I would like to count fail and pass exams for each subject and build a layout. More or less like this:

Subject Passed Failed

Chemistry 80 91

Maths 100 80

Biology 230 25

Please, could anyone help me? I know it may be an easy problem but I do nead help.

Thank you all in advance

PS Sorry for my poor English, I'm from Barcelona (Spain)

Link to comment
Share on other sites

Create a couple of calculation fields:

_cPassed = "Passed"

_cFailed = "Failed"

They can be unstored (a Storage option). Seems goofy, but you need them for the left side of the relationships.

Then create a TO and relationship for each of the statuses, a compound relationship:

"SubjectPassed" TO =

Subject = Subject


_cPassed = Qualification

"SubjectFailed" TO:

Subject = Subject


_cFailed = Qualification

Then just create a couple more calculation fields to count.

_cCountPassed = Count ( SubjectPassed::Subject)

_cCountFailed = Count ( SubjectFailed::Subject)

This will be for the entire table. Not for a found set. To do that you'd create a calculation field to produce a 1 for Passed, or a 1 for Failed. Then create a Summary field to Count each. Then a Subsummary part and report, or a GetSummary( Passed1, Subject) field(s). Sort by Subject.

Or add more conditions (such as date range) to the compound relationships.

If you want it for a found set, then post again, with more description of what form you want the results in, and we'll (or someone will) answer the above in more detail.


Link to comment
Share on other sites

  • Newbies

Fenton thanks a lot,

I see how self joins works. I have to admit that I was too much used to MS-Access and I have to change my mind to filemakes. I feel as an absolute beginner, more than this even! I have another problem and I'm sorry for disturbing you with these probably obvious questions for you. I have to scores tables (one for each center) I have repeat the steps you taught me. I have two relationships subjects to scores and subject to scores2. My problem is I can't create new records. I can create them in scores but subject table is not updated and I can't create new records in scores2. I've attached the new file in the post

Thanks a lot Fenton!!


Link to comment
Share on other sites

Hmm. Well, your Scores2 layout is showing fields from Scores. Also, Subjects needs to be populated independently from your Scores tables. It would probably work better to use a value list based on Subjects to select the subject in Scores. Then you can ensure that a subject is not entered that does not already exist in the Subjects table.

Link to comment
Share on other sites

I think I'd do it like this. You say you have 2 Centers. So that would be 1 table. Then enter data into portals, on the way down. That is the easiest way to do it.

You could alternatively have 2 separate Scores tables. But I don't really see that as necessary; especially since you never need to got there, and you can easily separate via Centers.

The advantage of such a relational structure is that if you ever wanted to do a report on overall performance it would be easy. With 2 Scores tables it wouldn't be so easy. It depends.


Link to comment
Share on other sites

This topic is 6621 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
  • Create New...

Important Information

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