Filtering with Criteria in SQL Query?

[Huddle]

I was probably playing around trying to find a solution and that was one I tried. I don't write code I do things in design view. I took out the quotes and * but it gives me this error "You tried to execute a query that does not include the specified expression 'Not QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number=[QryPost_FSL3_Within_Last_Two_Weeks].[PI_Post_Number]' as part of an aggregate function"



I tried putting in brackets and taking out brackets but it didn't make a difference. Since I don't know how to write code I don't know what it is referring to.

[Robeen]

Can you verify that in the DB you sent, the data in the two queries that are used in the 'Show' query - are producing correct data?
If so, then I will work on trying to figure out how to fix the 'Show' Query'.
By the way - I don't know why I named it ' . . . Show' . . . probably just so it would have a different name.
I'll get back with you.

[Huddle]

The other two are producting the correct results. The issue is in the Show Q.

[Robeen]

Ok . . . Let's try this again - using the last [most recent] db you posted.

I made a few changes in the Queries:
In the 1st one - I said PI_Post_Number is not null [because this is all about records that HAVE Post Numbers - right? . . . I hope that's right!! ].
In the 2nd & 3rd ones, I used the 'Distinct' keyword so that only one instance of each post would show up in each of those queries [not sure it was necessary to do that, but my assumption was that you only want to see each post number that requires inspection - one time in your query.
In the 4th query I used 'NOT EXISTS' instead of what I had before.

This looks like it is working on my pc. I hope this works for you.
Let me know!!

1. QryPost_FSL_Post_Date

SELECT Q_Bldg_Commander.Commander, Q_Bldg_Commander.FSL, Q_Bldg_Commander.District, Tbl_PI_Post.PI_Post_Number, Tbl_PI_Post_Insp.Insp_Date
FROM ((Tbl_Building_Information INNER JOIN Q_Bldg_Commander ON Tbl_Building_Information.Bldg_Number = Q_Bldg_Commander.Bldg_Number) LEFT JOIN Tbl_PI_Post ON Tbl_Building_Information.Bldg_Number = Tbl_PI_Post.PI_Bldg_Number) LEFT JOIN Tbl_PI_Post_Insp ON Tbl_PI_Post.PostID = Tbl_PI_Post_Insp.PostID_Number
WHERE (((Tbl_PI_Post.PI_Post_Number) Is Not Null));

2. QryPost_FSL3_Before_Last_Two_Weeks

SELECT DISTINCT QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
FROM QryPost_FSL_Post_Date
WHERE (((QryPost_FSL_Post_Date.FSL)=3) AND ((QryPost_FSL_Post_Date.Insp_Date)<Date()-14));

3. QryPost_FSL3_Within_Last_Two_Weeks

SELECT DISTINCT QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
FROM QryPost_FSL_Post_Date
WHERE (((QryPost_FSL_Post_Date.FSL)=3) AND ((QryPost_FSL_Post_Date.Insp_Date)>=Date()-14));

4. QryPost_FSL3_Final

SELECT QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number, QryPost_FSL3_Before_Last_Two_Weeks.FSL
FROM QryPost_FSL3_Before_Last_Two_Weeks
WHERE NOT EXISTS
(SELECT QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number
FROM QryPost_FSL3_Within_Last_Two_Weeks
WHERE QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number = QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number);

[Huddle]

I took your code into my main database. I did some tweaking by making the inspection dates "not null". I verfied all the data and everything works in the individual queries. When you run the QryPost_Union the data is incorrect, therefore the reports are incorrect. If I knew how to write code I wouldn't have to keep bothering you and could probably figure it out but I don't understand the language. What would cause the union q to change the outcome?

[Robeen]

Did you copy all the SQL from your individual queries into the Union query?
The way I usually build my Union queries is:
1. Create each individual query and run it to make sure it is giving the expected result.
2. Open each of the individual queries and go to SQL View [View -> SQL View]. Leave these open.
3. Create a New Query -> don't select any queries -> Click SQL View [top Left].
4. One at a time - copy the SQL from each query into the new [blank] query. Remove the ';' from the end of the query.
5. On a new line type in 'UNION' and hit Enter.
6. Copy in the next query & then put UNION on a new line. Remove the ';'.
. . . etc, etc
7. After copying in the last of the Queries, put a ';' at the end -> and then run the whole thing.

Make sure that each of the queries you copy in has the same number of fields and that they are in the same order.
If one of your queries has an extra field or two, delete the extra fields once you copy it into the Union query.

If you want, send me the SQL from your queries in a Private Message - and I will build the Union for you.
As long as the individual queries are working, the Union should work too [IF you are using the SQL from each one of them to make up your Union query].

[Huddle]

I created the Union and got it to work. The only problem was that it created records for those post that have duplicate number (i.e. CO post 504 and ND post 504 even though ND post 504 should have been the only record created). Therefore, I do need the building number associated with the post. I added it to all the SQL and they work fine except QryPost_FSL3_Show as below. I get an error " You tried to execute a query that does not include the specified expression 'PI_Post_Number' as part of an aggregated function. Can you help me work this out?

SELECT QryPost_FSL3_Before_Last_Two_Weeks.Bldg_Number, QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number, QryPost_FSL3_Before_Last_Two_Weeks.FSL
FROM QryPost_FSL3_Before_Last_Two_Weeks
WHERE (((Exists (SELECT QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number
FROM QryPost_FSL3_Within_Last_Two_Weeks
WHERE QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number = QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number) )=False))
GROUP BY QryPost_FSL3_Before_Last_Two_Weeks.Bldg_Number;

[Robeen]

It is not as simple as just adding fields to queries . . . especially if there is Grouping going on.
SQL code is picky . . . so it is easy to 'break' the SQL statement by making a change in mid-stream.

I thought I had posted all the queries for you - but I'm losing track!

Here are my queries. I attached a screenshot of the data that the UNION query returns. Tell me if the data shown in the screenshot looks correct to you.

1. QryPost_0-1-2_One_Inspection

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));

2. QryPost_FSL3_Show

SELECT QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number, QryPost_FSL3_Before_Last_Two_Weeks.FSL
FROM QryPost_FSL3_Before_Last_Two_Weeks
WHERE (((Exists (SELECT QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number
FROM QryPost_FSL3_Within_Last_Two_Weeks
WHERE QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number = QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number) )=False));

3. QryPost_FSL_4

SELECT QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
FROM QryPost_FSL_Post_Date
GROUP BY QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
HAVING (((QryPost_FSL_Post_Date.FSL)=4));

4. QryPost_Union_2

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
WHERE (((Exists (SELECT QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number
FROM QryPost_FSL3_Within_Last_Two_Weeks
WHERE QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number = QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number) )=False))
UNION SELECT QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
FROM QryPost_FSL_Post_Date
GROUP BY QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
HAVING (((QryPost_FSL_Post_Date.FSL)=4));

[Huddle]

You did give me the codes. But I had to add the building numbers to differentiate between the posts that had the same numbers. I've put my codes below. They all pull up the correct data but I screwed up the QryPost_FSL3_Show somehow.

QryPost_0-1-2_One_Inspection:


SELECT [QryPost_0-1-2].Bldg_Number, [QryPost_0-1-2].PI_Post_Number, [QryPost_0-1-2].FSL
FROM [QryPost_0-1-2]
GROUP BY [QryPost_0-1-2].Bldg_Number, [QryPost_0-1-2].PI_Post_Number, [QryPost_0-1-2].FSL
HAVING (((Count([QryPost_0-1-2].FSL))<2));


QryPOst_FSL_4:


SELECT QryPost_FSL_Post_Date.Bldg_Number, QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
FROM QryPost_FSL_Post_Date
GROUP BY QryPost_FSL_Post_Date.Bldg_Number, QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
HAVING (((QryPost_FSL_Post_Date.FSL)=4));

QryPost_FSL3_Before_Last_Two_Weeks

SELECT DISTINCT QryPost_FSL_Post_Date.Bldg_Number, QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
FROM QryPost_FSL_Post_Date
WHERE (((QryPost_FSL_Post_Date.FSL)=3) AND ((QryPost_FSL_Post_Date.Insp_Date)<Date()-14));

QryPost_FSL3_Within_Last_Two_Weeks:


SELECT DISTINCT QryPost_FSL_Post_Date.Bldg_Number, QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL
FROM QryPost_FSL_Post_Date
WHERE (((QryPost_FSL_Post_Date.FSL)=3) AND ((QryPost_FSL_Post_Date.Insp_Date)>=Date()-14));


QryPost_FSL3_Show:


SELECT QryPost_FSL3_Before_Last_Two_Weeks.Bldg_Number, QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number, QryPost_FSL3_Before_Last_Two_Weeks.FSL
FROM QryPost_FSL3_Before_Last_Two_Weeks
WHERE (((Exists (SELECT QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number
FROM QryPost_FSL3_Within_Last_Two_Weeks
WHERE QryPost_FSL3_Within_Last_Two_Weeks.PI_Post_Number = QryPost_FSL3_Before_Last_Two_Weeks.PI_Post_Number) )=False))
GROUP BY QryPost_FSL3_Before_Last_Two_Weeks.Bldg_Number;

[Robeen]

I renamed these with a _New in my copy of your db:
QryPost_FSL3_Before_Last_Two_Weeks_New
QryPost_FSL3_Within_Last_Two_Weeks_New
I used the exact SQL that you posted above in each of them.
Then I had this as my QryPost_FSL3_Show_New:

SELECT QryPost_FSL3_Before_Last_Two_Weeks_New.Bldg_Number, QryPost_FSL3_Before_Last_Two_Weeks_New.PI_Post_Number, QryPost_FSL3_Before_Last_Two_Weeks_New.FSL
FROM QryPost_FSL3_Before_Last_Two_Weeks_New
WHERE (((Exists (SELECT QryPost_FSL3_Within_Last_Two_Weeks_New.PI_Post_Number
FROM QryPost_FSL3_Within_Last_Two_Weeks_New
WHERE QryPost_FSL3_Within_Last_Two_Weeks_New.PI_Post_Number = QryPost_FSL3_Before_Last_Two_Weeks_New.PI_Post_Number) )=False));

See screenshot for the results I get.

[Huddle]

Everything is good and working fine. Thanks so much. I know you put a lot of hard work into this and I truly appreciate it. If I could bake you a cake I would.

[Robeen]

Glad I could help . . .

[Huddle]

OK Robeen, Got your thinking cap on??

My boss doesn't want the guys knocking out the level 1 & 2s right off the bat just to get them off the list. He wants them spaced out so he wants the criteria to be as below. I need help adjusting the QryPost_0-1-2 and/or QryPost_0-1-2_One_Inspection to reflect such. And of course the Union Q probably.

If no inspection has been done the 1st 3 months of the fiscal year (Oct, Nov, Dec) then it should show.
If one inspection has been done in the 1st 3 months then it will Not show.
If only one inspection has been done come July 1 then it should show again (of course if no inspection has been done it would still be on the list)
If two inspections have been done it will Not show any longer.

Can you figure it out???

[Huddle]

Another thing... He wants to show the "last inspected date" on the report for each shown post. Is that something we have to work into the query or is that something we do seperate?

[Huddle]

I need to change the Query for the 0, 1, and 2 FSL so that they will go back onto the report for the new fiscal year. I tryed entering "between 10/1/13 and 9/30/14" in the QryPost_0-1-2 query in the criteria but it did not change the report. Do I need to do a third query for the 0-1-2 FSL like we did for the FSL 3 to show which ones to show?


I think I got it with a Union.