Counting the number of times a value meets a certain criteria

[Stretholox]

So I'm running into a problem that I'm not sure queries will solve.



I have students with competency values. I want to be able to count the number of times a student achieves a competency above a certain number.

The additional problem is that there are different types of competencies. I want to count whether or not a student achieved a certain percentage value on each type of competency and the total number of competencies they have achieved a high enough value on. These values also change depending on the type of competency.

So I have Competency A that has a pass percentage of 50% and Competency B that has a pass percentage of 70%. I need to know which students got at least a 50% on A and at least a 70% on B and also how many competencies they have gotten a passing score on. So if both then 2 if one then 1.

Can anyone point me in the right direction?

[June7]

How many competencies? Do you have a table of competencies with a field for the percentage criteria?

[Stretholox]

Yes there are tables for each competency. They have a unique ID number and a competency percentage. I'm just creating a test database at the moment so I only have 2 competencies. But in the future I need this to be able to handle about 10 competencies if necessary.

[Stretholox]

I will totally settle for just counting the number of competencies passed.

[June7]

I think the first step is a query that joins the student test data records with the compentencies table so the pass percentage will be available. Then you can calculate a field to show "Passed/Not Passed" depending on comparison of score to the pass percentage. This should give a query that shows every student results. Apply filter criteria as you desire. Use this query in an aggregate query if you want.

Suggest you consider building a report that uses Grouping & Sorting features with aggregate calcs in group footer section. This will allow display of detail records as well as summary data.

[Stretholox]

I'm not sure what you mean by student test data records. The only table is a competency table with two columns. The first column is the uniqued ID and the second column is their score on the competency. I have a query that takes those above a specific percentage on the competency. I need to be able to add to my main form a box that holds a number of how many competencies they have passed. In essence, if there was a way to count the number of times a name appears in a set of queries that would be enough. Since I could just have a query for each competency type. I mean, if there was a way to show all the passed competencies together by student that would be amazing but that would mean gaps in data for certain people. As obviously not every passes each competency.

[June7]

What you call the competency table has student test scores? Then that's the table I meant as 'student test data records'. It should have fields for StudentID, CompetencyID, Score.

Now you need a table that has a unique record for each competency with fields for CompetencyID and pass percentage.

Join these two tables on the common competencyID fields.

[Stretholox]

What you call the competency table has student test scores? Then that's the table I meant as 'student test data records'. It should have fields for StudentID, CompetencyID, Score.

Now you need a table that has a unique record for each competency with fields for CompetencyID and pass percentage.

Join these two tables on the common competencyID fields.

You are my hero. Solved.