Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-11-2019, 11:29 AM   #1
Benyamil
Newly Registered User
 
Join Date: Jun 2019
Posts: 2
Thanks: 2
Thanked 0 Times in 0 Posts
Benyamil is on a distinguished road
Question Update Query Changes Calculated Field Value

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?

Benyamil is offline   Reply With Quote
Old 06-11-2019, 11:57 AM   #2
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,436
Thanks: 58
Thanked 1,411 Times in 1,392 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Update Query Changes Calculated Field Value

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Benyamil (06-11-2019)
Old 06-11-2019, 12:07 PM   #3
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,451
Thanks: 0
Thanked 571 Times in 567 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Update Query Changes Calculated Field Value

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Benyamil (06-11-2019)
Old 06-11-2019, 12:33 PM   #4
Benyamil
Newly Registered User
 
Join Date: Jun 2019
Posts: 2
Thanks: 2
Thanked 0 Times in 0 Posts
Benyamil is on a distinguished road
Re: Update Query Changes Calculated Field Value

Quote:
Originally Posted by theDBguy View Post
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.
Benyamil is offline   Reply With Quote
Old 06-11-2019, 12:38 PM   #5
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,436
Thanks: 58
Thanked 1,411 Times in 1,392 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Update Query Changes Calculated Field Value

Quote:
Originally Posted by Benyamil View Post
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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-11-2019, 01:16 PM   #6
Solo712
Newly Registered User
 
Solo712's Avatar
 
Join Date: Oct 2012
Posts: 741
Thanks: 14
Thanked 126 Times in 123 Posts
Solo712 will become famous soon enough
Re: Update Query Changes Calculated Field Value

Quote:
Originally Posted by Benyamil View Post
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
__________________
Dyslexics of the world untie !
Solo712 is offline   Reply With Quote
Old 06-11-2019, 01:36 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,680
Thanks: 93
Thanked 1,703 Times in 1,576 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Update Query Changes Calculated Field Value

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-11-2019, 02:28 PM   #8
Solo712
Newly Registered User
 
Solo712's Avatar
 
Join Date: Oct 2012
Posts: 741
Thanks: 14
Thanked 126 Times in 123 Posts
Solo712 will become famous soon enough
Re: Update Query Changes Calculated Field Value

Quote:
Originally Posted by The_Doc_Man View Post
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

__________________
Dyslexics of the world untie !
Solo712 is offline   Reply With Quote
Reply

Tags
calculated field , query error , update error , update query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using calculated field in query to update table themagpiesaid Queries 2 10-08-2018 04:57 PM
Calculated field in a query/table based on a calculated field joannkt General 5 11-07-2013 11:06 AM
update calculated field Bladerunner Queries 1 02-15-2013 02:37 PM
update query for a calculated field shutzy General 1 12-30-2011 03:40 AM
Whish is faster? - Calculated field in QUERY or Calculated field in REPORT? Cosmos75 Reports 3 03-18-2003 09:26 AM




All times are GMT -8. The time now is 12:20 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World