Results 1 to 3 of 3
  1. #1
    parkingguy is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    1

    Calculating/Storing Time Differences & Reference Result to Table

    I have tried a few different ways of setting this up and I cannot figure it out. I have a small database with 3 tables:

    tblTicketInfo
    - TransID
    - TicketNumber
    - TicketType
    - EntryDate
    - EntryTime
    - ExitDate
    - ExitTime


    - Duration
    - Rate

    tblTicketType
    - TypeID
    - TicketType

    tblFeeTable
    - IncrementID
    - StartTime
    - EndTime
    - Rate

    I then have a form where the user enters the EntryDate and EntryTime for the ticket. The ExitDate and ExitTime are auto-populated with the current date and time. I need this form to:
    - Calculate the difference between the EntryDate+EntryTime and the ExitDate+ExitTime
    - Store that value in the Duration field & display it on the form
    - Update the Duration any time the EntryDate, EntryTime, ExitDate, or ExitTime is changed
    - Use that Duration to reference the tblFeeTable; If the Duration is between a StartTime and EndTime, give me the Rate and display it on the form
    - Store the Rate in tblTicketInfo

    I've thought about creating another table to store durations and rates, using queries to do the calculations, using calculated fields, but I just can't get everything to update properly. Does anyone have any suggestions?

    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont need 2 fields for DATE, and TIME.
    a single date field holds both and you NEED both to calc the differnce.

    on a form you CAN split the 1 field into 2 text boxes to enter date in one, and time in the other, but they both feed the same field.
    so you can either add an extra field to combine the 2 fields,
    or combine them in a query in order to calc difference...
    =DateDiff("h",[dateFldStart],[DateFldEnd])

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Duration is a calculation. Therefore whether it should be stored, rather than calculated when needed, deserves serious forethought. The primary consideration is the user interface and managing changes of entry data.

    The same may be true for Rate if it is a calculation based upon Duration.

    A calculation can occur & display any where one needs it to display and only the designer knows that it isn't actually stored. But a stored field (based on a calculation) must have that calculation triggered at the time of data entry or data change - and depending on the user interface/experience it may or may not be the best approach.

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

Similar Threads

  1. Replies: 3
    Last Post: 04-03-2014, 10:43 AM
  2. Calculating Multiple Differences
    By dr4ke1990 in forum Queries
    Replies: 58
    Last Post: 01-02-2014, 12:34 PM
  3. Replies: 1
    Last Post: 03-14-2013, 10:16 AM
  4. Replies: 1
    Last Post: 09-07-2011, 02:42 AM
  5. Replies: 3
    Last Post: 07-12-2010, 01:38 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