Solved

Access Query Fill Empty Cells

Posted on 2009-05-08
8
875 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 450 total points
ID: 24337284
note, with the isnull, you can do this also

update mytable
set colb = cola
where colb is null
0
 

Assisted Solution

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

IF ISNULL(B) Then B=A
ELSE B=B
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 450 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now