Results 1 to 7 of 7
  1. #1
    weazel91 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    3

    Report displaying 2 lots of records when I only want 1

    Hi all,

    First post here so I hope im providing the correct and enough information for everyone.

    I'm having real trouble with a query which I can't get to work properly.

    I have 2 tables, one table with items and another with test dates which FK to the PK of SerialNo of the items.

    When I run the query I get the following results:

    SerialNo Description InspectedDate NextInspectionDate SafeForUse Missing
    002106678 Round Slings Polyester1M EWL 16/01/2018 16/07/2018 Yes No
    002106678 Round Slings Polyester1M EWL 16/01/2018 16/07/2018 Yes No
    002106678 Round Slings Polyester1M EWL 17/01/2018 17/01/2018 Not Inspected Yes
    002106678 Round Slings Polyester1M EWL 17/07/2018 17/01/2019 Yes No
    002106678 Round Slings Polyester1M EWL 18/07/2018 18/07/2018 Not Inspected Yes
    002106678 Round Slings Polyester1M EWL 15/01/2019 15/07/2019 Yes No
    002106678 Round Slings Polyester1M EWL 16/01/2019 16/07/2019 Yes No
    002106678 Round Slings Polyester1M EWL 17/01/2018 17/01/2018 Not Inspected Yes
    002106678 Round Slings Polyester1M EWL 17/07/2018 17/01/2019 Yes No
    002106678 Round Slings Polyester1M EWL 18/07/2018 18/07/2018 Not Inspected Yes
    002106678 Round Slings Polyester1M EWL 15/01/2019 15/07/2019 Yes No
    002106678 Round Slings Polyester1M EWL 16/01/2019 16/07/2019 Yes No

    I understand I can use MAX for the Inspected and Next Inspected dates with totals which would give me the following:

    SerialNo Description InspectedDate NextInspectionDate SafeForUse Missing
    002106678 Round Slings Polyester1M EWL 18/07/2018 18/07/2018 Not Inspected Yes
    002106678 Round Slings Polyester1M EWL 16/01/2019 16/07/2019 Yes No

    What I would like the query to do however is only display the SafeForUse record which is Yes. I can't however set criteria to "Yes" in that column however as there may be some items that are missing and not safe for use for within the test period.

    Because the query is coming out like that, when we pull reports for missing items for their test period they are coming out as missing when actually they aren't as there is a SafeForUse record within date so the Not Inspected one should therefore not show in the query.

    I hope this makes sense. I'm just not sure how to proceed with this query.



    Hope someone can help.

    Many thanks,
    Chris

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I am a bit confused by the description of desired output.

    Possibly a WHERE IN() criteria will work.

    Build a SELECT query that returns records for the desired period and show SafeForUse as "Yes".

    Build another query:

    SELECT * FROM table WHERE SerialNo NOT IN(query1) AND possibly other criteria here;

    If you need more help, provide db for analysis because I don't want to build records for testing. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    weazel91 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    3
    Hi June,

    I have not yet had experience with WHERE IN () and this may just work!

    I'm not yet beside the database however will be at 12GMT so in 2 hours or so and can try this out.

    I am however correct in assuming that the first query will display all those items who are in date (i.e. between #01/01/2018# and #01/01/2019#) and safetouse ("Yes)

    and the second query would show all those records in the table who is in date (i.e. between #01/01/2018# and #01/01/2019#) and aren't already safe as they exist IN query 1.

    Am I understanding correctly?

    Many thanks,
    Chris

  4. #4
    weazel91 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    3
    Hi June.

    I have found and followed the instructions on this post https://support.office.com/en-us/art...d-eaf10a5d1cb4 which explains about the Fidn Unmatched Query Wizard.

    That has helped.

    Thanks again,
    Chris
    Attached Files Attached Files
    Last edited by weazel91; 04-24-2019 at 05:46 AM. Reason: SOLVED

  5. #5
    leckytech is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2019
    Posts
    8
    First post so apologies for not introducing myself. If you dont mind me highjacking this post. This is very similar to a problem I am having and would very much appreciate any help you could provide.

    I have a very similar query to the one above so if I can use that as an example. My issue is by using the Max function my query will return the most recent date tested but like above will return both the "safe for use" records. How can I modify the query to only return the most recent inspection with the corresponding "safe for use" value of which there are 10. I hope that makes sense!

    The field in question is the last one i.e electrician, ETO

    Click image for larger version. 

Name:	1.JPG 
Views:	11 
Size:	26.9 KB 
ID:	38386

    Many thanks for taking the time

    Paul

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Best to start your own thread with a reference to existing thread if you think the older info can help explain your issue. New threads get more attention.

    This thread already marked solved and again, gets less attention.

    Did you try the suggestion using IN()?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    leckytech is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2019
    Posts
    8

    Post


    Quote Originally Posted by June7 View Post
    Best to start your own thread with a reference to existing thread if you think the older info can help explain your issue. New threads get more attention.

    This thread already marked solved and again, gets less attention.

    Did you try the suggestion using IN()?
    Thanks June, I have created a new post.

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Report not displaying all the records?
    By djspod in forum Reports
    Replies: 1
    Last Post: 08-27-2015, 07:44 AM
  2. Lots of Help Needed!
    By kennuge in forum Database Design
    Replies: 2
    Last Post: 11-19-2014, 02:49 PM
  3. Replies: 4
    Last Post: 04-23-2014, 06:30 PM
  4. Replies: 3
    Last Post: 07-29-2012, 04:37 PM
  5. Displaying multiple records in a report.
    By CammRobb in forum Queries
    Replies: 10
    Last Post: 07-23-2012, 06:44 PM

Tags for this Thread

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