We help IT Professionals succeed at work.

Access Query Fill Empty Cells

tahirih
tahirih asked
on
912 Views
Last Modified: 2012-05-06
In Table A, I want to update this table so that if Column B is empty, I will replace the value for that row with the value in Column A. If Columnb B is not empty, then the value in Column B will remain.


Example 1:

Row 1      Column A = "Dog"        Colmn B = " " (empty cell)
then this is updated to:
Row 1      Column A = "Dog"        Column B = "Dog"

Example 2;

Row 2     column A  = "Dog"         Column B = "Cat"
then this row will remain as it is since B was initially filled.

I prefer a non-SQL solution (i.w. how would I arrange this in the Query Design window using criterias and expressions.

Thanks.

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2006

Commented:
how about this

update mytable
set colb = cola
where isnull(colb) = true
CERTIFIED EXPERT
Top Expert 2006

Commented:
given your example, what is actually in colb, is it totally empty? or is there a space or something? to handle empty strings or space in it

update mytable
set colb = cola
where trim$(nz(colb,"")) = ""

CERTIFIED EXPERT
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Please offer how I can create Table C table from Table A, and leave Table A unchanged, but the new Table C will have the udpates. The following did not work:

UPDATE A INTO C SET Div = Dept
WHERE trim$(nz(Div,""))="";

Thanks.
CERTIFIED EXPERT
Top Expert 2006

Commented:
You cannot do a update when creating new, it will have to be a insert

this is an example of a make table query, it will create tablec for you

select cola, nz(colb,cola)
into tablec
from tablea


to use existng table

insert into tablec (cola, colb) select cola, nz(colb, cola) from tablea


the above statements copies all rows including those where colb is not null but it will contain cola value in it if null

CERTIFIED EXPERT
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.