Easy question -update query

[Guest]

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!

 

[Guest]

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

 

[Guest]

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]), ...

 

[Guest]

Yes, you can do both an update and an insert at the same time. I've heard
this called an UPSERT query. Although you can do this in Access, you cannot
do it in SQL Server 2000, don't know whether SS 2005 will allow it or not.

The SQL would look something like (watch the line wrap):

UPDATE tbl_Upsert2 LEFT JOIN tbl_Upsert1
ON tbl_Upsert2.ID = tbl_Upsert1.ID
SET tbl_Upsert1.ID = NZ([tbl_Upsert2].[ID],[tbl_Upsert1].[ID]),
tbl_Upsert1.LastName = NZ([tbl_Upsert2].[LastName],[tbl_Upsert1].[LastName]),
tbl_Upsert1.FirstName =
NZ([tbl_Upsert2].[FirstName],[tbl_Upsert1].[FirstName]),
tbl_Upsert1.Phone = NZ([tbl_Upsert2].[Phone],[tbl_Upsert1].[Phone]);

The way to do this is to Left join from your linked Excel table to the
Access table you want to update. Then, you update the data in table1 with
the data in table 2, using NZ to avoid overwriting data with a null from
table2. Because you have left joined from Table2, if the record does not
already exist in table1, it will be inserted.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

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]), ...

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

 

[Guest]

Hi again

The right join would have indeed caused the wrong updates to have happened.

And, as far as I know, there is no way in Access to do an Upsert in a single
query so I think you will need to do 2 queries - an update followed by an
insert.

The queries should be structured something like the following...

The Update query...

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

And the Insert query...

INSERT INTO [Data Sheet] ( [Vendor Number], Category )
SELECT [UpdateData].[Vendor Number], [UpdateData].[Category]
FROM [Data Sheet] RIGHT JOIN UpdateData
ON [Data Sheet].[Vendor Number]=[UpdateData].[Vendor Number]
WHERE [Data Sheet].[Vendor Number] is Null

{Note: Add any other columns being inserted into the Select clause}

Hope this helps

Regards

Andy Hull

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]), ...

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

 

[Guest]

Hi again

My last post was not entirely accurate! The queries will work but, as Dale
suggested, you can do an update and insert in one query using the syntax he
posted.

Thanks Dale!

Using your table and column names the query will look like...

update [Data Sheet] right join UpdateData
on [Data Sheet].[Vendor Number] = UpdateData.[Vendor Number]
set [Data Sheet].[Vendor Number] = UpdateData.[Vendor Number],
[Data Sheet].Category = Nz(UpdateData.Category, [Data Sheet].Category),
....


No need to use the Nz function on Update.Data[Vendor Number] as the join
column on the right side of a right join can't be Null.

Regards

Andy Hull

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]), ...

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