Results 1 to 11 of 11
  1. #1
    sarah.s is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    17

    Calculated Field


    Hi guys,

    I am trying to create a calculated field based on numerous data in a field. I have a field entitled assessments and I need to link their frequency to be completed in days to them i.e. 'ascribe' needs to be completed every 182 days 'Assembly practical' needs to be completed every 365 days and so on.

    I have this in a separate assessment table. I also have an assessment history table detailing all of the assessments that have already been completed by the employees. I have managed to create a calculated field in that table calculating last completed, next due for to be completed, but when I add a new employee I want to enter their name, the assessment and the date they completed it and have all the other data fill itself out.

    I think I can manage the latter when I have the former worked out. I know its some form of code but I am pants at writing them (don't even know where to start)

    Please help! Thanks!

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Do not store calculated fields in a table, create a standard query which you use each time you need to view this information.

    Could you provide the math statement of what your calculation is? It sounds like it is dependent on another field, such as ascribe = 182? If so, that value is not calculated but will stored in the table with 'ascribe'.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Echoing aytee111,

    I have managed to create a calculated field in that table
    it is considered "bad" to use calculated FIELDS in tables. Calculations should be done (if possible) in queries or in UDF's.

    Would you describe how you would manually calculate the dates for 'ascribe' and 'Assembly practical'....

  4. #4
    sarah.s is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    17
    This problem is no longer relevant as I have been informed that all assessment have the same renewal time! However I do have an issue with another part of the database if you wouldn't mind helping with that?!

    I am trying to create 2 forms. The first for adding an assessment an employee has not completed before to the database, and the second for updating the date which the employee last completed an assessment they have done before (so is already on the system) I seem to have accomplished the 1st form but I can't seem to crack the second.

    Below is my table:
    Staff Member Assessment X Completed Last Completed Due
    John Doe Assessment A 1 23/09/2015 22/09/2016
    John Doe Assessment B 2 09/03/2016 09/03/2017
    John Doe Assessment C 3 30/03/2016 30/03/2017
    Jane Doe Assessment D 2 11/11/2015 10/11/2016
    Jane Doe Assessment E 2 21/04/2016 21/04/2017
    Jane Doe Assessment F 2 08/06/2016 08/06/2017

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    How do you see the date update happening? What will trigger this, does the employee make changes to an existing assessment thru a form or what?

  6. #6
    sarah.s is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    17
    Sorry for the late reply, I only work Tuesday, Wednesday, Thursday and only have Tuesday allocated to me to work on this darned database, though I do try and work on it whenever I get a spare moment.

    Yes, the data will be put in through a form by the employee. As I said above, I want one form to add a new assessment they have done for the 1st time and one form to find an assessment they have done before and update the date they last did it i.e the last completed column would be the only one being changed as I have set up a calculated field to auto update the due column.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I don't know how you have the form designed, but you can/could have the form default view property set to "Single Form View" and have data entry property set to NO.

    When you click the "ADD DATA" button, open the form with the data entry property set to YES.
    Otherwise open the form in EDIT mode.

    The code would be something like:
    Code:
    Private Sub btnOpenFormAdd_Click()
        DoCmd.OpenForm "YourFormName", acNormal, , , acFormAdd
    End Sub
    
    Private Sub btnOpenFormEdit_Click()
        DoCmd.OpenForm "YourFormName", acNormal, , , acFormEdit
    End Sub
    One form to manage, not two......

  8. #8
    sarah.s is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    17
    I've managed to create the form to add the new assessments. Now I just need a form to look up the assessment by employee and assessment. The issue comes form there being multiple records under the same assessment and multiple records under the same employee.

    I want to create a combo box that includes the employee and the assessment so I find the ONE corresponding record, but when I do the drop down box just has numbers in place of the employees names and assessment titles

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can you post your dB?

  10. #10
    sarah.s is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    17
    Its not letting me upload the DB.

    Sorry I know you must ne getting very fed up with me. I'm very fed up myself!

    If I select 2 fields to be included in the combo box (i.e the employee and the assessment to find one specific record that fills the 'x completed' and 'last completed' with the relevant information ready to be edited) it doesn't list:

    Jane doe assessment one
    Jane doe assessment two
    John Doe assessment one
    instead it comes out like this:

    1 9
    1 11
    2 9

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    To upload your dB, do a "Compact & Repair", then Zip it. The max zip file size is 2 mb.

    What is the SQL of the combo box ROW SOURCE?

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

Similar Threads

  1. Replies: 1
    Last Post: 02-03-2016, 06:54 PM
  2. Replies: 8
    Last Post: 01-06-2016, 02:52 PM
  3. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  4. Replies: 2
    Last Post: 12-15-2013, 02:29 AM
  5. Replies: 3
    Last Post: 02-13-2013, 10:15 AM

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