Hide specific query results by subtable

[Muffel2k]

Hi,

I would like to create a query to gather records from a table by excluding some based on a subtable.


First table (which returns the query results)

Field 1	ID_Projekte
Field 2	Projektname
Field 3	Bezeichnung
Field 4	Bemerkung

The query to return all the projects is simple but now I would like to remove some of the results based on a second table looking like this:

Second table (additonal information for each project)

Field 1	ID_Projektphase
Field 2	ID_Projekte_FK
Field 3	ID_ProjektphasenID
Field 4	Bezeichung

"Field 1" from the first table is linked to "Field 2" from the second table with a 1:many relationship (which means each project from the first table can have multiple entries on the second table. The difference is "Field 3" on the second table which is an indicator for a specific process. This field contains a number between 1 and 20 and there can be up to 10 records for each project). What I want to do is to show all Projects which do not have a value of "4" or "10" in "Field 3" on the second table. If I just add an additional criteria to my query it still shows the project as there are entries with "ID_ProjektphasenID" = 1 (or another value) but I need to hide the project completely if there is an entry with 4 or 10.

right now my SQL query Looks like this:

SELECT DISTINCT tbl_Projekte.ID_Projekte, tbl_Projekte.Projektname, tbl_Werkzeugtypen.Bezeichnung, tbl_Projekte.Werkzeugnummer, tbl_Projekte.Projekttyp, tbl_Projekte.Bemerkung
FROM tbl_Werkzeugtypen INNER JOIN (tbl_Projekte INNER JOIN tbl_Projekte_Phasen ON tbl_Projekte.ID_Projekte = tbl_Projekte_Phasen.ID_Projekte_FK) ON tbl_Werkzeugtypen.ID_Werkzeugtypen = tbl_Projekte.Werkzeugtyp
WHERE (((tbl_Projekte.Projekttyp)=[Formulare]![frm_Kundenauftraege]![Projekttyp]) AND ((tbl_Projekte_Phasen.ID_Projektphasen_FK)<>4))
ORDER BY tbl_Projekte.Projektname DESC;

and here is a screenshot of my Project


the table in the middle is "Table 1" and the right one is "Table 2".
Any ideas what I can do?

[Bulzie]

Replace the table with a query that excludes 4 and 10 from the start, then use that query in your joins above. Or you can insert the query above into a new query, add all the fields then filter in criteria for <> 4 and <> 10, etc.

[Muffel2k]

I think I've got your point but how do you manage to exclude the whole project? Let's say I have these projects created on table 1:

Test 1
Test 2
Test 3

on table 2 there are several entries for these projects. As example:

Test 1: ID_ProjektphasenID = 1
Test 1: ID_ProjektphasenID = 3
Test 1: ID_ProjektphasenID = 4
Test 2: ID_ProjektphasenID = 9
Test 2: ID_ProjektphasenID = 11
Test 3: ID_ProjektphasenID = 10

My query returns all of them and with the criteria I still get "Test 1" and "Test 2". "Test 3" is removed due to the criteria but "Test 1" is still there because there are entries with different values than 4 and 10.
"Test 1" should be removed as well. Not sure how to do that.

[aytee111]

Create a query of all projects that have a 4 or a 10. Then exclude those in your next query (left join with project=Null).

[Muffel2k]

worked like a charm, thank you!