Update All Null Values to 0 in a Table

[ertweety]

I have a table that contains a list of employees and their working hours by month. At the end of each month a new column gets added. I would like to have a query update all the null values in the table without having to list each individual column name, since these will change.

I can't do it in previous steps because a crosstab pulls the data and a Nz function doesn't work. Off the crosstab, I create a make table. In that table I made is where I need to reset the nulls to 0's. Help!

 

[jzwp22]

A query needs a specific field name in order to work, so you cannot do what you want with a query. The only way I know of is with Visual Basic for Application code.

But, you have a bigger issue. By adding a new field (column) every month, you are creating an un-normalized table structure which will cause you all kinds of issues when you start doing forms, reports and queries.

Since an employee will have many working hour entries, that describes a one-to-many relationship which, by normalization rules, requires a separate but related table

tblEmployees
-pkEmployeeID primary key, autonumber
-txtFName
-txtLName


tblEmployeeTime
-pkEmpTimeID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees, long number integer datatype field
-dtePeriodEnd (period ending date, I assume the last day of the month)
-spHoursWorked (single precision number field, I assume-you can change as needed)


You can set the default value for the hours worked field to zero, that way any time you create a new record the value will be zero.

 

[gemma-the-husky]

jz is correct - you sdhouldnt need to add new columns to the table.

you should be adding rows to a different table

if you need to add columns, something is wrong.