Update Query Producing Zero Values

[fperri]

I have a field in my main table called "CHANGE". Each week before we update the data we create a backup of the table and adjust the CHANGE field for all records to zero. I have an update query where I am updating the CHANGE field with the difference between the main table's RATE field and the backup table's RATE field. For some reason, it is assigning a zero value to some of the changes instead of the actual change, but then some of them are pulling through. At first I thought that it was just with the rates that decreased, but both positive and negative numbers are comming through and/or being set to zero. I then thought that it was because of the data type of the field so I set the field to a Double, Fixed, 3 Decimal Places, and even changed the properties of the calculated field in the update query. I also read somewhere online that if the field is null that it can mess up an update query so I adjusted all the CHANGE fields to zero before running the update query and I get the same results.

Any idea why it would be assigning a value of zero for some changes but not for others?

[fperri]

If I do just a regular select query I get the correct change amounts, its when I turn it into an update query where it sets some of the change amounts to zero.

[nico5038]

Hmm, logically it looks a bit strange to move a value to a backup table, initialize it in the original table to zero and then update it with the difference. Looks to me that the result will be the same (original) value as a value -/- 0 will de that value again...

So basically I wonder what you expect to happen from this algoritm..

Nic;o)

[fperri]

Hmm, logically it looks a bit strange to move a value to a backup table, initialize it in the original table to zero and then update it with the difference. Looks to me that the result will be the same (original) value as a value -/- 0 will de that value again...

So basically I wonder what you expect to happen from this algoritm..

Nic;o)

Getting the difference between the current and backup table's RATE column and putting it in the current table's CHANGE column. If they are the same it assigns a zero and if there is a difference it will show the change amount.

I ended up scrapping this approach and created a form instead for the user's to enter their changes into. Then using the form's before and after update events I was able to create a log table that holds these change values. Then I created a report from this log. It works nicely.

~ Franccesca