Null values

[Gordony]

Hi,
I'm new to the Access database query language & need some help regarding
"null values" & converting them into another record within the Access
database. The question & background is:-
I have extracted some payroll data from another database (excel) & have
copied it into an Access table (payroll table). One of the fields in the
table, "Tasks" contains records of payroll tasks & in that field there are a
number of null records. What I want to do is have a new updated "Task" field
within the table which takes all null records & updates them with another
record from another record field within the table & keeping all the remaining
"Tasks" as well. As an example, the table is set out below as a before &
after scenario:
Before:
Field Field
Task Location
Fulfill
Backlist Picking
Returns
Perm Stk Stock

After:
Task Location
Fulfill Fulfill
Backlist Backlist
Returns Returns
Perm Stk Stock

Cheers
Gordon

 

[John Spencer]

I think you have an error in your sample updated data. You changed Backlist
Picking to Backlist Backlist and my understanding is that you would NOT
change that record.

BACKUP your data and then try an update query

UPDATE Payroll
SET Payroll.Task = [Payroll].[Location]
WHERE Payroll.Task Is Null

IF this is not what you want to do then I have misunderstood your question.
I am puzzled by "...takes null records & updates them with another record
from another record field within the table". Does that mean you want to get
the update value from a field in another record or do you want to get the
update value from another field in the same record? The update query above
does the latter. IF you want to do the former, then you need some way to
identify which record and field would be used to update each record that
contains a null value.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Gordony via AccessMonster.com]

Hi,
I'm new to the Access database query language & need some help regarding
"null values" & converting them into another record within the Access
database. The question & background is:-
I have extracted some payroll data from another database (excel) & have
copied it into an Access table (payroll table). One of the fields in the
table, "Tasks" contains records of payroll tasks & in that field there are a
number of null records. What I want to do is have a new updated "Task" field
within the table which takes all null records & updates them with another
record from another record field within the table & keeping all the remaining
"Tasks" as well. As an example, the table is set out below as a before &
after scenario:
Before:
Field Field
Task Location
Fulfill
Backlist Picking
Returns
Perm Stk Stock

After:
Task Location
Fulfill Fulfill
Backlist Backlist
Returns Returns
Perm Stk Stock

Cheers
Gordon

Sorry John - "After" should read:
Task Location

Fulfill Fulfill
Backlist Picking
Returns Returns
Perm Stk Stock

What I want to do is, in the before scenario - is wherever there is a null
value/data in the Task field go to the location field & copy that value/data
to the task field in the after scenario.
Hope that makes sense.