Results 1 to 4 of 4
  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    Best Practice To Store Which Datatype?


    My database project tracks monthly progress invoices and payments for construction projects, "jobs".

    A job has dozens of items, or tasks, to bill for each month. An item has a total dollar value, if x% of an item is completed in a month we can bill for x% of the item's value for that month.

    My question: would it be best practice to store progress on an item as % (float) and just calculate dollar values in the invoice (reports), OR since we're dealing with currency and access is so good with currency, store my progress as currency dollar values?

    I had assumed just working with currency data types entirely would minimize potential for issues with rounding and that kind of thing, but now that I thought about it for a moment I'm wondering if my concerns we're even an issue. The current spreadsheet that's actually used to track these values, the spreadsheet(s) I'm trying to replace with db, tracks them as percentages...

    I realize as a general rule you don't store calculated values in a field, but the circumstances feel different here.... I suppose it's not a calculated value that would ever change once it's recorded (or at least not supposed to).

    I can think of a few pros and cons for storing progress value as either pct or currency. But I'd like to know if anyone's dealt with a similar situation and has thoughts?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How many decimal places do you think you need to measure % complete?
    How do you define (level of precision) sub tasks/work items? (duration/effort?)

    Many projects have a hold back amount until final acceptance, it may apply.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by orange View Post
    How many decimal places do you think you need to measure % complete?
    Typically % complete would be input in whole numbers, that is to say something like just 25%, 90% etc. BUT sometimes we need to get specific in the spreadsheet and bill something like 13,500/23,075 = 58.504875406......%

    How do you define (level of precision) sub tasks/work items? (duration/effort?)
    I'm not sure I understand the question. An item might be stairs, if we installed stairs on 10 out of 30 floors we could bill for 33% of that item.

    Many projects have a hold back amount until final acceptance, it may apply.
    Yes, we call it retainage around here. Typically 10% but can be variable. I'm accounting for that in my design.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I put together this spreadsheet to try and illustrate exactly what it is I have to do, and my question. The following would be repeated for every billable item in the job.
    Click image for larger version. 

Name:	7.png 
Views:	16 
Size:	52.4 KB 
ID:	30095

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

Similar Threads

  1. Replies: 5
    Last Post: 07-15-2016, 01:25 PM
  2. Replies: 2
    Last Post: 07-13-2016, 08:28 AM
  3. Replies: 2
    Last Post: 05-07-2015, 09:50 PM
  4. Replies: 9
    Last Post: 04-01-2014, 05:06 PM
  5. Binary datatype
    By huBelial in forum Access
    Replies: 1
    Last Post: 04-04-2011, 02:02 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