standardising input form entries retrospectively

[fingermouse]

Hi,

I should start off by stating that im pretty much a complete novice in Access, but will attempt to explain the situation as best I can.

I have been tasked with creating pivot tables in excel via an external connection (an access database) I have managed to do this via Pivot Table > 'use an external data source' then choosing the table (s) I require.

My problem is that in these tables, there are a number of fields across all records where a number of people have input non-standardised entries eg: 'Case Opened', 'Case:Opened', 'Case - Opened' etc... Pretty much multiple ways of saying the same thing.

As a result, when I try to pivot the data, I get the individual outputs in terms of counts.

Going back into each field and standardising the text in each record isnt really an option as it would take weeks. Is there anyway I can edit the tables quickly and easily? Any help much appreciated! Cal.

 

[xenou]

I'd get started standardizing. It shouldn't take weeks. Pivot tables allow you to filter/group but I'm not sure if it could do this on text fields like this. Also, you'll have the same problem every time you try to create a new query on this data.

 

[fingermouse]

I'd get started standardizing. It shouldn't take weeks. Pivot tables allow you to filter/group but I'm not sure if it could do this on text fields like this. Also, you'll have the same problem every time you try to create a new query on this data.

ok, thanks for the advice Xenou, much appreciated. Ill try and ensure that my colleagues stick to the standard text going forward!

 

[xenou]

Okay. You will probably start with a Select Distinct XXX From Table to find out how many variations you have, then start grouping them together (possibly in Excel).

Using some simple concatenation formulas you can build out your updates pretty easily (as you can see in the last row).
------------------------------------------------------------------------------------------------
| Case: Opened | Case Opened | Update Table Set XXX = 'Case Opened' Where XXX = 'Case: Opened' |
| Case Open    | Case Opened | Update Table Set XXX = 'Case Opened' Where XXX = 'Case Open'    |
| Open Case    | Case Opened | Update Table Set XXX = 'Case Opened' Where XXX = 'Open Case'    |
| Open         | Case Opened | ="Update Table Set XXX = '"&B4&"' Where XXX = '"&A4&"'"         |
------------------------------------------------------------------------------------------------


You could also do this with a lookup table (to not change the original table). Or by adding a field for the cleaned up text in the table (to save the original input).

 

[baderms]

Don't give them the option to stray from the standard. Edit the front end so they can only select what you want them to.

 

[Micron]

Agree with last 2 posts. When you have discovered the variations as suggested, you should be able to decide if you can run an update query against the Access tables, using LIKE and wild cards (*) to update anything that resembles *Case* to whatever it is you want it to be. You can do several fields in the same table at once if necessary, but if you have related records based on any field that you want to update, you could introduce problems, assuming the query didn't fail because of that.

Forethought during design should prevent such issues, but that usually comes with experience.