Results 1 to 4 of 4
  1. #1
    Pennguin is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2020
    Location
    Pennsylvania
    Posts
    11

    Displaying and updating calculating fields in multiple tables

    I have two tables active. The first is a time sheet table. EmployeeName is my relationship connector for both tables and Primary Key. The Time Sheet has a date field, time in field, time out field, and a CALCULATE hours field. {Expression as: ([Time_Out]-[Time_In])*24}

    The second table is a subform of my personnel form. It contains all the jobs available that the employee can work. I want to display the totals of all time sheets in the subform adjacent to the job they performed. I want this field to continuously add the hours (from the CALCULATED hours field from the time sheet table) as new time sheets are submitted to that employee.

    How can this be accomplished?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Calculations should not normally be saved in a table. Usually better to use a calculated field the query used as the forms record source
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Pennguin is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2020
    Location
    Pennsylvania
    Posts
    11
    I understand what you are saying, Im still lost. I can convert my (table 1) time sheet table to a query. But that really doesnt help resolve my issue. I added some picture that may help you understand, I found that be visualizing what is needed sometimes helps. Schedule Table is the table view of the Time Sheet. It shows the calculation field and the expression that goes with it. Apparently this is something new within 2019. Table 1 is the form view of Schedule Table, I need the Job Time hours to automatically add together as the jobs accrual, then for those totals to be displayed within table 2, which is the form view of the Employee Main page. Click image for larger version. 

Name:	schedule table.jpg 
Views:	8 
Size:	194.8 KB 
ID:	43735Click image for larger version. 

Name:	table 1.jpg 
Views:	7 
Size:	152.4 KB 
ID:	43736Click image for larger version. 

Name:	Table 2.jpg 
Views:	7 
Size:	210.6 KB 
ID:	43737
    A total field will need to be added to the query once it has been created, but what is the equation that will tell the query to add the fields the new data to the old data and not overwrite the existing data? In other words the field will be blank (0 hours) until the first entry is made, (example 4.0 hours), then the next entry of hours (ex: 6.0 hours) is made. How will the query know to add the numbers together as opposed to writing over the existing numbers? The end result (from the example above) should be 10.0 hours.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I need the Job Time hours to automatically add together as the jobs accrual, then for those totals to be displayed within table
    a. this is not possible with a table calculated field
    b. to do it you would need to run a query to add up the previous value and the current value and store in the current record. This is doable but not recommended because...
    c. this can then lead to complications if the data changes. for example a previous record is corrected/changed/deleted or a new one added where the datetime predates the latest datetime
    d. better to use a query to sum the times as and when required and not store in the table
    e. your current jobtimehours calculation is at risk of being wrong if the end time is in a different day to the start time
    f. you appear to require two aggregated time values, one per employee/job (per your employee form) and one per job (per your description)

    In summary, you need to rethink what you are trying to achieve and how to do it.

    suggest
    a) include the date as part of your time value
    b) calculate the jobtimehours as and when required in a query
    c) use an aggregate query to get the 'total hours to date' value.

    Other comments.
    You need to include a PK in your timesheet table
    your employees appear to be identified with a number, but you are using a text field for the FK

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

Similar Threads

  1. Replies: 20
    Last Post: 01-24-2021, 05:49 AM
  2. Replies: 1
    Last Post: 09-27-2013, 09:44 AM
  3. Replies: 3
    Last Post: 04-24-2013, 10:44 AM
  4. Replies: 3
    Last Post: 03-12-2013, 11:47 AM
  5. Replies: 9
    Last Post: 09-26-2011, 09:16 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