Results 1 to 5 of 5
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Fuzzy Dates

    I have taken a break from my Lineage DB, to work on a Ships DB which is about WW2 ships. War has never been about paper work so some dates are fuzzy ie Mid month, /4..8/1941, over the winter, etc.
    Now looking round at this there are couple of options,

    1. Use a Date and an Integer to give a plus date range.
    2. Use a Date and don’t worry about it.
    3. Use 3 text fields, Year,Month,Day.
    4. The one YOU love.

    I know most of this will be based on how I will use the data, but I wondering if anyone Loves/Hates any of these approaches?




    Case 3 means the date needs to computed to use any date functions but gives the user clear Idea of the true accuracies. Converting it to a date could be tricky depending on what was entered (and controlled).


    Case 1 is hard for humans to read eg 2019 05 16 +45 . It also means using a date that the earliest possible, but unlikely. This makes the queries and date calculation questionable.


    Case 2 means putting erroneous data in with no markers/flags. It is the easiest one to do, but I do want to put the DB out out there.


    A version of 1 and 3 means using a +- number, but the best would be two different numbers. Using a open and close dates will not work for me as I do like “this until the next record” method for insertion and fine tuning.
    As I said any thoughts about how to handle FUZZY DATES ?


  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    I'd go for date and the number days for possible error (minimum hassle with maximum accuracy). When you know exact time interval, then enter middle date, and half of interval (rounded up to whole day) as possible error value. When time is somewhere in month, enter e.g. 15. of month as date and 15 as error, etc. Of-course when you know exact date, the value for error will be 0. You can set default value for error control (NB! For control!) in form to 0 - so user has enter possible error only when it is >0.

  3. #3
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Quote Originally Posted by ArviLaanemets View Post
    When you know exact time interval, then enter middle date, and half of interval (rounded up to whole day) as possible error value. When time is somewhere in month, enter e.g. 15. of month as date and 15 as error, etc.
    Thanks Arvil. Question on the secondary part, how do flag that from the original case (1)? I’m developing a like for the non null camp so I agree with the zero value.

    Then again the date math and searches will be off. Math by about 1/2 of the error, and searches if the date ranges are not encompassing the date but with in the error value. Any hints?
    Thanks for answering
    Neil

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Western_Neil View Post
    Thanks Arvil. Question on the secondary part, how do flag that from the original case (1)? I’m developing a like for the non null camp so I agree with the zero value.
    Essentialy it's same. I only added some example rules which you can apply when you want (i.e. like in case time interval is ... set date as ... and possible error as ...). It is useful to avoid situations where you'll later e.g. wonder, why you entered just this date.

    Quote Originally Posted by Western_Neil View Post
    Then again the date math and searches will be off. Math by about 1/2 of the error, and searches if the date ranges are not encompassing the date but with in the error value. Any hints?
    For simple search you always can have condition like
    Code:
    ... WHERE YourDate BETWEEN YourDate - PossibleError AND YourDate + PossibleError
    When you want to search in specific time interval, it will get trickier - it may be you cant take cases where error diapason for certain date doesn't fully belong into search interval fully into account. Calculating the modifier when you assume even distribution is difficult enough, and I hate the thought to handle e.g. cases with normal distribution.

  5. #5
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Thanks again Arvil
    I am confused, are you talking case 1 or case 1&3 (call it 5)? I think you are talking Case 5 and for 31 days would be cause me use -15 +16 or +-15 or 16. So error value would be 15 or 31? Any way I will have to have a rule to handle it.
    Thanks again for this, it makes me feel more confident about this choice.
    Neil

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

Similar Threads

  1. fuzzy match in Access
    By Alaska1 in forum Access
    Replies: 14
    Last Post: 04-22-2019, 04:10 AM
  2. Fuzzy mapping_Instr Query
    By google in forum Access
    Replies: 7
    Last Post: 06-27-2016, 05:47 AM
  3. report exported as pdf is fuzzy in Publisher
    By louise in forum Import/Export Data
    Replies: 2
    Last Post: 12-05-2015, 09:25 AM
  4. Fuzzy lookup on field?
    By Poida3934 in forum Forms
    Replies: 2
    Last Post: 10-10-2013, 09:10 PM
  5. Replies: 10
    Last Post: 04-18-2012, 10:55 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