Update Query Changes Calculated Field Value (1 Viewer)

Benyamil

New member
Local time
Today, 17:52
Joined
Jun 11, 2019
Messages
2
This one really has me stumped.

I have an Attendance Tracker that uses a query to update records on an Attendance Table.

Users enter records into the Query using a form which contains a calculated field that assigns an occurrence point value based on the infraction type and time frame.

For example:
If I call out for 8 hrs the calculated field assigns 1 point
If I’m late for 25 minutes the calculated field assigns .25 points
If I leave 3 hours early the calculated field assigns .50 points
…etc.

When users submit the completed form, an Update Query updates the occurrence point value in the Attendance Table.

This is working 95% of the time… the other 5% I encounter an error where the update query posts a DIFFERENT occurrence point value into my Attendance Table other than what’s shown in the Query’s calculated field. For example my Query’s calculated field will show an Occurrence Point value of 1 (correct) but when I run the Update Query it posts a value of .50 (incorrect) into the Attendance Table.

The problem is happening randomly. If I catch the error on a specific record, I can sometimes duplicate the issue if I delete and enter the exact same contents into a new record. Today I caught an erroneous record and was able to duplicate the issue, however when I tried to duplicate it on my backup file (containing the exact same error on the same record) the error would not duplicate and it fixed itself. I also can’t find any commonalities between the records that produce the error.

To recap, the Query’s calculated field is ALWAYS correct 100% of the time. However the Update Query is taking this result and updating the Table with a random (incorrect) number 5% of the time.

I’ve tried compacting and repairing the database, I also moved all the elements into a new database file to see if the original file was corrupt, but the error continues.

Any ideas?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:52
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to the forum. Not sure if this applies to your particular situation, but this is exactly why we always recommend not to store calculated values in tables because the result of calculating values will always be more accurate in queries than in tables. Is there any particular reason why the calculations are stored in the table if you're going to change/update them regularly anyway using a query?
 

June7

AWF VIP
Local time
Today, 13:52
Joined
Mar 9, 2014
Messages
5,423
This is why saving calculated data, especially aggregate data is usually a bad idea. Calcs can get 'out of sync' with raw data. Calculate this data when needed.
 

Benyamil

New member
Local time
Today, 17:52
Joined
Jun 11, 2019
Messages
2
Hi. Welcome to the forum. Not sure if this applies to your particular situation, but this is exactly why we always recommend not to store calculated values in tables because the result of calculating values will always be more accurate in queries than in tables. Is there any particular reason why the calculations are stored in the table if you're going to change/update them regularly anyway using a query?

That's actually a very valid point. On the original version of my database, users had to manually type in the occurrence point value because I didn't know how to make a calculated field. After I learned how to do this I didn't even think about removing the point value field from the original table.

I guess I've got some work to do! Thanks for the help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:52
Joined
Oct 29, 2018
Messages
21,358
That's actually a very valid point. On the original version of my database, users had to manually type in the occurrence point value because I didn't know how to make a calculated field. After I learned how to do this I didn't even think about removing the point value field from the original table.

I guess I've got some work to do! Thanks for the help.
Hi. If you could avoid using the calculated values in the table and able to use the calculated values from the query instead, then you should always have the accurate information to use for whatever purpose or report you need them. Good luck!
 

Solo712

Registered User.
Local time
Today, 17:52
Joined
Oct 19, 2012
Messages
828
That's actually a very valid point. On the original version of my database, users had to manually type in the occurrence point value because I didn't know how to make a calculated field. After I learned how to do this I didn't even think about removing the point value field from the original table.

I guess I've got some work to do! Thanks for the help.

Hi, one way to fix that would be to have a value list field (combobox) in the table with text for the three conditions that you named i.e. 8 hrs out, 3 hrs, and late for x minutes. Then if you need to convert the values to points for a report/query you may do that the way you constructed the calculated field.

Best,
Jiri
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:52
Joined
Feb 28, 2001
Messages
27,001
If we are talking about having a multi-value field for lookup purposes in a table, that feature is now considered discredited due to its side effects on JOIN queries. Combo boxes on forms? No problem. Combo boxes in tables? Big problem.
 

Solo712

Registered User.
Local time
Today, 17:52
Joined
Oct 19, 2012
Messages
828
If we are talking about having a multi-value field for lookup purposes in a table, that feature is now considered discredited due to its side effects on JOIN queries. Combo boxes on forms? No problem. Combo boxes in tables? Big problem.

No, I am not talking about queries in a table but a restricted value list that is being treated as a text box. Kindly read what is being proposed before commenting.

Jiri
 

Users who are viewing this thread

Top Bottom