I'm trying to clean data that had no input mask. The variable is supposed to be surname but I have blank fields, number codes, alphanumeric codes and surnames.

Is there any query that I could do that could categorise these ie make a new field that states the type of surname entered?

If it can't be done in access can it be done in excel?

Thanks,

Sandra

Hi Sandra

Could you post some examples of the data.

You can do a update query, once you have obtained the dataset that you want to categorise.

Add a new field to the table, can call it something like SurnameCategory

Once you have the basic SELECT query in place (ie. IS NULL for blanks), then change the query type to UPDATE. (you will find this next to the Red Exclaimation mark on the toolbar).

In the query you will need to have two fields:
The one that you are filtering (surname), and the one you will update (SurnameCatagory).
Simply specify what you want to update the SurnameCategory field to (ie: "BLANK"), and then click the RED EXCLAIMATION mark to run the Query.


I suggest that you backup or make a copy of your database before doing this, as the results are not reversable.

Let me know if this works out for you.

Regards

Richard