Running more than one sql update query

[davidfox]

I am helping a company reorganize its employee data to reflect recent changes in the company's organization. It involves a lot of data manipulation that, for the most part, can't be done programatically.

I can save a little time using SQL update queries like this:

update personnel set Department = "Support Staff" where Jobtitle="Shipper"
update personnel set Department = "Maintenance" where Jobtitle="Mechanic"
update personnel set Department = "Regional" where Jobtitle="HR Manager"

I have prepared a text file containing 530 such queries that can be pasted into Access. But as much time as that saves me, it is still a day or two of cutting and pasting.

What would be the best way to talk Access into running all the queries, without having to paste in every one individually?

Thanks...
...df

 

[Pat Hartman]

Don't hurt yourself as your hand hits your forhead but a better solution would have been to create a "cross reference" table and use that in your update query. That way you need only a single query.

Update MainTable InnerJoin crossrefTable On MainTable.JobTitle = CrossrefTable.JobTitle Set MainTable.Department = CrossrefTable.Department;

The crossref table contains just two columns:

JobTitle, Department
Shipper, Support Staff
Mechanic, Maintenance
HR Manager, Regional

 

[davidfox]

Awesome. Thanks a million.