Update query that does not create extra lines with changes?

[kb44]

Hi all,

I need to create an UPDATE query that updates tableB (destination) with the data from tableA (source) and that does not create an extra line for each change in the table.



Here is the break down:

two tables with matching id's

tableA (source) has 6 columns and about 700 lines

tableB (destination) has 18 columns and also about 700 lines

There will be roughly about 95 changed fields



I would like to:

Update tableB with the data from tableA by id's

Do this without adding extra lines (for changes in lines with matching id's)

Only add a line to tableB if there is a new ID in tableA



What I have done so far:

Created an update query

Added both the source and destination tables to the query

Created a 1 to 1 relationship between the ID's

Added the names of the destination fields to the Field row of the query design grid

Added the names of source fields to the Update To row of the query design grid by using the following syntax:

.[source_field]

This results in a list that for some reason creates an extra line for each change found.

Here is my SQL so far:

UPDATE [1e tbl_Select Current Month TEMP] INNER JOIN [Copy Of tbl_Select Current Month] ON [1e tbl_Select Current Month TEMP].[ID] = [Copy Of tbl_Select Current Month].[ID] SET [Copy Of tbl_Select Current Month].Country = [1e tbl_Select Current Month TEMP].[Country], [Copy Of tbl_Select Current Month].[Business Unit] = [1e tbl_Select Current Month TEMP].[Business Unit], [Copy Of tbl_Select Current Month].[Corporate Job Title] = [1e tbl_Select Current Month TEMP].[Corporate Job Title], [Copy Of tbl_Select Current Month].[Reports To] = [1e tbl_Select Current Month TEMP].[Reports To], [Copy Of tbl_Select Current Month].Territory = [1e tbl_Select Current Month TEMP].[Territory];

I think I am close but being new to access it's been a pain figuring this out. Thank you.

 

[theDBguy]

Hi,

Try this on copy of your tables just test it out first. Create the same query you have now and run it. Open the updated table and see if the any rows were added. I suspect you won’t find any new rows. If is, then the reason you’re seeing additional rows is probably because you may actually have a many to many relationship.

 

[The_Doc_Man]

An UPDATE query cannot create rows. That honor belongs to INSERT INTO. But if you have multiple combinations defined by the JOIN, each such combination could be updated. In particular, if the relationship is one-to-many then the same record might get updated many times and only the one that matched last is retained because each prior update would be overwritten.

 

[isladogs]

Just to add to the previous answers, you also need an APPEND query to add the new records.

However in some circumstances, it is possible to combine APPEND and UPDATE into one query.
This is commonly called an UPSERT or UPEND query. See http://www.mendipdatasystems.co.uk/upend-query/4594428616

 

[kb44]

Thanks for the replies everyone!

I looked and do not see a one to many relationship. I only see a one to one by id. I will look again though.

A closer look revealed that the only entries being duplicated are the ones where the field changed from a value to a blank space (the field was deleted).

Is there a condition I can enter to handle the blank fields so that the empty field is displayed as the change shows without adding a line?

 

[theDBguy]

Thanks for the replies everyone!

I looked and do not see a one to many relationship. I only see a one to one by id. I will look again though.

A closer look revealed that the only entries being duplicated are the ones where the field changed from a value to a blank space (the field was deleted).

Is there a condition I can enter to handle the blank fields so that the empty field is displayed as the change shows without adding a line?

Hi. Can you show us how you're seeing the additional lines? As was previously mentioned, and UPDATE query will not add any new lines to the table. Maybe you're using a SELECT query instead?

 

[kb44]

Hi. Can you show us how you're seeing the additional lines? As was previously mentioned, and UPDATE query will not add any new lines to the table. Maybe you're using a SELECT query instead?

Here you go. As you can see, the second and third from the bottom are duplicates. This only happens when the change is from a field with input to an empty field.

Edit: you can also see it on the fourth and fifth entries from the top.

Edit: Here is the SQL:

UPDATE [1e tbl_Select Current Month TEMP] INNER JOIN [Copy Of tbl_Select Current Month] ON [1e tbl_Select Current Month TEMP].[ID] = [Copy Of tbl_Select Current Month].[ID] SET [Copy Of tbl_Select Current Month].Country = [1e tbl_Select Current Month TEMP].[Country], [Copy Of tbl_Select Current Month].[Business Unit] = [1e tbl_Select Current Month TEMP].[Business Unit], [Copy Of tbl_Select Current Month].[Corporate Job Title] = [1e tbl_Select Current Month TEMP].[Corporate Job Title], [Copy Of tbl_Select Current Month].[Reports To] = [1e tbl_Select Current Month TEMP].[Reports To], [Copy Of tbl_Select Current Month].Territory = [1e tbl_Select Current Month TEMP].[Territory];

 

[theDBguy]

Hi. Thanks. This is "what" you're seeing. I was wondering "how" you're seeing it. For example, are you clicking the Datasheet View on the Ribbon when viewing the UPDATE query in Design View?


PS. Can you please post the SQL statement of your UPDATE query? Thanks.

 

[kb44]

Hi. Thanks. This is "what" you're seeing. I was wondering "how" you're seeing it. For example, are you clicking the Datasheet View on the Ribbon when viewing the UPDATE query in Design View?


PS. Can you please post the SQL statement of your UPDATE query? Thanks.

That is what I see after fully running the query. I posted the SQL above in my edit.

 

[theDBguy]

That is what I see after fully running the query. I posted the SQL above in my edit.

Hi. If you run an UPDATE query, you shouldn't see anything, other than messages from Access to confirm the changes it's about to make. So, is the picture you posted earlier actually an image of your table then? If a query, then it must be a SELECT query (for you to see records by just running it).


PS. To help better understand the problem, can you please post a small copy of your db with test data? Thanks.

 

[isladogs]

How about excluding records from the update where the fields are null?

 

[kb44]

Hi. If you run an UPDATE query, you shouldn't see anything, other than messages from Access to confirm the changes it's about to make. So, is the picture you posted earlier actually an image of your table then? If a query, then it must be a SELECT query (for you to see records by just running it).


PS. To help better understand the problem, can you please post a small copy of your db with test data? Thanks.

Now I understand what you mean. Indeed, when I run the update query I see a message informing me about:

• the fact that I am about to execute an update
• how many records will be updated

I will work on posting some form of the DB since it has sensitive data.

 

[kb44]

How about excluding records from the update where the fields are null?

I can do that but will have do do it eventually (possibly in a different query?) since the update to a blank field is important also.

 

[theDBguy]

Okay, so, where did the image you posted earlier come from? Is it an image of your table or a SELECT query?

 

[kb44]

Okay, so, where did the image you posted earlier come from? Is it an image of your table or a SELECT query?

That was the resulting table after the UPDATE query was executed.

 

[theDBguy]

That was the resulting table after the UPDATE query was executed.

Ah, okay, it was a table. So, if you don't want any duplicates in the table, you'll have to manually delete them. The UPDATE query did not add them for you. As I was saying earlier, try it out on a copy of your tables first. Open the table copies and delete any duplicate records.Then, run the same UPDATE query and see if you see any duplicates in the table after you run the updates. Please let us know what you find out. Thanks.

 

[isladogs]

You can see which records will be affected by an UPDATE query by selecting Datasheet View before you run the query.
In fact I thought that was what you were showing us, hence my comment about excluding nulls.

 

[kb44]

Ah, okay, it was a table. So, if you don't want any duplicates in the table, you'll have to manually delete them. The UPDATE query did not add them for you. As I was saying earlier, try it out on a copy of your tables first. Open the table copies and delete any duplicate records.Then, run the same UPDATE query and see if you see any duplicates in the table after you run the updates. Please let us know what you find out. Thanks.

Wow. Thank you. I thought that it was the UPDATE query that was adding the duplicate lines, when in fact the duplicates were there already since I used the wrong table to begin with.

Since I have been testing a lot and re-naming tables, that's where the error occurred.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Remember, UPDATE queries only "change" values, they don't "add" records (unless you use one of those UPSERT or UPEND query variants). Good luck with your project.

 

[kb44]

Hi. Glad to hear you got it sorted out. Remember, UPDATE queries only "change" values, they don't "add" records (unless you use one of those UPSERT or UPEND query variants). Good luck with your project.

I definitely will. At my next progress meeting I'll see what they say about the project. Feels like I am making good progress.

Thanks to everyone for your help and sharing your knowledge.