How to edit multiple records

[robinyearwood]

Hi everyone,

I am extremely new to access, but I want to take my companies job management to another level and take them out of Excel and into Access.



I have a bunch of records or entries I have import them into access just fine. My problem is this, the data entry in the excel spreadsheet was all over the place. So for example in the location field I get "St. Michael" "Saint Michael" "St Michael" and sometimes just "Michael".

So I created a new column to replace this field with with a combo box, that way when entering new jobs into the database they just pick from a list and it's consistent. So I have to manually populate the column with the values from the previous column but I am trying to find a way to edit multiple combo boxes at once.

So for example I could go to the old location column and filter by all the different versions of "Michael" and then update the new column of all the filtered records by selecting the "St. Michael" option in the combo box. Right now the only way I can work out how to do it is to manually click each entry and select the location but I feel there must be and easier way to do it as I have 1000's of records.

Thanks in advance for your help.

[moke123]

Do you have a table of valid locations? How many are there?

If you know how many variants there are you can do update queries along the lines of
Update someTable set LocationField = "Saint Michael" Where LocationField in("St.Micheal","St Micheal","Michael", etc)

[ranman256]

this is the bane of databases, addresses and names.
I would add a MARK field (yes/no)
run an update query to the mark yes all records LIKE *MICHAEL*
then review each to see if they need changed, if not remove the mark.

then run an update query to set the new name ST. MICHAEL to all MARK =TRUE.

[Gicu]

Or build a lookup table with two fields: ExistingLocation, UpdatedLocation. Now populate it (you can use an append query to add all the existing locations from your current table using SELECT DISTINCT to avoid duplicates), add the proper UpdatedLocation then create your update query.

Cheers,

[robinyearwood]

Thanks everyone for you help, your suggests helped me to get my brain to think in Access and work it out.