Improve company productivity with a Business Account.Sign Up

x
?
Solved

Access Query Fill Empty Cells

Posted on 2009-05-08
8
Medium Priority
?
894 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.

0
Comment
Question by:tahirih
  • 5
  • 2
8 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24337245
how about this

update mytable
set colb = cola
where isnull(colb) = true
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24337262
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,"")) = ""

0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1800 total points
ID: 24337284
note, with the isnull, you can do this also

update mytable
set colb = cola
where colb is null
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Assisted Solution

by:greyhuman
greyhuman earned 200 total points
ID: 24337288
Simple USE

IF ISNULL(B) Then B=A
ELSE B=B
0
 

Author Comment

by:tahirih
ID: 24337313
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24337344
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

0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 1800 total points
ID: 24337347
if u just want the records with nulls in it, just extend the select so you add in where colb is null
0
 

Author Closing Comment

by:tahirih
ID: 31579500
Thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Watch the video to know the simple way to remove or recover or reset lost or forgotten passwords of Outlook PST file. With Kernel Outlook Password Recovery tool such operation is very easy to perform. It is a freeware with limitation to use with 500…

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question