Counting items in related tables

[humler]

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)

[Fenton]

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

AND

_cPassed = Qualification

"SubjectFailed" TO:

Subject = Subject

AND

_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.

PassFail.zip

[humler]

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!!

PassFail.fp7.zip

[-Queue-]

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.

[Fenton]

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.

PassFail2.zip