Results 1 to 12 of 12
  1. #1
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127

    Query to indicate where specific records can NOT be found.

    I'm struggling with this today. I am trying to build a query that appends some text to a table if it can not find a specific record in a specific table.



    I have a table called [Survey] and a field called [Depth]. I would like to be able to quickly query if there are no records in [Survey] where [Survey]![Depth] = 0

    The indication would be in the form of appending text to a [Totals] table, something like "NO ZERO DEPTH RECORD FOUND".

    I swear I have had luck using NZ here, but for the life of me I cannot remember how I did it.

    Thanks

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Use the Unmatched Query wizard ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Quote Originally Posted by Welshgasman View Post
    Use the Unmatched Query wizard ?
    I haven't been able to get this to work, I think because there is no other table to compare with? The data type is only found in the [Survey] table before it is found to be missing and then appended.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There are several ways you can find if there are no zero depth records.
    For example, start by creating a query to count zero depth values

    Code:
    SELECT Count(Survey.Depth) AS CountOfDepthFROM Survey
    WHERE (((Survey.Depth)=0));
    Or more simply, use the DCount function
    Code:
    If DCount("*","Survey","Depth=0") = 0 Then
    However you should not be appending such text to a Totals table where it will remain in place even if a zero depth value is later added.
    That sort of information should be obtained on demand using a query
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You can use a table/query more than once in a query?, though I am not sure how your logic will work if it is the same table?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Quote Originally Posted by isladogs View Post
    There are several ways you can find if there are no zero depth records.
    For example, start by creating a query to count zero depth values

    Code:
    SELECT Count(Survey.Depth) AS CountOfDepthFROM Survey
    WHERE (((Survey.Depth)=0));
    Or more simply, use the DCount function
    Code:
    If DCount("*","Survey","Depth=0") = 0 Then
    However you should not be appending such text to a Totals table where it will remain in place even if a zero depth value is later added.
    That sort of information should be obtained on demand using a query
    Thanks. For whatever reason these are still just returning all the records where there is good data. I also need to use this with another table and in inner join, but that is based on a different field [ID]

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry but I don't understand your reply. Perhaps it would be best to post a cut down copy of your database with just the relevant table(s). Change anything confidential first
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    OK. I have attached an example database. test.zip

    In this example, the single query 'qryReturnMissingRecord' should do the work. The goal is to search the table 'tblSURV' to determine if there is a record where [DEPTH] = 0. The complicating factor here is that the query limits the search to a specific [RECNAME] using an inner join from table 'tblMAIN'.

    If the query determines that there is NO record where [DEPTH]=0 for the selected [RECNAME] then it will append some sort of notice to the table 'tblCHECKS'.

    The fields to be appended are [RECNAME] to [RECNAME] as well as some sort of custom written statement like 'MISSING 0 DEPTH SURVEY' to the field [RESULT] in 'tblCHECKS'

    I hope that makes sense. It's always difficult to describe what I'm trying to do. This DB will ultimately be split if that matters.

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Two things. First, tblMain is showing Test-02, and there is no Test-02 zero record in tblSurvey. Change tblMain to show Test-01.
    Then change your append query:


    Click image for larger version. 

Name:	depth.png 
Views:	10 
Size:	29.3 KB 
ID:	44077

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK you've simplified the data a bit too much and your approach is never going to work.

    First of all I've changed the ID field names to MainID, SurvID, CheckID for clarity
    I've added additional records to tblMain - you now have TEST-01 through to TEST-04 and I've added corresponding records to tblSurv so that only TEST-01 & TEST-03 have zero depth values

    These are identified using the query qryTestDepthZero
    Code:
    SELECT tblSURV.RECNAME, tblSURV.DEPTHFROM tblMAIN INNER JOIN tblSURV ON tblMAIN.RECNAME = tblSURV.RECNAME
    WHERE (((tblSURV.DEPTH)=0));
    I've included both tables in case your real tables have additional fields you need here....but this would work equally well: qryTestDepthZero _v2
    Code:
    SELECT tblSURV.RECNAME, tblSURV.DEPTH
    FROM tblSURV
    WHERE (((tblSURV.DEPTH)=0));
    Now create an outer join query with tblMain and one of the above queries to list tests with no zero depth values qryTestNoDepthZero
    Code:
    SELECT tblMAIN.MainID, tblMAIN.RECNAME, "MISSING 0 DEPTH SURVEY" AS RESULT
    FROM tblMAIN LEFT JOIN qryTestsDepthZero ON tblMAIN.RECNAME = qryTestsDepthZero.RECNAME
    WHERE (((qryTestsDepthZero.RECNAME) Is Null));
    NOTE: the added Result field with the wording you wanted.
    This query has everything you need. You DON'T need tblChecks at all and using it will give you permanent records which may later become invalid

    However, whilst NOT recommended, if you really MUST append records to tblChecks, use qryAppendTestsNoDepthZero:

    Code:
    INSERT INTO tblCHECKS ( RECNAME, RESULT )
    SELECT qryTestsNoDepthZero.RECNAME, qryTestsNoDepthZero.RESULT
    FROM qryTestsNoDepthZero;

    See attached
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Thanks very much Colin. This did the trick. I slimmed it down to two queries by combining the append query and it runs nicely. The table where appended results are stored gets completely cleared and out repopulated with each run of a larger macro, so no worries there.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome.
    I could have merged the append and unmatched queries but was trying to discourage you from using the append query at all.
    Even if you are replacing tblChecks on a regular basis, its unnecessary as you can get the info direct from a query.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Handwritten records not found by query
    By AbramF in forum Queries
    Replies: 6
    Last Post: 03-30-2020, 09:34 AM
  2. Replies: 12
    Last Post: 04-25-2013, 01:32 PM
  3. Need some help with a No Records Found query
    By kilosierra71 in forum Queries
    Replies: 1
    Last Post: 08-19-2011, 02:56 PM
  4. Split string when specific word is found
    By DB4284 in forum Programming
    Replies: 1
    Last Post: 11-18-2010, 03:30 PM
  5. Number of Records Found by a Query?
    By Xiaoding in forum Queries
    Replies: 3
    Last Post: 03-05-2010, 03:34 PM

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