Creating a filter in one table that effects all other tables

[Dawsonh4]

I am very new to Access and have figured out how to create appropriate relationships between tables, but I am having trouble filtering out data from the first level table based on filters applied to the third level.

Just for clarity I will explain what I am doing. I have a database with power plants in the US in table 1, it lists their location and ID. In table 2 I have data for those power plants for each year from 09-2014. Finally, in table 3 I have unit specific data for each power plant for the given year.

I want to filter just the combined cycle units in table 3 and have that return only the appropriate plants in table 1 and 2.

I appreciate any help.

[Seth Schrock]

If you have your tables related properly, then by filtering one (or more) power plant in table 1, you will only get those records that are related to that power plant in tables 2 and 3. What have you tried and what result did you get?

[Dawsonh4]

I have tried this, and they do not filter across tables either way. I guess my relationships are incorrect.

Currently I have table 1 ID related to a field in table two through a one-to-many type 1 relationship. Table 2 relates to table 3 in the same way (with table 2's ID to a field in table 3)

[Seth Schrock]

The relationships look right. How are you implementing the filter? For example, are you using a WHERE clause in a query, using a form filter, etc.

[Dawsonh4]

No, I am not familiar with using a WHERE clause. I simply hit the filter button on the ribbon and the pull down arrow on the column I wanted to filter.

Is there a more appropriate way I should be doing things?

[Seth Schrock]

If you are looking at the table when you do that, then yes, there is a better way. If you go to the Create tab on the Ribbon, select Query Wizard, select Simple Query Wizard and click OK. In the Tables/Queries drop down box, select Table1 and then select the fields that you want from that table. Then change to Table2 and do likewise and then Table3. Once you have all the fields that you want, click Finish. Now, when you filter, the related fields from Table2 and 3 will also filter.

This is probably the simplest method, but there are certainly other, more stylish ways to do it.

[Dawsonh4]

Unfortuneatly, this doesn't seem to be working for me. I must be missing something.

I created a query per instructions. I then opened table 1 and put a filter the plant city field. I opened table 2, 3, and the query I just created and they are returning all entries still.

Any suggestions on what I am doing wrong?

[Seth Schrock]

You don't need all the tables open. You just look at the query and perform the filter on the query.

[Dawsonh4]

I follow you now.

The problem that exists with this method is that I can't put in a filter for all combine cycle units from table 3 and have it return only the plant names in table 1 with combine cycle units. Instead it returns plant names repeated for every combine cycle unit at that plant.

I would like to try and filter table 3 for different things such as combine cycle and be able to go back to table 1 and have it show only those plants with CC.

Is this possible?

[Seth Schrock]

That you can't do with a plan filter, but you can using the WHERE clause on a query. Open the query in Design view. In the query properties window, make Unique Values be True. Then uncheck the checkbox under each field that you don't want to show. Then, under the field that you want to filter, type the text that you want to filter by in the Criteria line. For example, you might enter "Combine Cycle" Or "Other Thing". Notice the double quotes and the key word Or. Let me know how this works for you.

[Dawsonh4]

That did the trick. Thanks for all your help!