Multiple Update Queries in Access

Question

Using Access 2007, I have an update that I am trying to build a script or macro or even just a useful chunk of SQL that I can keep and use whenever I want to run the update...

...I have figured out how to this with an update query, but, I don't want to have to spend an hour or so everytime I want to update...

Basically, I am cross walking office symbols for old to new...but all of the old office symbols need to be updated:

This is the SQL view from in access:

UPDATE [Actions Completed - Back UP] 
SET [Actions Completed - Back UP].OFC_NEW = [UPDATE TO:]
WHERE ((([Actions Completed - Back UP].OFC) Like [old]));

I used prompts to just run through my list of changes...

THANKS!

yoopersmith

Answer 1

You have to describe your problem a bit more I think, what you are trying to achieve is not entirely clear: explain what you are tracking, how your table is made and what it contains.

Having said that, using a Datasheet form is a very easy way to enter your data (Access 2007):

• click on your Actions Completed - Back UP table.
• create a new Datasheet form (Forms > More Forms > Datasheet)
• you can hide the columns you don't want to appear (right-click and select Hide)
• you can lock the columns you don't want to edit by mistake (in the Property Sheet > Data > Locked = Yes)
• You can order and pre-filter the list (put the table in Design view in Home > View, click on the form and in the Property Sheet under Data you can change Order By and Filter)
• when viewing data, you can also filter the list based on column filters (click the triangle in the column header and you'll get a list of the filtering options, like Excel).

You should now be able to edit your data much faster than having to fill-in input boxes.

There are more interesting things you could do but since we don;t know much about your problem, it's a bit hard to know if it would help or not.

Answer 2

I suggest you create a table that holds the required changes:

OldCode  NewCode
SYK      SYSW
SMC/PK   SYSW
AXK      SMC/PK

And so on.

You can then easily run an update query:

UPDATE tblTable 
INNER JOIN tblNewCodes ON tblTable.Code = tblNewCodes.OldCode
SET tblTable.Code = tblNewCodes.NewCode