Filtering with Criteria in SQL Query?

[Robeen]

Can I assume that QryPost_FSL_Post_Date [in the version of the db that you sent me] is returning the correct data?


If so - I'll take that as the 'starting point'.
See screenshot for the base data.

[Huddle]

Yes QryPost_FSL_Post_Date does return the correct data.

[Robeen]

I added a screenshot to my previous post - please tell me if that data is correct.

[Huddle]

I made all the changes on my real database. Let me make a copy, take out the secure data and resend it. It will take me a while but that way we can make sure I'm not missing anything.

[Robeen]

The data you provided in your test DB poses the same problem as your original data.
It does not represent all the different scenarios that you want to test for.
There is no data Within the last 14 days.

[Huddle]

Here is the updated db. The dummy data is closer to what I have in my db and there is more of it. I added more recent data as well.

[Robeen]

You changed the criteria of 'QryPost_FSL3_Before_Last_Two_Weeks' from:
< Date() -14
to:
< Date() - 7
?

But you left 'QryPost_FSL3_Within_Last_Two_Weeks' at:
>= Date() -14
?

[Huddle]

Hmmm. Not sure why. If the inspection is within a two week span it doesn't have to be performed so I guess it should be 14. My mind is swimming on this.

[Robeen]

I went through your newly posted db and all the queries run fine.
I got no errors.
Now - as to whether the data the final [UNION] query is producing - you will have to check that.

One part of the Union query was in there twice [my fault] - I fixed that.
I also added an 'Is Not Null' to take out a record that shows up with no Post number. [In red].
This is how the Union query looks now:

SELECT [QryPost_0-1-2].PI_Post_Number, [QryPost_0-1-2].FSL
FROM [QryPost_0-1-2]
GROUP BY [QryPost_0-1-2].PI_Post_Number, [QryPost_0-1-2].FSL
HAVING (((Count([QryPost_0-1-2].FSL))<2))
UNION
SELECT QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number, 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.PI_Post_Number <> QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number
UNION 
SELECT QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
FROM QryPost_FSL_Post_Date
WHERE (((QryPost_FSL_Post_Date.PI_Post_Number) Is Not Null) AND ((QryPost_FSL_Post_Date.FSL)=4))
ORDER BY QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL;

Have you checked the data to see if the final result is correct?

The problem with having hundreds of records in your tables while you're testing is that you may not know exactly how many should show up in the final query.
Still - you should check the data to verify.

Let me know if there is still an issue. I didn't have any problems running this last db you posted.

[Robeen]

Just want to make sure you see my latest post . . . above

[Huddle]

I got it. I've been checking the data. The FSL 3 is still not filtering properly. Example post 150 had been inspected 6/2, 6/5 & 6/7 but showed on the report.

Also we have a problem with the 1 & 2 q. That I think I can fix. There are a few post that are duplicates i.e. there is a 502 in ND and CO. I think if I add the building number to the q it should resolve the problem of counting the posts together.

[Huddle]

For some reason the criteria in the QryPost_FSL3_Show query is not working. I don't see any reason why it shouldn't be unless there has to be some command that the QryPost_FSL3_Within_Last_Two_Weeks has to run to make this query work.

[Robeen]

Have you got this figuered out?

If not . . .
Would it be possible for you to send me a small version of your DB [with sensitive data removed if necessary] so that I can see the errors for myself?
Make sure you leave some 'bad' records in there.
That was kind of what I meant about having good test data.
If you have a small set of records from which it is easy to keep track of which ones should, and which ones should NOT show up - then it becomes easy to test the queries.

[Huddle]

I removed excess data to focus on the 3s. In the q posts 150 (PostID 243), 603 (PostID 270) and 614 (PostID 269) should not be showing because they have had inspections in the last two weeks. I'm going to be out the rest of the day but I will work more on this tomorrow. Thanks so much for your help.

[Robeen]

In this:

SELECT QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number
FROM QryPost_FSL3_Before_Last_Two_Weeks, QryPost_FSL3_Within_Last_Two_Weeks
GROUP BY QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number
HAVING (((QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number)<>"[QryPost_FSL3_Within_Last_Two_Weeks].[PI_Post_Number] *"));

. . . the part in red is different from what I have in my version of your db. I don't have the quotation marks and the *. Why do you have that?