Ok . . .
I may be making this more complicated than it needs to be but . . . . it looks like it's working.
I added some more data to what you posted just to present some more possibilities [see screenshot Post_Table.jpg - to see what data my table has].
The final Union query uses the outcome of all the queries that I built one at a time to get each piece of the puzzle working.
I called my Table Post_Inspection. Substitute your Table name -> or change your Table Name to 'Post_Inspection'.
I'll paste each query into a code window for you.
Copy each one into a separate query in SQL View as follows:
Create -> Query Design -> Cancel (Don't select any Table) -> Click SQL View (Top Left) -> Paste SQL -> Save with the name I've indicated.
Name the queries like I have indicated [in green].
1. QryPost_FSL_4:
Code:
SELECT Post_Inspection.Post, Post_Inspection.FSL
FROM Post_Inspection
WHERE (((Post_Inspection.FSL)=4));
2. QryPost_0-1-2:
Code:
SELECT Post_Inspection.Post, Post_Inspection.FSL, Post_Inspection.InspectionDate
FROM Post_Inspection
WHERE (((Post_Inspection.FSL)<3));
3. QryPost_0-1-2_One_Inspection:
Code:
SELECT [QryPost_0-1-2].Post, [QryPost_0-1-2].FSL
FROM [QryPost_0-1-2]
GROUP BY [QryPost_0-1-2].Post, [QryPost_0-1-2].FSL
HAVING (((Count([QryPost_0-1-2].FSL))<2));
4. QryPost_FSL3_Before_Last_Two_Weeks:
Code:
SELECT Post_Inspection.Post, Post_Inspection.FSL, Post_Inspection.InspectionDate, CInt(Format([InspectionDate],"ww")) AS Week_Number
FROM Post_Inspection
WHERE (((Post_Inspection.FSL)=3) AND ((CInt(Format([InspectionDate],"ww")))<Format(Date(),"ww")-1));
5. QryPost_FSL3_Within_Last_Two_Weeks:
Code:
SELECT Post_Inspection.Post, Post_Inspection.FSL, Post_Inspection.InspectionDate, CInt(Format([InspectionDate],"ww")) AS Week_Number
FROM Post_Inspection
WHERE (((Post_Inspection.FSL)=3) AND ((CInt(Format([InspectionDate],"ww")))>Format(Date(),"ww")-2));
6. QryPost_FSL3_Show:
Code:
SELECT QryPost_FSL3_Before_Last_Two_Weeks.Post, QryPost_FSL3_Before_Last_Two_Weeks.FSL
FROM QryPost_FSL3_Before_Last_Two_Weeks INNER JOIN QryPost_FSL3_Within_Last_Two_Weeks ON QryPost_FSL3_Before_Last_Two_Weeks.Post <> QryPost_FSL3_Within_Last_Two_Weeks.Post;
7. QryPost_Union (This one gives you your final result):
Code:
SELECT Post_Inspection.Post, Post_Inspection.FSL
FROM Post_Inspection
WHERE (((Post_Inspection.FSL)=4))
UNION
SELECT [QryPost_0-1-2].Post, [QryPost_0-1-2].FSL
FROM [QryPost_0-1-2]
GROUP BY [QryPost_0-1-2].Post, [QryPost_0-1-2].FSL
HAVING (((Count([QryPost_0-1-2].FSL))<2))
UNION
SELECT QryPost_FSL3_Before_Last_Two_Weeks.Post, QryPost_FSL3_Before_Last_Two_Weeks.FSL
FROM QryPost_FSL3_Before_Last_Two_Weeks INNER JOIN QryPost_FSL3_Within_Last_Two_Weeks ON QryPost_FSL3_Before_Last_Two_Weeks.Post <> QryPost_FSL3_Within_Last_Two_Weeks.Post
UNION
SELECT Post_Inspection.Post, Post_Inspection.FSL
FROM Post_Inspection
WHERE (((Post_Inspection.FSL)=4))
ORDER BY Post_Inspection.Post, Post_Inspection.FSL;
When you run the last [Union] query, you should only see the rows of data you need.
See screenshot Post_Union_Query.jpg - to see what data my Union Query returns.
If it's not 'all there', you could try tweaking - or let me know where there's a problem.
The way I've done it, it's a bit on the tricky side . . .
I hope this helps!