Updating in Access 2003

[vishan_rana]

Dear All,


I want to update <0 data as 0 in many column at once mean in 1 query

Data is like

<table bgcolor="#ffffff" border="1" cellspacing="0"><caption></caption> <thead> <tr> <th bgcolor="#c0c0c0">DECR_01_Mar</th> <th bgcolor="#c0c0c0">DECR_02_Mar</th> <th bgcolor="#c0c0c0">DECR_03_Mar</th> <th bgcolor="#c0c0c0">DECR_04_Mar</th> </tr> </thead> <tbody> <tr valign="TOP"> <td align="RIGHT">0</td> <td align="RIGHT">0</td> <td align="RIGHT">0</td> <td align="RIGHT">0</td> </tr> <tr valign="TOP"> <td align="RIGHT">-2
</td> <td align="RIGHT">0</td> <td align="RIGHT">0</td> <td align="RIGHT">0</td> </tr> <tr valign="TOP"> <td align="RIGHT">0</td> <td align="RIGHT">-15
</td> <td align="RIGHT"></td> <td align="RIGHT">0</td> </tr> <tr valign="TOP"> <td align="RIGHT">
</td> <td align="RIGHT">
</td> <td align="RIGHT">
</td> <td align="RIGHT">
</td> </tr> <tr valign="TOP"> <td align="RIGHT">
</td> <td align="RIGHT">
</td> <td align="RIGHT">
</td> <td align="RIGHT">
</td> </tr> <tr valign="TOP"> <td align="RIGHT">0</td> <td align="RIGHT">0</td> <td align="RIGHT">0</td> <td align="RIGHT">0</td> </tr> <tr valign="TOP"> <td align="RIGHT">0</td> <td align="RIGHT">0</td> <td align="RIGHT">-100</td> <td align="RIGHT">0</td> </tr> <tr valign="TOP"> <td align="RIGHT">0</td> <td align="RIGHT">0</td> <td align="RIGHT">0</td> <td align="RIGHT">0</td> </tr> <tr valign="TOP"> <td align="RIGHT">-5
</td> <td align="RIGHT">0</td> <td align="RIGHT">0</td> <td align="RIGHT">-1</td> </tr> </tbody> <tfoot></tfoot> </table>
Pls help in this

 

[Joe4]

You can do this with calculated fields in a query. It won't update the values in the underlying tables, but most of the time, that is not really necessary (as you can work with a query the same way you would a table).

So the calculated fields may look something like:
NewDECR_01_Mar:IIF([DECR_01_Mar]<0,0,[DECR_01_Mar])

You would have a calculation like this for each field you would want to apply this to.

 

[guerillaunit]

You can do what Joe suggested and then use a make table query to construct a new version of your original table. Once you make the table, you can delete the old table and rename the new table as the original table

 

[Joe4]

You can do what Joe suggested and then use a make table query to construct a new version of your original table. Once you make the table, you can delete the old table and rename the new table as the original table

You certainly can, though it may not be necessary. It really depends on the users wants and needs.

Most of the time there is no need to really update the original data permanently if you can get what you need through a query, and actually sometimes it can actually be "bad" to permanently update the table (or create a new table), as it could eliminate the dynamic nature of the database and undermine data integrity.

 

[guerillaunit]

An additional query on top of the table will work in some instances. But if the original table has a large # of records, an additional query can really slow down any extra calculations you want to do on top of it

 

[Joe4]

An additional query on top of the table will work in some instances. But if the original table has a large # of records, an additional query can really slow down any extra calculations you want to do on top of it

Good point. Sometimes it is necessary or makes sense to do so. The point I am trying to make is to fully understand the implications of doing so.

I have seen people try to store calculations in a table, when the calculations are simply of a function of other fields within that table (or another). That violates data normalization rules and can undermine data integrity.

For example, let's say that they have fields A and B in a table and want to store their sum as field C in the same table (i.e. A=1, B=2, so they store 3 in field C). Now, at some point down the road, the value of A is changed to 4. Unless they re-do their calculations and update the table again, they will have data integtiry issues, as 4+2<>3.

BTW, I am sure that you (guerillaunit) already know all this, I am posting it for the benefit of the poster and others.

 

[vishan_rana]

thanks Joe & guerillaunit

thankx for your great suggestions...
bt 1 more question is, Is there any option to do same as questioned first?

 

[Joe4]

bt 1 more question is, Is there any option to do same as questioned first?

Do you mean do it all in one query? See my first reply.