docmd.runcmd SQL functions

  • Thread starter Thread starter Carl29464
  • Start date Start date
C

Carl29464

I have a table with fields a,b,c,d. If I have a row (x) where x.d is
blank, I want to search the table and see if there is a row (y) such
that x.c=y.c and x.b=y.b and if y.d is not blank, then I want x.d to
be filled in with y.d.

I can't seem to figure out how to start this.

Thanks

Carl
 
I agree with you however I am trying to massage a table I was given...
Sometimes flat files work fine for quick and dirty work.

Carl
 
True, but what if you have records z and y that have identical values of b
and c, but different values for d? Which record wins? Not only do flat
file store data redundantly, but you have real data integrity concerns.

Nevertheless, you could use a correlated update query. Something like:

Update Table1 as T1 Set d = (Select d From Table1 as T2 Where T1.b = T2.b
And T1.c = T2.c)
Where T1.d Is Null;

Disclaimer: This was written off the top of my head. You also left off a
lot of details, so it may not apply or be complete. For instance, the above
will fill ANY d that is null. If you wanted a specific row filled, you
would add those values to the Where clause:

Where T1.d Is Null And T1.b = "zzz" and T1.c = "yyy";

But it should give you a starting point.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
I like the disclaimer, can I have your permission to use part of it in my
signature block? ;-)
 
Back
Top