MS Access Question-Input format/mask

Factual Questions
1

I don’t know if this is even possible, but I’m going to ask anyway.

I have been tasked to clean up yet another section’s database. While attempting to run an outer join with another DB using the name field, I discovered it wouldn’t work.

It wouldn’t work because the DB I’m attempting to clean up is in a sorry state due to non-consistency in how they enter data.

They have entered many names like this:

LASTNAME,FIRSTNAME,MI,

Yup, just like that. No spaces and commas where you’re seeing them in the example above. Additionally, they’re not being consistent. Not all names are entered this way, just about fifty percent of them.

The other DB has the name field entered thusly:

LAST NAME,FIRST NAME MI

As this data is pulled from a big DB in the sky, this is the format we need to use.

Is there away to input a mask or format for the name field in the FUBARRED DB and dummy proof it?

I see there are input formats for dates, SSNs, etc, but nothing for a name.

I’ve tried many different things using the SQL view and nothing works. Am I SOL?

2

It sounds like the biggest problem in your database is that the Name information is kept in a single field. I highly recommend that that you break this field out into at least three different fields for first name, last name, and middle name. “Prefix” wouldn’t be a bad field either (“Jr.,” “III,” etc.), or “Salutation” ("Mr., “Dr.,” etc.). You can then use code or an action query to parse out the existing names into the new fields, but only if the users have been consistent in how they enter the name (such as Last Name-comma-First Name-comma-Middle Name). Let me know if you need a hand creating an action query to parse out name data; they can be pretty nasty.

You can use the input mask to force the user to enter all text or force a comma, but Access has no way of know when they’re entering a first name or a last name or whatever into a single field. For instance, when would you know when to force the comma?

In my experience with fixing legacy databases, about half my time is spent parsing or modifying data with code, and the other half spent manually going through the records and changing some by hand.

3

OK let’ get this out of the way first - neither of those name fields is properly normalized.

That being said, I have to say I don’t know how to do this with a mask.

How I would do it………
you can write 2 queries. One to reformat the name field in each table using a combination of Instr(), Left(), Right() and Mid() making sure to include the primary keys in the results. The issue here is that you’ll get some fallout from the table that’s not consistent. You may have to tweak your query several times to get all the records to translate.

Once you’ve got those you can query them to match up the primary keys from each table.

Not elegant at all but it should work.

Of course someone will come along with an absolutely beautiful solution right after I post this :smiley:

4

And I see PoorYorick beat me to a pretty similar solution.

5

Absolutely. Names should be kept in one table and one table only, to avoid this very situation. Keys should be numeric. And I agree with splitting names into 3 fields.

Taters, do you have the means to restructure the DB? How many records are we talking here?

Oh, and I could find no way to mask it as you want to do.

6

Thanks all for your responses.

First, I realize the name field should be split into three separate fields, but, unfortunately, the data comes from another source.

Second, we’re talking many, many thousands of names and there is no way in hell I’m going to clean that up manually. I didn’t make the mess and I’ve preached consistency from the get-go. It all falls on deaf ears.

At any rate, I’ve had a chat with the boss discussing the issues with the fubarred DB. We’re going use the DB from sky. The problem with this particular DB is that of course it’s a huge file. I FTP it from another source and then bring it into Access. This file is way too large for Excel. Is this telling you all something?

Finally, I’m going to have to import it at least quarterly.

The action query to parse out names sounds very interesting and I’d be really interested in learning how to do that. I foresee more of these types of issues coming my way.

7

OK, here’s probably the simplest situation you’ll run into, and what I did with one database. It has a table called tblArtists with a field called Contact Name. This field contains an unparsed name usually written as First Name-space-MiddleName-space-LastName. I created three new fields called 1) ContactFName, 2) ContactMName, and 3) ContactLName. I created an update query using mostly LEFT, RIGHT, MID, LEN, and INSTR (as suggested by zoid) to update the new fields. The SQL was:

UPDATE tblArtists SET tblArtists.ContactFName = IIf(InStr([Contact Name]," “)<>0,Left([Contact Name],InStr(1,[Contact Name],” “)-1),” “), tblArtists.ContactLName = IIf(InStr(InStr([Contact Name],” “)+1,[Contact Name],” “)<>0,Right([Contact Name],Len([Contact Name])-InStr(InStr([Contact Name],” “)+1,[Contact Name],” “)),Right([Contact Name],Len([Contact Name])-InStr([Contact Name],” “))), tblArtists.ContactMName = IIf(InStr(InStr([Contact Name],” “)+1,[Contact Name],” “)<>0,Mid([Contact Name],InStr(1,[Contact Name],” “)+1,InStr(InStr(1,[Contact Name],” “)+1,[Contact Name],” “)-InStr(1,[Contact Name],” “)),” ")
WHERE (((tblArtists.[Contact Name]) Is Not Null));

You can modify this to replace the field names, table names, and the " " with “,”. However a simple parse like this only works if the names are consistently entered in the same order.

8

Thanks. I’ll give this a try too. I’m always up for improving my skill sets.