Update Multiple Fields from a Table (Linked or Otherwise)

[Guest]

I can't figure out how to do this.
I have an Access Database 03' that manages our Service Calls. I have over 5K
customers in the database that were exported from our main company database
(txt converted to excel). Every few day's or so, I want to export the
customers from our main company database and update my customer list in
Access and Append any new customers added to our main company database.

I'm ok with the append, I just can't seem to get the update to work. I can
get it to work if I run a query for each individual field, but I can't do all
of the fields at once.

For now, I'd be happy to get it working with an Access Table. But eventually
I'd like to use a linked table in excel format (if that factors into the
answer).

So say I have two tables: Suppliers and Suppliers2
Suppliers2 has the most up-to-date information so I want to update Suppliers
with Suppliers2

Fields: Address, City, State
qry_UpdateAddress
Field: Address
Table: Suppliers2
Criteria: <>[Suppliers].[Address]

That returns the results I'm looking for.
Again, I just want to be able add City, State to the same query. I tried to
put the Criteria in the 'or' field, but that didn't work...

I'd really appreciate your help on this!

 

[John Spencer]

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Also how are you handling Null values in the suppliers table

You probably need a query that looks something like the following.

UPDATE Suppliers INNER JOIN Suppliers2
ON Supplier.SupplierID = Suppliers2.SupplierID
SET Suppliers.Address = Suppliers2.Address
, Suppliers.City = Suppliers2.City
, Suppliers.State = Suppliers2.State
, Suppliers.PostalCode = Suppliers2.PostalCode
WHERE Suppliers.Address <> Suppliers2.Address
OR Suppliers.City <> Suppliers2.City
OR Suppliers.State <> Suppliers2.State
OR Suppliers.PostalCode <> Suppliers2.PostalCode
OR Suppliers.Address Is Null
ORSuppliers.City Is Null
OR Suppliers.State is Null
OR Suppliers.PostalCode is Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I really need to learn how to write SQL statements.
I appreciate the quick response.

I created the SQL query and received an error.
Syntax error in JOIN Operation
HIghlights Suppliers (after UPDATE in the statement)

I'm using the Northwind Sample Database.
I just copied the Suppliers table and named it Suppliers2 & then altered a
few fields.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Also how are you handling Null values in the suppliers table

You probably need a query that looks something like the following.

UPDATE Suppliers INNER JOIN Suppliers2
ON Supplier.SupplierID = Suppliers2.SupplierID
SET Suppliers.Address = Suppliers2.Address
, Suppliers.City = Suppliers2.City
, Suppliers.State = Suppliers2.State
, Suppliers.PostalCode = Suppliers2.PostalCode
WHERE Suppliers.Address <> Suppliers2.Address
OR Suppliers.City <> Suppliers2.City
OR Suppliers.State <> Suppliers2.State
OR Suppliers.PostalCode <> Suppliers2.PostalCode
OR Suppliers.Address Is Null
ORSuppliers.City Is Null
OR Suppliers.State is Null
OR Suppliers.PostalCode is Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I can't figure out how to do this.
I have an Access Database 03' that manages our Service Calls. I have over
5K
customers in the database that were exported from our main company
database
(txt converted to excel). Every few day's or so, I want to export the
customers from our main company database and update my customer list in
Access and Append any new customers added to our main company database.

I'm ok with the append, I just can't seem to get the update to work. I can
get it to work if I run a query for each individual field, but I can't do
all
of the fields at once.

For now, I'd be happy to get it working with an Access Table. But
eventually
I'd like to use a linked table in excel format (if that factors into the
answer).

So say I have two tables: Suppliers and Suppliers2
Suppliers2 has the most up-to-date information so I want to update
Suppliers
with Suppliers2

Fields: Address, City, State
qry_UpdateAddress
Field: Address
Table: Suppliers2
Criteria: <>[Suppliers].[Address]

That returns the results I'm looking for.
Again, I just want to be able add City, State to the same query. I tried
to
put the Criteria in the 'or' field, but that didn't work...

I'd really appreciate your help on this!

 

[John Spencer]

I have a typo in that. The on clause did read

ON Supplier.SupplierID = Suppliers2.SupplierID
and it should read
ON Suppliers.SupplierID = Suppliers2.SupplierID

Remember, I don't know your field and table names. You are responsible
to fix my errors.... chuckle.

UPDATE Suppliers INNER JOIN Suppliers2
ON Suppliers.SupplierID = Suppliers2.SupplierID
SET Suppliers.Address = Suppliers2.Address
, Suppliers.City = Suppliers2.City
, Suppliers.State = Suppliers2.State
, Suppliers.PostalCode = Suppliers2.PostalCode
WHERE Suppliers.Address <> Suppliers2.Address
OR Suppliers.City <> Suppliers2.City
OR Suppliers.State <> Suppliers2.State
OR Suppliers.PostalCode <> Suppliers2.PostalCode
OR Suppliers.Address Is Null
ORSuppliers.City Is Null
OR Suppliers.State is Null
OR Suppliers.PostalCode is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I really need to learn how to write SQL statements.
I appreciate the quick response.

I created the SQL query and received an error.
Syntax error in JOIN Operation
HIghlights Suppliers (after UPDATE in the statement)

I'm using the Northwind Sample Database.
I just copied the Suppliers table and named it Suppliers2 & then altered a
few fields.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Also how are you handling Null values in the suppliers table

You probably need a query that looks something like the following.

UPDATE Suppliers INNER JOIN Suppliers2
ON Supplier.SupplierID = Suppliers2.SupplierID
SET Suppliers.Address = Suppliers2.Address
, Suppliers.City = Suppliers2.City
, Suppliers.State = Suppliers2.State
, Suppliers.PostalCode = Suppliers2.PostalCode
WHERE Suppliers.Address <> Suppliers2.Address
OR Suppliers.City <> Suppliers2.City
OR Suppliers.State <> Suppliers2.State
OR Suppliers.PostalCode <> Suppliers2.PostalCode
OR Suppliers.Address Is Null
ORSuppliers.City Is Null
OR Suppliers.State is Null
OR Suppliers.PostalCode is Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I can't figure out how to do this.
I have an Access Database 03' that manages our Service Calls. I have over
5K
customers in the database that were exported from our main company
database
(txt converted to excel). Every few day's or so, I want to export the
customers from our main company database and update my customer list in
Access and Append any new customers added to our main company database.

I'm ok with the append, I just can't seem to get the update to work. I can
get it to work if I run a query for each individual field, but I can't do
all
of the fields at once.

For now, I'd be happy to get it working with an Access Table. But
eventually
I'd like to use a linked table in excel format (if that factors into the
answer).

So say I have two tables: Suppliers and Suppliers2
Suppliers2 has the most up-to-date information so I want to update
Suppliers
with Suppliers2

Fields: Address, City, State
qry_UpdateAddress
Field: Address
Table: Suppliers2
Criteria: <>[Suppliers].[Address]

That returns the results I'm looking for.
Again, I just want to be able add City, State to the same query. I tried
to
put the Criteria in the 'or' field, but that didn't work...

I'd really appreciate your help on this!

 

[Guest]

Works Perfectly!!!
I can't thank you enough.

I have a typo in that. The on clause did read

ON Supplier.SupplierID = Suppliers2.SupplierID
and it should read
ON Suppliers.SupplierID = Suppliers2.SupplierID

Remember, I don't know your field and table names. You are responsible
to fix my errors.... chuckle.

UPDATE Suppliers INNER JOIN Suppliers2
ON Suppliers.SupplierID = Suppliers2.SupplierID
SET Suppliers.Address = Suppliers2.Address
, Suppliers.City = Suppliers2.City
, Suppliers.State = Suppliers2.State
, Suppliers.PostalCode = Suppliers2.PostalCode
WHERE Suppliers.Address <> Suppliers2.Address
OR Suppliers.City <> Suppliers2.City
OR Suppliers.State <> Suppliers2.State
OR Suppliers.PostalCode <> Suppliers2.PostalCode
OR Suppliers.Address Is Null
ORSuppliers.City Is Null
OR Suppliers.State is Null
OR Suppliers.PostalCode is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I really need to learn how to write SQL statements.
I appreciate the quick response.

I created the SQL query and received an error.
Syntax error in JOIN Operation
HIghlights Suppliers (after UPDATE in the statement)

I'm using the Northwind Sample Database.
I just copied the Suppliers table and named it Suppliers2 & then altered a
few fields.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Also how are you handling Null values in the suppliers table

You probably need a query that looks something like the following.

UPDATE Suppliers INNER JOIN Suppliers2
ON Supplier.SupplierID = Suppliers2.SupplierID
SET Suppliers.Address = Suppliers2.Address
, Suppliers.City = Suppliers2.City
, Suppliers.State = Suppliers2.State
, Suppliers.PostalCode = Suppliers2.PostalCode
WHERE Suppliers.Address <> Suppliers2.Address
OR Suppliers.City <> Suppliers2.City
OR Suppliers.State <> Suppliers2.State
OR Suppliers.PostalCode <> Suppliers2.PostalCode
OR Suppliers.Address Is Null
ORSuppliers.City Is Null
OR Suppliers.State is Null
OR Suppliers.PostalCode is Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I can't figure out how to do this.
I have an Access Database 03' that manages our Service Calls. I have over
5K
customers in the database that were exported from our main company
database
(txt converted to excel). Every few day's or so, I want to export the
customers from our main company database and update my customer list in
Access and Append any new customers added to our main company database.

I'm ok with the append, I just can't seem to get the update to work. I can
get it to work if I run a query for each individual field, but I can't do
all
of the fields at once.

For now, I'd be happy to get it working with an Access Table. But
eventually
I'd like to use a linked table in excel format (if that factors into the
answer).

So say I have two tables: Suppliers and Suppliers2
Suppliers2 has the most up-to-date information so I want to update
Suppliers
with Suppliers2

Fields: Address, City, State
qry_UpdateAddress
Field: Address
Table: Suppliers2
Criteria: <>[Suppliers].[Address]

That returns the results I'm looking for.
Again, I just want to be able add City, State to the same query. I tried
to
put the Criteria in the 'or' field, but that didn't work...

I'd really appreciate your help on this!