Results 1 to 3 of 3
  1. #1
    wtucker is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    50

    Query to Show Old Record only if New Record does not exist

    Hello all,

    I'm sure the title doesn't explain much, so let me elaborate on my problem.

    We have customer contracts in a table, with fields such as CustomerID, CustomerName, ContractStartDate, etc.


    Each customer renews (or doesn't) on the Contract Start Date. I want to write a query to show me which customers haven't yet renewed their contracts for a given period.

    So say fifty customers are due to renew on 5/1. Forty have already submitted their papers, and we have their records for the current year. I want a query to show me the other twenty.
    I'd really prefer to do it without SQL, as the people managing the data aren't familiar enough with it.

    Much appreciated!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'd really prefer to do it without SQL, as the people managing the data aren't familiar enough with it.
    If you use forms, a user could click a button without ever seeing the SQL or whatever was behind the button.

    If you know 40 have already submitted their papers and they are recorded for the current year, couldn't you
    query your customer and customer renewal tables to find those that have not renewed?
    You'll get more focused answer if you tell us about your table designs.
    Last edited by orange; 04-19-2018 at 03:53 PM. Reason: spelling

  3. #3
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    We don't have enough information to give you an exact answer. Is there a field in your table to indicate if a customer has renewed their contract? If so then your query might look something like;

    SELECT * FROM YourTable WHERE Renewed = False AND ContractSartDate = #5/1/2018#

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

Similar Threads

  1. Replies: 2
    Last Post: 03-14-2017, 02:48 AM
  2. record doesn't exist
    By slimjen in forum Forms
    Replies: 7
    Last Post: 08-13-2014, 11:07 AM
  3. Replies: 4
    Last Post: 12-14-2012, 06:33 PM
  4. Check if record exist
    By khhess in forum Programming
    Replies: 1
    Last Post: 06-14-2011, 06:56 PM
  5. If record does not exist, open add new record form
    By RobertIngles in forum Programming
    Replies: 2
    Last Post: 02-01-2011, 08:47 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