Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318

    Filtering with Criteria in SQL Query?

    I am in new territory and totally confused.



    I have a report with a SQL Query as a Record Source. One of the fields is [FSL]. What I need to do is filter the report so that it does the below but what I don't know is can you do this through the criteria and if so how, or do you have to do it some other way.

    The second problem is I don't know how to write the expression or code to perform the task.

    What I want to perform is:

    If [FSL]= 0, 1 or 2 and there are more than 2 records for a unique post then don't list it; If [FSL] = 3 and there is a record dated for this or the previous week don't list it; If FSL = 4 List it

    Example: Would list 101, 102
    Post FSL Date
    101 3 01/06/12
    101 3 03/01/12
    100 1 04/03/12
    103 3 04/04/12
    102 4 04/05/12
    105 3 05/04/12
    100 1 05/06/12
    100 1 05/12/12
    103 3 05/29/12


    I'll be asking for more and more help since I am way beyond my skill level at this point

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Are there two separate problems?
    If so, can you tell me what the first problem is, please?
    I don't understand what this means:
    What I need to do is filter the report so that it does the below
    Also, can you please explain this:
    and there are more than 2 records for a unique post
    In short, if you could rephrase your question, it might make it easier for me to try & help.

  3. #3
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    The report's function is to list all the guard posts that are due for inspection. If a post that has a FSL of 0, 1, or 2 has had two inspections it does not need to show on the report. If a FSL 3 post has been inspected the current or previous week it does not need to be listed. The FSL 4s will always be listed.

    I hope this helps.

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I'm leaning towards a multi-query solution. I'm working on it already - but you can try it for yourself if you want.
    Here's the concept:
    Make a query that meets each of your criteria [Eg: one for FSL 0, 1, 2; another one - based on the previous one - that has only one inspection . . . Etc . . .].
    What you'll do at the end is perform a Union All on the queries you come up with for each of your criteria.
    Ther may be a single-step solution but my sql skills are not that advanced.
    I'll get back with you in a little.

  5. #5
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    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!
    Attached Thumbnails Attached Thumbnails Post_Table.JPG   Post_Union_Query.JPG  

  6. #6
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    Wow! I'd neve figured this out in a million years.. OK so to throw some more in the pot.

    The Post number, FSL and Inspection dates come from 3 seperate tables so I can bring them into a query and replace your 'Post_Inspection' table with the query name, correct?

  7. #7
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    lol . . . I've slept since I posted that solution . . .

    Yes - you can bring them into the equation by creating a query that replaces my 'Post_Inspection' table.
    In fact - you can call the query 'Post_Inspection'.
    I just got into the habit of naming my queries starting with 'Qry'. That way I always know whether a data source is a query or a table.

    Let me know how this works out for you.

    Again, there might be an easier solution . . . but I was not able to come up with it.

  8. #8
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    Yea it took me a while to get back with you. I think I got everything change but I'm getting a "Data type mismatch in criteria expression" error. Where should I be looking to correct this?

  9. #9
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    There's probably an issue in the criteria [after the 'WHERE' . . .] in one of the queries.
    You could try running each one separately to see which one is causing the problem.
    If it's the final [Union] query that is the problem, then there are a couple of things that could be happening.
    Make sure each part of the Union query has the fields in the same order. If you mix up the order - the Union query may not work.
    What are the data types of the different fields?

  10. #10
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    These are the Queries that are bringing up the error

    QryPost_FSL3_Before_Last_Two_Weeks:

    SELECT QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL, QryPost_FSL_Post_Date.Insp_Date, CInt(Format([Insp_Date],"ww")) AS Week_Number
    FROM QryPost_FSL_Post_Date
    WHERE (((QryPost_FSL_Post_Date.FSL)=3) AND ((CInt(Format([Insp_Date],"ww")))<Format(Date(),"ww")-1));

    QryPost_FSL3_Within_Last_Two_Weeks:

    SELECT QryPost_FSL_Post_Date.PI_Post_Number, QryPost_FSL_Post_Date.FSL, QryPost_FSL_Post_Date.Insp_Date, CInt(Format([Insp_Date],"ww")) AS Week_Number
    FROM QryPost_FSL_Post_Date
    WHERE (((QryPost_FSL_Post_Date.FSL)=3) AND ((CInt(Format([Insp_Date],"ww")))>Format(Date(),"ww")-2));


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


    PI_Post_Number is Text; FSL is Numeric; Insp_Date is Date/Time

  11. #11
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I just changed my 'Post' data type to Text' and ran my version of the queries and there were no problems.
    However, I know that you are doing things a little differently and you don't have a Table named 'Post_Inspection' like the one I created . . .
    I wonder if there is a problem with the Data Type in one of the other fields.

    It might be easiest if you are able to post a copy of your database here for me to look at. If there is sensitive data, you can delete it and just include enough fake data for me to be able to see the error for myself.

    If you can't post your db here - you will have to back-track one step at a time and see where the problem arises.
    I would go back to 'QryPost_FSL_Post_Date' and examine what exactly it is getting - from wherever it is getting its data.

    You said:
    The Post number, FSL and Inspection dates come from 3 seperate tables so I can bring them into a query and replace your 'Post_Inspection' table with the query name, correct?
    Are you certain that the data types in ALL three tables are exactly the same?

  12. #12
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    I copied that section of the database and created fake data for it. I think I got it all there.
    Attached Files Attached Files

  13. #13
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    It is reading the "ww" as a date rather than a number in the WHERE part of the equation. When I moved that part of the equation under the date it did not show the error though it didn't pull up the correct data.


    Scratch that.... I think I have it...maybe....sometimes there is no date therefore the week number gives and #error..

    Would that be the cause and if so how can we work in an if error into the statement?


    Scratch again....Found another issue. We can't use the Week Number because we have multiple years. We'll have to use the actual date. So I used >=Date()-14 under Insp_Date for the criteria for the Within Last Two Weeks Q and <Date()-14 for the criteria for the Before Last Two Weeks Q. In the QryPost_FSL3_Show I need the Within Last Two Weeks to be filtered from Before Last Two Weeks. Then it should work.
    Last edited by Huddle; 06-07-2012 at 10:40 AM.

  14. #14
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I'm not sure I understand exactly where you're at with this now!!
    Can you tell me again what you need?

  15. #15
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    The formula isn't working

    Click image for larger version. 

Name:	Untitled.png 
Views:	14 
Size:	15.4 KB 
ID:	8000

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-13-2011, 11:00 AM
  2. Filtering and using First() inside a query
    By Gilligan in forum Queries
    Replies: 17
    Last Post: 03-10-2011, 05:52 PM
  3. Filtering a Query
    By Brian62 in forum Queries
    Replies: 2
    Last Post: 06-03-2010, 01:46 PM
  4. Filtering a Query
    By jbarrum in forum Access
    Replies: 1
    Last Post: 11-20-2009, 03:03 PM
  5. Filtering recs from query ...
    By rfhall50 in forum Forms
    Replies: 0
    Last Post: 02-18-2009, 09:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums