Urgent Update Query

[Bill]

I have downloaded a XML file imported into access
converted to table created a make table query to give only
those records required. I now need access to update
records in a main table from the new table - basically
filling in the blanks with the downloaded data.

Main table = Table1
New Table = Table2

Do I use an update query or an append query?

 

[Gary Walter]

I have downloaded a XML file imported into access
converted to table created a make table query to give only
those records required. I now need access to update
records in a main table from the new table - basically
filling in the blanks with the downloaded data.

Main table = Table1
New Table = Table2

Do I use an update query or an append query?

Hi Bill,

I'm sure this is not what you want to hear...
but "it depends"...on what you mean by "blanks."

simple example:

tblItems ("Main table")
ID (pk) ItemNumber OnHand
1 1 5
2 2 4
3 3 6
4 4

tblItemsNew ("downloaded New table")
ID (pk) ItemNumber OnHand
1 1 3
2 2 4
3 3 6
4 4 12
5 5 9

blank1 -- no value for OnHand for item 4
blank2 -- main table had no item 5 previously

UPDATE tblItems RIGHT JOIN tblItemsNew
ON tblItems.ItemNumber = tblItemsNew.ItemNumber
SET tblItems.ItemNumber = [tblItemsNew].[ItemNumber],
tblItems.OnHand = [tblItemsNew].[OnHand];

the above query is an "update" query....
item 1 is changed to 3,
item 4 gets its blank filled in,

but it also will "append" item 5 to "main" table.

tblItems after running query:

ID ItemNumber OnHand
1 1 3
2 2 4
3 3 6
4 4 12
5 5 9

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter

 

[John Vinson]

I have downloaded a XML file imported into access
converted to table created a make table query to give only
those records required. I now need access to update
records in a main table from the new table - basically
filling in the blanks with the downloaded data.

Main table = Table1
New Table = Table2

Do I use an update query or an append query?

Depends on what you want to do.

An Append query creates new records where none existed before.

An Update query updates the fields in existing records.