Left join query with two criterias

[Morten]

I have two queries. The unique key in both queries is GUID for katalogposition.

One is showing me records which has an product end date (Produkt slut dato) between today and end date of next month. This query works fine and is called q_termination.

The second one shows me unmatched records in the first query (q_termination). The query works fine and is called yq_NonTermination.

The goal is now to show me records from the first query "q_termination" that fullfill one of two criterias.

1. No match in second query "yq_NonTermination"
2. Match BUT product end date (Produkt slut dato) is greater than the match in "yq_NonTermination".

I have made a left join query on the field "Dublet_Lagervarer". From the join query the goal is to show me only q_Termination.Guid for Katalogposition number 47 and 134008.

How can I do that? Is there another way to do it? Please see attachment.

Best regards
Morten

 

[CazB]

try adding this WHERE statement to your criteria?

WHERE (((yq_NonTermination.Dublet_Lagervare) Is Null)) OR (((yq_NonTermination.[Produkt slut dato])>=[q_Termination].[Produkt slut dato]))

... although that brings up a 3rd one as well....

 

[Morten]

try adding this WHERE statement to your criteria?

WHERE (((yq_NonTermination.Dublet_Lagervare) Is Null)) OR (((yq_NonTermination.[Produkt slut dato])>=[q_Termination].[Produkt slut dato]))

... although that brings up a 3rd one as well....

Thanks a lot. You pointed me in the right direction. I made a little change to the WHERE statement.

WHERE (((yq_NonTermination.Dublet_Lagervare) Is Null)) OR (((q_Termination.[Produkt slut dato])>[yq_NonTermination].[Produkt start dato]));

It seems to give med the right result.

 

[CazB]

Fab - wasn't sure if I'd got the logic right to get the right answer but thought it should at least help