Query to show duplicates in 3 out of 4 tables

[figuringitout]

I am working with test score data that has been compiled and stored over the years. I have already created queries that will find the bottom 10% of scores for each year but now I want to look and see what schools consistently score in the bottom 10% over the years. We only have 3 years worth of data currently. I can create a query based upon the relationships to show schools that have scored in the bottom 10% during the last 3 years but going forward how can I create a query to show schools that have scored in the bottom 10% in 3 out of the last 4 years?



Basically is there a way to create a query designed to show if an entry appears in 3 out of 4 tables?

Thanks

[June7]

You have each year of data in a separate table? Should be one table.

[figuringitout]

Ok. I have all the data in one table but then I break it into separate tables to show the bottom 10% of schools for each year. So if I were to use the master table with all of the information how would you suggest going about that to show if a school is in the bottom 10% in 3/4 years?

[June7]

What do you mean by 'break it into separate tables' - are these just queries?


Not an analysis I've ever had to do but maybe:

First do query that calculates the percentage for each school for each year.

Use that query in another query to do an aggregate (GROUP BY) Totals query to count how many years each school is in bottom 10%, restricted to last 4 years by filter criteria.

[figuringitout]

Yes. So I have one "Master" table with each school's grades from 2012 to 2015. I then created a query to return "flagged schools" which are schools that scored in the bottom 10%, did not turn in exams, and schools that we had a discrepancy with. I used this query to create separate "flagged schools" tables for each year. Sorry for leaving the other criteria that made a school in this "flagged school" category. If it was as simple as the bottom 10% scoring each year that would have made this a lot easier.

I do like the idea of a count because that would be pretty easy to do. Is there a way to count how many times a school shows up across multiple tables? If not then I may have to rearrange the way I have set up the data in order to make this count work.

[June7]

By 'multiple tables' do you mean 'multiple queries'? Regardless, the answer is no. Not without doing a UNION of the 'multiple tables' or using VBA and/or DCount domain aggregate function.

[figuringitout]

Ok. Thanks. I just used one query and changed the minimum score a school could have to create each separate table.

Anyways the results are kind of like this.

Table 1
School ID
0001
0002
0003
0004

Table 2
School ID
0002
0004

Table 3
School ID
0001
0002
0004

Table 4
School ID
0001
0003
0004

So what I'm trying to get out of it is a table like this:

School ID___ Count
0001________ 3
0002________ 3
0003________ 2
0004________ 4

I understand how to use a UNION to look at each table and then return all of the unique values from each of them. I just didn't know if I can then count how many tables or times these values appear between all 4 tables.

From my understanding the DCOUNT function can only be used in one table.

I appreciate your patience and helpfulness with this.

[June7]

You keep saying table when I think you mean query dataset. Might seem like minor distinction but can significantly impact understanding your goals.

I still don't know why you have 4 queries instead of 1 query with grouping and filter criteria.

Show example of raw data from the source table.

Yes, DCount can reference only 1 table/query.