How can I concatenate 2 FIELDS IN A NEW FIELD IN A TABLE

[Guest]

i need som help with this

 

[Guest]

Hi,
you wouldn't want to do this within the table since it would create
redundant data. If you want to display the two values together you can do so
at runtime in queries or on forms/reports with:

FullValue: [FirstField] & " " & [SecField]

HTH
Good luck

 

[Guest]

Your question is somewhat lacking in detail. If you want to populate a new
field and then delete the old fields then you can do it with an update query
before deleting the old fields. If you intend to keep the old fields then
don't create a new filed; it would create redundancy in the table and leave
the door open to update anomalies. Compute the new field on the fly in a
query.

If you are intending to delete the old fields the query would go along these
lines, once you've created the new field in able design view and saved the
new design. I'm assuming the fields are text and you want a space between
the two values if both field have a value:

UPDATE MyTable
SET MyNewField = (MyFirstOldField + " ") & MySecondOldField;

If you are keeping the old fields then a query to return the concatenated
values would go something like this:

SELECT SomeField, SomeOtherField,
(MyFirstOldField + " ") & MySecondOldField AS MyFullField
FROM MyTable;

By using the + operator in the parenthesised expression the redundant space
would be suppressed if MyFirstOldField is Null. This is because Nulls
propagate, i.e. Null + Anything = Null.

Ken Sheridan
Stafford, England

 

[Joseph Meehan]

i need som help with this

You would use an update query. You would create the new field in the
table first.

 

[Guest]

THANK
NO REALLY IS MOST SIMPLE I GET CONFUSED

I NEED CREATE A NEW CAMP

I HAVE A TABLE WIT REPETITIVE TEXT

BUT I NEED TO COMPOSE A NEW FIELD IN WHICH 3 FIELDS CONTRIBUTE
CAN I DO THIS IN THE SAME TABLE?

 

[Guest]

I don't have a good enough picture of what you want to do here, I'm afraid.
You'll need to provide a more detailed explanation if I'm going to be able to
advise further.

Ken Sheridan
Stafford, England

 

[Rick B]

Give us specifics please. What are the three fields? What is an example of
the three fields and the result you hope for.

As several people have pointed out, you should not do this in your table.

What if you have...

First: Susan
Middle: L
Last: Smith

and you want to create a new field called "Full" and populate it with "Susan
L Smith".

What happens when Susan gets married and changes her last name? You now
have TWO fields to fix.

Normally, you would not store calculated or concatenated values in a new
field. Instead, concatenate them in your queries, forms, or reports, but
leave the values separated in the table.

 

[Joseph Meehan]

You would use an update query. You would create the new field in
the table first.

I am sorry that I did not provide detailed information. It appears you
will need detailed information.

The problem is you question is a little vague so it is difficult to
understand the situation well enough to provide the information. In
addition I agree with the others on the issue of duplicating the information
in the table.

There are two likely situations. First, my assumption, was that you
want to combine two fields of text type data into a single new field and
then delete the first two fields. You would do this if you would not need
to take them apart later. The other possibility is you want to combine them
to use the combined field for some functions and still keep the others. You
don't really want to do this. You waste space, slow down Access and if any
of the original data were updated, the combined filed would not be
automatically updated.

 

[Guest]

thanks ken
see
i have 3 field all type text but are numbers really (in orden to include the
initial zeros)
field1 is number between 000000 and 999999, denotes de date (just numbers)
field 2 is number also betwen 0000 and 9999, denotes any of my cases
field 3 is number between 00 and 99, to explain de age
I can do a relationship like a code between all 3 fields, if that is
possible to the apear like a unique number in the same table o maybe in a
consult
well im not an expert nor acces neither englishh too, thats why y request
some help

 

[Guest]

THANK
YES IS NO MATTER IF THE DATE ARE REPETITIVE
SEE
I NEED A CREAR A CODE
CODE IS COMPOSED BY 3 DATA (THE 3 FIELDS) ALL THEY ARE NUMBER BUT ARE STORED
LIKE TEXT
FIRS IS BETWEEN 000000 999999
SECOND 0000 AND 9999
AND THE LAST 00 AND 99
I NEED CONCATENATE THEM TO APEAR LIKE A CODE IS NO MATTER IN THE SAME TABLE
OR ANOTHER TABLE
REALLY I NEED STORE DE DATA IN ACCES BUT COPY THEN IN EXCEL
IN EXCEL I CAN DO THAT (CONCATENATE) I WOULD MAKE THAT IN ACCES, THEN JUST
HAVE TO COPY AND PASTE AND NO USE THE FUNCCTION CONCATENATE

 

[Smartin]

I am sorry that I did not provide detailed information. It appears you
will need detailed information.

The problem is you question is a little vague so it is difficult to
understand the situation well enough to provide the information. In
addition I agree with the others on the issue of duplicating the information
in the table.

There are two likely situations. First, my assumption, was that you
want to combine two fields of text type data into a single new field and
then delete the first two fields. You would do this if you would not need
to take them apart later. The other possibility is you want to combine them
to use the combined field for some functions and still keep the others. You
don't really want to do this. You waste space, slow down Access and if any
of the original data were updated, the combined filed would not be
automatically updated.

Just playing devil's advocate here... mightn't there be a case for
storing redundant data? I'm not necessarily saying the OP should do
this, but wondering more generally if sometimes it is appropriate.

Imaginary scenario: Three fields are used to store three attributes of,
say, a part number. Some end users have frequent need to search on the
concatenated result of these fields and would never need to break down
their search to one or two attributes. Wouldn't storing the concatenated
result in a fourth (redundant) column speed up searches and simplify
queries?

The real life scenario I know of is an application database at work.
Some tables store names (as entered by the user) and redundant columns
with the name in upper case. According to the vendor this design speeds
up name searches.

On the other hand, the real life example is an Oracle DB and we can make
use of triggers to fill the redundant columns. I agree though that with
the absence of triggers, populating a redundant column automatically is
more complicated.

 

[Joseph Meehan]

Just playing devil's advocate here... mightn't there be a case for
storing redundant data? I'm not necessarily saying the OP should do
this, but wondering more generally if sometimes it is appropriate.

Imaginary scenario: Three fields are used to store three attributes
of, say, a part number. Some end users have frequent need to search
on the concatenated result of these fields and would never need to
break down their search to one or two attributes. Wouldn't storing
the concatenated result in a fourth (redundant) column speed up
searches and simplify queries?

The real life scenario I know of is an application database at work.
Some tables store names (as entered by the user) and redundant columns
with the name in upper case. According to the vendor this design
speeds up name searches.

On the other hand, the real life example is an Oracle DB and we can
make use of triggers to fill the redundant columns. I agree though
that with the absence of triggers, populating a redundant column
automatically is more complicated.

Frankly I don't know. The search issues are something I never had to
play with.

 

[Guest]

You don't need to create another field in the table, and should not do so.
If you do and concatenate the 3 values into it, then one of the values could
be changed, leaving the concatenated value unchanged. This is what's called
an 'update anomaly'. To make sure that the combination of the 3 values is
always unique you can create an index on the 3 fields. This can be done in
one of two ways:

1. Make the 3 fields the primary key of the table. To do this highlight
all 3 fields in table design and click the Primary Key button on the toolbar.

2. Create a unique index on the 3 fields. To do this select Indexes from
the View menu in table design view. In the Index Name column of the dialogue
enter a suitable name for the index. Select Yes for the Unique property in
the options below. In the Field Name column of the same line select Field 1,
on the next line, Field 2 and on the next line Field 3. In lines 2 and 3
leave the Index name blank.

If you need to relate another table to this table with a foreign key column,
then I would recommend you use option 2 above and add an autonumber field to
the table as its primary key. This will automatically be given unique
values. In another table a foreign key column of long integer number data
type can then reference the autonumber primary key.

Ken Sheridan
Stafford, England