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:
Code:
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?