RE: Easy question -update query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

So I had a right join beforehand and so I guess that might have been causing
the problem. I replaced right join with inner join. The updating works fine
for already existing vendor numbers now and cells arn't being replaced by
blanks.

However, if i have new records in my UpdateData table (ie, for a new vendor
number that is in the UpdateData table but not in the Data Sheet table) the
query will not append the record for that new vendor number with its
information in the Data Sheet table from the UpdateData table.

Do you know if this is possible? Thank you so much!

UPDATE [Data Sheet] INNER JOIN UpdateData ON [Data Sheet].[Vendor Number] =
UpdateData.[Vendor Number]
SET [Data Sheet].Category = NZ([UpdateData].[Category],[Data
Sheet].[Category]), ...

"Andy Hull" wrote:

Hi

Dificult to say without seeing your query.

But your query should look something like...

Update Table1 inner join Table2 on Table1.Column1 = Table2.Column1
Set Table1.Column2 = Nz(Table2.Column2, Table1.Column2),
Table1.Column3 = Nz(Table2.Column3, Table1.Column3),
...


I don't know how the tables are joined so I have assumed an inner join using
a field called Column1. You should change this as necessary.

Hope this helps

Andy Hull




"arsenalattack007" wrote:

I have two tables in my database.
I would like to update the information in table 1 with the information in
table 2.
I am importing information from an excel file in table 2, and depending on
the content, entire columns and many cells will be left blank in table 2.
When I run the update query, the blanks in table 2 are replacing already
existing information(ie, non blanks) in table 1 with blanks.

I tried the NZ function and the blanks in table 2 are now not replacing
information if it exists in table 1. (which is what i want). However, if a
specific cell for a record is blank in both table 1 and table 2, for some
reason it is uploading data for this cell in this record from the next record
in table 2 that is non blank for that field.

Does anyone know why this is happening? If not, please guide me from scratch
as to how to achieve this!

Your help is much appreciated and will make my day. Thank you!
.

Relevant Pages

  • sql manipulation
    ... Here is the sql I am working with, ... First 4 consonants of the last name, replacing any blanks with 'b' ... First 3 characters of the street, ...
    (microsoft.public.sqlserver.programming)
  • Re: Ideas for finding blank data fields in user records?
    ... Terence wrote: ... In the third step, you have to set all DIGITS to 9, but not the ... replacing blanks by '9' would be a problem if you had a field that looked like 'bbb-1.3' -- you'd end up with '999-1.3' and a run-time error. ...
    (comp.lang.fortran)
  • Re: sql manipulation
    ... >First character of the first name, replacing any blanks with 'b' ... >Your suggestion/approach is appreciated. ...
    (microsoft.public.sqlserver.programming)
  • Re: what does "serialization" mean?
    ... Nick Landsberg writes: ... > &#aaaa encodings to render Cyrillic. ... Replacing ... > blanks with had the web page renderred ...
    (comp.programming)