Solved Update Query Updating all Columns Except for 1 (1 Viewer)

AngelSpeaks

Active member
Local time
Today, 14:00
Joined
Oct 21, 2021
Messages
406
I just know there has to be a simple solution. I'm using Access 2013. I have an update query that joins two tables, tblPWTable and tblPWBenefits. tblPWTable contains various rates of pay and tblPWBenefits has the hours information and calculated fields for vacation, training, etc.

One field [Gross Earned] will not calculate, even if I hardcode an amount in that column, it won't appear. It's defined as NUMBER, DOUBLE, FIXED, 2 decimals (as are all my other columns). In the dataset view, the column is blank (I don't know if that's helpful, as the other columns have zeros).

The calculation for Update To is:
Round([tblPWBenefits]![OTHrs]*([tblPWTable]![Base Wage])*1.5,2)+Round([tblPWBenefits]![Regular]*[tblPWTable]![Base Wage],2)

To simplify things, I've copied the query and removed a lot of the columns. Here is the sql:

SQL:
UPDATE tblPWTable INNER JOIN tblPWBenefits ON tblPWTable.County = tblPWBenefits.County SET tblPWBenefits.[Pension Earned] = [Total Hrs]*[tblPWTable]![Pension], tblPWBenefits.[Fringe Total] = [Total Hrs]*[tblPWTable]![Total Fringe Benefits], tblPWBenefits.[Other Earned] = [Total Hrs]*[tblPWTable]![Other Fringe Benefit], tblPWBenefits.[Base Wage] = [tblPWTable]![Base Wage], tblPWBenefits.[OT Wage] = Round([tblPWTable]![Base Wage]*1.5,2), tblPWBenefits.[DT Wage] = Round([tblPWTable]![Base Wage]*2,2), t[B]blPWBenefits.[Gross Earned] = Round([tblPWBenefits]![OTHrs]*([tblPWTable]![Base Wage])*1.5,2)+Round([tblPWBenefits]![Regular]*[tblPWTable]![Base Wage],2)[/B]
WHERE (((tblPWBenefits.[Fringe Total])>0));

Thanks so much.

The query results are:

Query.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:00
Joined
Oct 29, 2018
Messages
21,358
Could there be any null values in your table(s)?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:00
Joined
May 7, 2009
Messages
19,169
only comment.
you are computing and updating this:

tblPWBenefits.[Fringe Total]

yet you are using it as Criteria?
can you use the base calculation as the criteria?

... WHERE ([Total Hrs]*[tblPWTable]![Total Fringe Benefits]) > 0
 

AngelSpeaks

Active member
Local time
Today, 14:00
Joined
Oct 21, 2021
Messages
406
only comment.
you are computing and updating this:

tblPWBenefits.[Fringe Total]

yet you are using it as Criteria?
can you use the base calculation as the criteria?

... WHERE ([Total Hrs]*[tblPWTable]![Total Fringe Benefits]) > 0
At this point of time, if the Total Fringe Benefits are zero, then I want to do the calculations (this represents new rows added). For my testing, I changed it to >0 to redo all of he records.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:00
Joined
May 7, 2009
Messages
19,169
tblPWBenefits.[Fringe Total] will be Null or Zero, until you Update it (see your SQL string).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:00
Joined
Oct 29, 2018
Messages
21,358
The rates aren;t null, but the column that I'm trying to do the calculation on, is null.
It's hard to see what's wrong from here. If you can post a sample copy of your db with test data, we might be able to help you fix it sooner.
 

AngelSpeaks

Active member
Local time
Today, 14:00
Joined
Oct 21, 2021
Messages
406
It's hard to see what's wrong from here. If you can post a sample copy of your db with test data, we might be able to help you fix it sooner.
That will take awhile to recreate. I am currently using real data that's payroll related. Thanks. At least, so far, I'm not going crazy.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:00
Joined
May 7, 2009
Messages
19,169
can you use Separate query to Update the [Gross Earned]?

update yourTable Set [Gross Earned] = Nz([field1],0) + Nz([field2],0) + Nz([field3],0) Where Nz([Gross Earned], 0) = 0
 

AngelSpeaks

Active member
Local time
Today, 14:00
Joined
Oct 21, 2021
Messages
406
can you use Separate query to Update the [Gross Earned]?

update yourTable Set [Gross Earned] = Nz([field1],0) + Nz([field2],0) + Nz([field3],0) Where Nz([Gross Earned], 0) = 0
I will give it a try. Thanks
 

AngelSpeaks

Active member
Local time
Today, 14:00
Joined
Oct 21, 2021
Messages
406
can you use Separate query to Update the [Gross Earned]?

update yourTable Set [Gross Earned] = Nz([field1],0) + Nz([field2],0) + Nz([field3],0) Where Nz([Gross Earned], 0) = 0
I tried, no success
 

AngelSpeaks

Active member
Local time
Today, 14:00
Joined
Oct 21, 2021
Messages
406
OK time for an update. I've tried all kinds of crazy things. Soooo, I decided that the append query that originally added the records to the table should be changed to check if columns are null and if so, default to zero and now this query is working. thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:00
Joined
Oct 29, 2018
Messages
21,358
OK time for an update. I've tried all kinds of crazy things. Soooo, I decided that the append query that originally added the records to the table should be changed to check if columns are null and if so, default to zero and now this query is working. thanks
Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom