Results 1 to 6 of 6
  1. #1
    PimpCup is offline Novice
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Posts
    10

    What is the best practice for handling and storing spans of time in a field?

    I am hoping someone can give me some guidance on this...



    I am constructing a database that keeps track of equipment that my employer sells. Most of the equipment we sell will have a product warranty from the manufacturer, and/or a product warranty that our company itself will provide.

    I was wondering what would be the best practice for storing the lengths of the various different warranties for our various equipment in a table field. Some warranties might be specified in years, others in months, and others in days. I shouldn't require anything more granular than days.

    Should I have three number-type fields for specifying years, months, and days? Maybe then I can have a calculated field that give the total in days, and possibly another calculated field that converts it into months? I feel like with that, I would also need a lot of data validation rules to prohibit someone from entering something confusing, like 3 years and 24 months.

    I greatly appreciate any wisdom you folks here might have to share.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Each product should have it's own warranty values, not be entered manually?
    Whether you store the value in months, days is up to you.
    Use DateAdd() when needed to add that value. That gives you your expiry date.
    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
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can let the end user enter the data however you like, but from a processing point of view I would probably use Months or Days to store the data.
    Do you have a list of the current product warranty lengths, that would guide you to the best solution.

    In my head Months would be easiest as 1, 3, 6, 12, 18, 24, 36, 48 all seem very common periods, and as Gasman suggests adding that number to a sales date to give you the expiry date is super easy.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you built a data model to get your tables and relationships clarified?

  5. #5
    PimpCup is offline Novice
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Posts
    10
    Quote Originally Posted by Welshgasman View Post
    Each product should have it's own warranty values, not be entered manually?
    Whether you store the value in months, days is up to you.
    Use DateAdd() when needed to add that value. That gives you your expiry date.
    I apologize for the ambiguity in my OP.

    I have a Warranty table with a surrogate key, a productID (foreign key), a WarrantyTypeID (foreign key), and a field for warranty length. We have manufacturers' warranties as well as our own issued warranties, and a specific product could have one or more of those warranty types simultaneously. The WarrantyType table simply has an ID and a field for the type of warranty.

    The warranty start dates don't necessarily hold up consistently, so expiry dates are likely going to be a bit fuzzy. We have dates of receiving for when the products are procured, shipment dates for when the products shipped as part of a customer order, and, depending on the nature of the sale, install dates for the product. Some things we sell as stock for a customer to replace when needed, and we don't have a measure of how long those could sit in their inventory. I could feasibly use the most recent of those dates available as a start point for the warranty, and any repair request we receive we might just have to give or take maybe a month or so in determining whether a repair would be covered by the warranty.

    My concern was mostly about what form to save the warranty length as, in a way that would be helpful upon re-querying such information at a later date. For instance, 913 days (about 30 months) isn't immediately helpful, whereas 1.666666666666... years (20 months) is not exactly the most helpful either.

    However, that DateAdd() function looks a little more sophisticated than I had initially presumed it was. With it, I can add time in years, months, or days. Most important, it has ways of handling variances in the number of days in a month, leap years, and things like that already built in, which was a large part of my apprehension when it was going to come to storing something as a length of time.

    I suppose it will all just come down to a matter of preference. Thank you very much for your help.

  6. #6
    PimpCup is offline Novice
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Posts
    10
    Quote Originally Posted by orange View Post
    Have you built a data model to get your tables and relationships clarified?
    In this specific portion of my database, yes. I am comfortable with the table relationships in this area of it.

    One of my bosses has liked the progress I've made and wants to incorporate Purchase Orders with the potential for split shipments into the mix, and I might be making a new post about that if I continue to find myself still stuck with that. From searching similar subject matters here, I've seen your links to Allen Browne's "Quantity on Hand", so I know I am on the right track with that.

    As for storing the warranty lengths, I was curious as to whether there were some best practices I might want to abide by. It seems that the DateAdd() function that Welshgasman mentioned should take care of some of the peculiarities that come from converting months to days, when every month doesn't have the same number of days. I'd say I'm satisfied with that now, so thank you for the help you've been providing me here, and in past forum posts.

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

Similar Threads

  1. Replies: 1
    Last Post: 06-23-2020, 12:48 AM
  2. best practice for handling redundant data?
    By merlin777 in forum Database Design
    Replies: 3
    Last Post: 09-21-2015, 08:32 AM
  3. Storing Elapsed Time in a Field
    By andybuck86 in forum Access
    Replies: 1
    Last Post: 10-08-2011, 05:07 AM
  4. Replies: 2
    Last Post: 08-24-2011, 12:59 PM
  5. Replies: 2
    Last Post: 03-31-2010, 05:32 AM

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