Results 1 to 13 of 13
  1. #1
    KGlynn is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Location
    UK
    Posts
    7

    Arrow Can't use calculated field as a table join - so how else can I do it

    I have one table that is a time card.

    The total hours worked in any day are collected in there and I also want to calculate how many extra hours are worked in any day.
    How many extra hours is calculated by deducting a set number of hours from the total number of hours worked in any day.
    The tricky bit is that the set number of hours to deduct varies depending on the day of the week.

    Let me show the difference in Set Hours in a table

    Day of Week Set Hours
    Mon 07:30
    Tues 07:30
    Weds 07:30
    Thur 07:30
    Fri 07:00
    Sat 07:00
    Sun 07:00

    So depending on the day of the week, the amount to be deducted to calculate extra hours is different, for example, if the hours worked every day was 7h 30m, then the extra hours would vary like this
    Day of Week Hours Worked Set Hours (as in table above) Extra Time (h:mm)
    Mon 7:30 7:30 0:00
    Tue 7:30 7:30 0:00
    Wed 7:30 7:30 0:00
    Thur 7:30 7:30 0:00
    Fri 7:30 7:00 0:30
    Sat 7:30 0:00 7:30
    Sun 7:30 0:00 7:30
    I want this extra time to be calculated in the same place as the employee fills out their daily time card.
    I have created two tables, one for the daily time card input, and one to hold the set hours.
    The only relationship between the two tables is the 'Day of Week' . In the time card table, this is a calculated field using the input date, so I can't join the two tables using this. I have created a query which joins the two sets of data (I think), but I cannot work out how to then put this query back into the Time Card table so that it can shows the extra time for each row in the Time Card table.



    These are the two table fields:

    Time Card
    ---------------
    Date: dd.mm.yyyy
    Start Time hh:mm
    End Time hh:mm
    Total Hours: End Time - Start Time (hh:mm)
    Weekday: WeekdayName([Day of Week],True,2)
    Day of Week: Weekday([Date],2)

    Set Hours
    -----------
    Day of Week : Short text (matches to values calculated in Weekday column above
    Hours: hh:mm

    Query
    ----------
    SELECT [Set Hour].Hours
    FROM [Set Hours] INNER JOIN [Time Card] ON [Set Hours].[Day of Week] = [Time Card].[Week Day];

    How do I add the result of this query into a field in the Time Card table?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    When you say that the field is a "calculated" field, do you mean that is the value or the data type? If the latter, change it to the same data type as on the day table and do the calculation when you store the record to the time table.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is your table name [Set Hour] or [Set Hours]?
    One of these must be incorrect in your query SQL.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Set Hours
    -----------
    Day of Week : Short text (matches to values calculated in Weekday column above
    Hours: hh:mm
    You might also have problems because 7:30 (with a colon) is a TIME, whereas 7.5 is a duration: 7 hrs and 30 minutes.

    Also, I think the top table should be 0 for Saturday and Sunday, not 7 hrs and 30 minutes (if Sat & Sun are not normal work days).

  5. #5
    KGlynn is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Location
    UK
    Posts
    7
    Quote Originally Posted by aytee111 View Post
    When you say that the field is a "calculated" field, do you mean that is the value or the data type? If the latter, change it to the same data type as on the day table and do the calculation when you store the record to the time table.
    It is the value that is calculated, I think. I'm not sure what you mean by data type.
    The data format is 'Short Text' for both fields in both tables.

  6. #6
    KGlynn is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Location
    UK
    Posts
    7
    Quote Originally Posted by orange View Post
    Is your table name [Set Hour] or [Set Hours]?
    One of these must be incorrect in your query SQL.
    Oh it is Set Hours (and it's a Query!). That's just my bad typing..

  7. #7
    KGlynn is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Location
    UK
    Posts
    7
    Quote Originally Posted by orange View Post
    Is your table name [Set Hour] or [Set Hours]?
    One of these must be incorrect in your query SQL.
    Sorry replied in the wrong place

  8. #8
    KGlynn is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Location
    UK
    Posts
    7
    Quote Originally Posted by ssanfu View Post
    You might also have problems because 7:30 (with a colon) is a TIME, whereas 7.5 is a duration: 7 hrs and 30 minutes.

    Also, I think the top table should be 0 for Saturday and Sunday, not 7 hrs and 30 minutes (if Sat & Sun are not normal work days).
    Ok thanks, I'll bear than in mind but that's not showing up as a problem at the moment.

  9. #9
    KGlynn is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Location
    UK
    Posts
    7
    The problem I am actually seeing is that I cannot simply reference the correct 'Set Hour' by joining the tables because there is an error when I try and join the two tables on [TimeCard].[Weekday] and [Set Hours].[Day of Week]. The error says a relationship can't be made using a calculated field.

    So i have created the Set Hours query to look this up (rather than doing a table join), but now I can't work out how to put that query value back into the Time Card table as a row value.

    Have I structured my data in the best way, and if so, how do I put a query value into a table?

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    How does the query know that the field is a calculated field? If the data type is short text, that is good. However, in table design, is there a calculation attached to the field? If so, remove it, calculate it when adding the record to the table.

  11. #11
    KGlynn is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Location
    UK
    Posts
    7
    The Query does not care if the field is calculated. It is when I try and join the tables that the error comes up. I have worked out how to update the Time Card table now - eventually. Now I'm trying to work out how to a) have it run automatically when a new row is added, and b) how to show negative hours properly so that they can be summed

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    b) how to show negative hours properly so that they can be summed
    Now we get into the difference between times (7:30 am) and duration (7.5 hrs). BTDT


    In table design, using Look up FIELDS, Multi-Value fields and Calculated fields is a really BAD idea (IHMO). They cause more trouble than they are worth...

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I had no problem building a query that linked tables on a Calculated type field. What I do have issue with is using WeekdayName() function as shown in the post. Had to do:

    WeekdayName(Weekday([StartDate]), True)

    ExtraTime should be calculated as decimal hour, not saved as the clock time format shown.

    It is not really necessary to save the ExtraTime calculation - calculate when needed. If anything needs to be saved, I suggest it be the SetHours value in effect at the time TimeCard record is created. And do this only if there is possibility the SetHours value could change over time.


    If a table join really is not possible, then a DLookup() could be used.


    Date is a reserved word. Should avoid reserved words as names. Also recommend no spaces or punctuation/special characters (underscore only exception).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-27-2016, 10:24 AM
  2. Replies: 1
    Last Post: 07-24-2014, 08:35 PM
  3. Replies: 1
    Last Post: 04-21-2013, 03:20 PM
  4. Replies: 3
    Last Post: 02-13-2013, 10:15 AM
  5. Replies: 2
    Last Post: 10-24-2012, 02:53 PM

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