Calculated Field

[sarah.s]

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!

[aytee111]

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'.

[ssanfu]

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'....

[sarah.s]

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

[aytee111]

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?

[sarah.s]

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.

[ssanfu]

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:

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......

[sarah.s]

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

[ssanfu]

Can you post your dB?

[sarah.s]

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

[ssanfu]

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?