Results 1 to 3 of 3
  1. #1
    avisam is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2019
    Posts
    1

    Contracts that were not renewed

    Hello,



    I have Contracts table with the following fields:
    ContractID - key of the table
    ContractNum - can appear multiple times in case the contract was renewed
    StartDate
    EndDate
    RenualReminderDays

    If a contract is renewed - I generate new record with new ContractID and the same ContractNum.

    I wan to return the record that: Diff between today and EndDate <= RenualReminderDays
    and that for each one there is no record in Contracts with the same ContractNum and with StartDate>EndDate (I mean it was not renewed).

    How can I do it in access without using Not In?

    Thank you

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by avisam View Post
    If a contract is renewed - I generate new record with new ContractID and the same ContractNum.
    To me - this is just me - it sounds like a design problem. I would have a table for contract numbers (a text field) and a table for renewal dates.
    Consider
    Click image for larger version. 

Name:	Renewal1.png 
Views:	13 
Size:	19.8 KB 
ID:	39898

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Nor should you be storing calculated data (reminder days) if that's what is meant by Renual(sp?)Date.
    Unless you have a good reason to do what you're doing, I agree with the normalization suggestion. As I see it, a contract is an entity that has attributes (characteristics) and you shouldn't repeat records of an entity. Rather than have a record for each renewal, why not just a renewal date? I see nothing in the fields that suggests there is a reason to have a separate set of records that would make them unique (such as who handled the renewal).

    Assuming the renewal field is how many days before getting a reminder, then what about

    ContractIDpk ContractNum StartDate EndDate RenewDate RenewDays
    1 ABC123 4/01/19 3/31/20 30

    where RenewDate is Null because it was never renewed. When it gets renewed, you update that field each time.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Working with contracts through Access
    By fleutius in forum Access
    Replies: 4
    Last Post: 09-23-2014, 08:39 AM
  2. Contracts to be fulfilled
    By Alex Motilal in forum Queries
    Replies: 6
    Last Post: 11-27-2012, 03:07 PM
  3. Help needed on Contracts Portfolio database
    By futurecoder in forum Database Design
    Replies: 4
    Last Post: 04-04-2011, 09:08 AM
  4. contracts query
    By ntd1 in forum Queries
    Replies: 5
    Last Post: 02-23-2009, 03: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