[Access] - how to check what an update query is going to change

PrzemoF · 22-08-2016 09:43AM #1

I have 2 tables (A and

and I use update query (qC) to migrate information from A to B. The query qC is overwriting all information in table B, no questions asked, no criteria except a field that is common in both tables.

Is there an easy way to check what will be changed after I run the query?

WARNING: the question is about a badly designed database, so comments like: "why do you have the same information in 2 different tables?" will be ignored

Tom Dunne · 22-08-2016 10:03AM

PrzemoF wrote: »

Is there an easy way to check what will be changed after I run the query?

I'm not trying to be facetious, but won't reading the SQL code tell you what will be changed?

Other than that, does Access have a ROLLBACK command? If so, you can do the update, check what has changed and then, um, roll back if you don't like it. Just make sure auto-commit is off (again, assuming there is such a thing in Access).

PrzemoF · 22-08-2016 10:16AM

Thank for the reply!

I know exactly what will be changed in terms what the SQL code is trying to change - everything. It's overwrite-all right now. But I don't consider change of a value of "Something" to "Something" as a change, so I want to capture when "Something" is changed to "SomethingElse".
There are i.e. 90 records with 40 fields each - checking it manually is not an option. Ideally I'd like to make a query that shows only what has changed. I.e. if all fields for a record are the same in both tables, just do not show it in the results.

I can do export to excel before and after and compare it there easily, but that's an extra step.

I've no clue if access has rollback, but I can make a safe copy of the whole database.

BrokenArrows · 22-08-2016 10:21AM

1. Copy the contents to a temporary table.
2. Run the Update query.
3. Write an SQL query which compares the temp table against the newly updated table to return any rows which are different.

garbeth · 22-08-2016 10:22AM

I don't think access has a roll back capability but yes take a back up before running

Can you do a select statement to see what'll change replace your update line and keep all the rest

chakotha · 23-08-2016 01:51PM

Agreed - can you run a select first with the same criteria.

select count(*) as num where ...(update criteria)

beauf · 30-08-2016 04:35PM

I would make a copy of the table, then run a select which shows the current value and what it will be changed to.

Once happy with the select. I would copy the select and change it to an update or insert as required keeping the same where clauses.

In some cases I keep a table as logfile that I would update with changes. So I can refer to in the future, if there was a problem with the changes that were made.

PrzemoF · 31-08-2016 04:10PM

beauf wrote: »

I would make a copy of the table, then run a select which shows the current value and what it will be changed to.
[..]

That's almost impossible to do. 90 records with 40 fields each is deal breaker. The query would show 90 records and 80 fields (before and after) and most of the would contain the same value.

I just imported it without check, but if I have to do it next time I'll go for export to a spreadsheet and compare it with simple if function cell-to-cell.

P.S. It might be time to dive into VBA for access...

beauf · 31-08-2016 04:25PM

If most are same, then something that's different stands out. It just does.

You can even create a another column with a case statement that highlights fields that have changed. Or indeed a where clause to only display changed records. I've a table with 140 fields and a million + records. That's how I double check before I make changes. We have a trigger that makes a copy of any changes.

Using SQL/Query to do this will be vastly more efficient, and useful to your skillset than reverting to VBA. Because if you are working with database is likely something you will do a lot of. Going back to Excel works with small datasets but its a kludge. Though I do it myself from time to time.

Maybe I'm over thinking it.

PrzemoF · 01-09-2016 07:42AM

OK, I wasn't clear enough - most are the same in a colunm. An example:
W600 C06 40 1536 H 65012T1 65020TA 2588 2588 1559 255E 85A was changed to
W600 C06 40 1536 AEZI H 65012T1 65020TA 2588 2588 1559 255E 85A

It's easy to spot the change when you have them one above the other, but with side by side:
W600 C06 40 1536 H 65012T1 65020TA 2588 2588 1559 255E 85A was changed to W600 C06 40 1536 AEZI H 65012T1 65020TA 2588 2588 1559 255E 85A
itis impossible

Catching a changed record was not good enough - I don't want to compare 40 fields manually , so I was looking for something that would compare 2 tables, leave a cell (a particular field in a record) blank if the values are identical or show something if the values are not identical.

I probably know enough to make a query per column, but that's still 40 queries. I might think about generating the queries using python or something...

gargargar · 01-09-2016 01:43PM

If I understand correctly, could you run something like:

select count(1) from tableA, tableB
where tableA.[IDCOL]=tableB.[IDCOL] [Whatever the joining condition is]
and (
tableA.col1<>tableB.col1 or
tableA.col2<>tableB.col2 or
...
)

The tableA.col1<>tableB.col1 is seeing where there is a difference between the col in tableA that you are updating from tableB.

Not particularly pretty though

beauf · 01-09-2016 02:04PM

What I'm talking about is

Table A (original data)
Table B (modified data)

Inner Join A to B Then have a column C that shows the differences. (or multiple columns)

A1 is 1111 B2 is 1112
Add a column C that says if A1 is the same as B1 then display "ok" otherwise display "different"

You can also choose (filter) not to display rows where nothing is different.
You don't have to create table B is is just a select of what will happen if you create it.

The Query wouldn't be simple or short. But if its something you have check a lot, then it will be worth it.

beauf · 01-09-2016 02:07PM

Of course the real issue it likely to be its probably a bad database design if you have tables of 40 columns.

But that's like asking someone for directions and they tell you wouldn't I wouldn't start from here.

PrzemoF · 02-09-2016 07:01AM

beauf wrote: »

Of course the real issue it likely to be its probably a bad database design if you have tables of 40 columns.

But that's like asking someone for directions and they tell you wouldn't I wouldn't start from here.

Very good description of the situation!!!:)

It's a client database - I'll forget about it when the project is over.

PrzemoF · 02-09-2016 07:49AM

A query catching all changes (can be easily generated from field list), but it shows only ID of the modified record:

SELECT
     TABLE_A.ID
FROM TABLE_A,
     TABLE_B
WHERE (((TABLE_A.ID) = TABLE_B.ID)
AND (
   ((TABLE_A.FIELD_A) <> TABLE_B.FIELD_A)
OR ((TABLE_A.FIELD_B) <> TABLE_B.FIELD_B)
OR ((TABLE_A.FIELD_C) <> TABLE_B.FIELD_C)
OR ((TABLE_A.FIELD_D) <> TABLE_B.FIELD_D)
..
));

[Access] - how to check what an update query is going to change

Comments