Combining the data in three table fields

[timbar]

I have a table that has 3 columns. I'd like to be able to combine the data included in the 3 columns into one of those columns, or create a fouth field with the combined data.

An example would be to combine address information stored in three fields into one field. Although this is not my specific application, it illustrates what I'd like to do.

If the first column is the Street Address (text), the second column is the City Address (text), and the third column is the Zip code (integer number), can I combine the data in a new fourth column which contains the combined set of data (street, city and zip)?

I think I can do this in a form or report (as indicated by Access Help - Combining text values from multiple fields on a form, report, or data access page), but I'd like to combine the 3 columns of data in a table (either directly in the table somehow, or by using a query) to produce the new table field.

Thanks in advance for your help.

Tim Barbour
timbar@att.net

 

[AccessAce]

First, create the new field as type text and a size that will ft the concantenation of the fields.

Then, create an update query that inserts the this expression:
[Field1] & " " & [Field2] & " " & [Field3]

into that new field.
Field1: "City"
Field2: "ST"
Field3: 12345-0001
Field4: "City ST 12345-0001"

 

[RickSpr]

AccessAce's answer does what you asked for, but are you sure you want to do this? If you retain the original columns and the new column, you've made updating the address difficult (because any change has to be made in both the original column and the new column). On the other hand, if you delete the original columns, you can no longer use them for sorting and searching--at least not as easily.

It's usually better to concatenate fields for display only (in a form or report) but to keep them separate in the database. The only time you should permanently combine them is if you're certain you'll never be interested in the individual components, separately.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein