Table question

[youdaman]

I have two fields in a table that I would like to combine into one field. Example: UserName CompName field1
doej rocky doej:rocky

I have been able to do it in a form using a default value:=([CompName]) & ([UserName]) for field1. I would like to do the append in the table without developing a form for every record in the table. I have tried to add =([CompName]) & ([UserName]) to the default value of field1 in the table but get an error 'The database engine does not recognize either the field 'CompName' in validation expression, or the default value in the table "Table1". Any ideas would be greatly appreciated.

 

[RickSpr]

Create an update query. (That is, create a standard query based on this table, and then change it to an Update Query using the Query menu in Design View.)

Drag field1 to the grid. in the Update To: line underneath field1, enter the expression:
[CompName] & [UserName]

Save the query, then double-click it in the Database Window to run it.

Update queries are the workhorses to use for bulk updating of a table. Rick Sprague

 

[JeremyNYC]

Short Answer: Not a good idea.

Only Slightly Longer Answer: It's much better to store your data in separate fields and do the concatenating on forms and reports. This is one of the basic principals of database design. Search the web for some information on Data Normalization. On my website I've got a great article written by Paul Litwin (Fundamentals).

The default value is not the place to do this, in any case. That only affects newly created rows. You could do this with an update query, using the syntax that worked for you before. But again, I strongly suggest that you not do it, as it will cause problems for you down the road. The article will explain why.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[RickSpr]

Jeremy stepped back and took a broader look at what you're doing. In general, I would agree with him--if you intend to access the individual components separately, you usually shouldn't combine them.

However, there are times when it's best to break the rules. I'm doing exactly this, combining not only first and last names, but title ("Mr.&quot and suffix ("Jr.&quot as well, while still maintaining the distinct component fields. This means I've deliberately put redundant data in my table, and created a burden for myself when any part of the name gets updated.

But I've done this with full knowledge of what I was doing, and I've imposed some rules on myself to keep the maintenance burden down. The user never gets to see the individual components, only the full name. The individual components are there for searching and sorting only. Any time the full name is updated, it is immediately parsed and the components are updated. They are never updated by any other means. This keeps me in sync.

The reason I keep the full name redundantly is that some people are very picky about how their name is spelled, punctuated, etc. When I parse out the components, spacing and punctuation are lost and can't be reconstructed.

The reason I keep the components is that searching a full name for the suffix "Sr.", meaning "Senior" can never be completely reliable--because "Sr." is also a title meaning "Sister" (a nun). There are other examples of this sort.

When I parse the full name into components, these aberrations mean I might make some mistakes. I use a heuristic to estimate my confidence in the parse, and if I'm not certain of it, I display a form containing the individual components to the user for verification. (OK, so I lied about users never seeing the individual components. Outlook users will recognize this technique.)

Well, I've gone on probably too long about all this, but the point is that there can be good reasons to keep composite or redundant columns in a table, but you should have the experience to realize what the consequences will be and how to mitigate them before you choose to do this. Rick Sprague

 

[JeremyNYC]

Absolutely, Rick.

To the original poster: if you're going to do as thorough a job as Rick has described, and do it knowing how much work it will be to set it up, then there are times when the return on your investment will be worth it to you. Rick knows why he is breaking the rules, and is willing to do the work, because he gets something valuable to him and, more importantly, his client's customers.

Make decisions like that when it is time. But do be careful about it, and know the risks from which you'll have to insulate yourself.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[JohnHarkins]

A suggestion:

I use "honorific" (sister, pastor, etc.), "degree" (MD, PhD, etc.), "generation" (Junior, Senior, III, IV), as a way of handling the oddities. It's surprising how effective it can be to break a name into these component parts on the way into the database, instead of trying to work around the ambiguous stuff later on. If the database is under a couple of hundred records, who cares (you might also consider a spreadsheet), but if you're serious about maintaining a growing (or frequently changing) list of entries, it's far better to invest the time in the first place.

Regards,
John Harkins