Query based upon table exclusions and inclusions.

[whipper]

Ok lets see if I can explain.
I want to make a query that will set criteria based upon entries in a table. So, let me give you some data to play with.
Table1 - field1, field2
row1 - l2a1234, Y
row2 - l2a2345, Y
row3 - l2a3456, M
row4 - l2a4567, N

Table2 - field1, field2, field3
row1 - "Y", "table1", "field2"
row2 - "N", "table1", "field2"

Output:
row1 - l2a1234, Y
row2 - l2a2345, Y
row4 - l2a4567, N

Normal SQL for Table1 to provide Output is:
SELECT field1, field2, field3 FROM Table1 WHERE field1 = "Y" OR "N";

But what I want is the ability to look into Table2 and see that I need to select all data that matches entries in table1.field2. So since table2 has "Y" and "N" then I need to find all entries in Table1 that are "Y" or "N."

Is this possible? This is the easy version, I'm really trying to build a single location to view what is included or excluded in a database.

 

[amorous]

You may want to try something like this:

SELECT Table.Field1, Table1.Field2 FROM Table1
LEFT JOIN Table2 ON
Table1.Field2=Table2.Field1

Hope this helps.

-VJ

 

[whipper]

This works fine until I have and another row to table 2 so it looks like this:

Table2 - field1, field2, field3
row1 - "Y", "table1", "field2"
row2 - "N", "table1", "field2"
row3 - "N", "table4", "field2"

I need to set criteria to only look at table2 - field2 and match the text "table1". I also need to do the reverse where I exclude them.
Maybe this might help: What about building an SQL statement for the "Criteria" of the query. How do I make the SELECT statement with "AND" and/or "OR" and/or "NOT"? Otherwise output to "Criteria" ("Y" AND "N")?

 

[amorous]

I am not sure what you want to do. Can you list some data of your both the tables table1 and table2 and also the kind of result you are looking for.

-VJ