Solved Update Query Updating all Columns Except for 1

[AngelSpeaks]

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:

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:

 

[theDBguy]

Could there be any null values in your table(s)?

 

[arnelgp]

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]

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.

 

[AngelSpeaks]

Could there be any null values in your table(s)?

The rates aren;t null, but the column that I'm trying to do the calculation on, is null.

 

[arnelgp]

tblPWBenefits.[Fringe Total] will be Null or Zero, until you Update it (see your SQL string).

 

[theDBguy]

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]

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]

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]

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]

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]

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]

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.