Add concatenated field to a Table

[Bill Davis]

Add concatenated field to a Table
I have 3 fields on a form that is used to add new items to the database.
I have a form that is used to add new items to a database. I have 3
fields that I have concatenated in a field on the form. What I need to
know is how can I get the concatenated field to be entered in to the
table when you save the entry.
Thanks

 

[Van T. Dinh]

Since you have 3 different values before concatenation and presumably each
of these values has some meaning, I think it may be more efficient (and more
structurally sound in terms of database design) to store them as 3 separate
values in your Table rather than 1 combined value???

Perhaps, you should describe what each of these values means and why you
want to store the concatenated value as 1 Field rather than 3.

 

[Bill Davis]

Van
The three fields are components of a billing system and are part of a
one to many relationships. The concatenation is used to create a unique
fields so that when the monthly upload is being done any issue that has
not been resolved will not be added again.. The only issue is when a
user enters a record manually if the make a mistake when doing the
concatenation it could cause a problem with revenue because it could be
counted more than once. So, if the concatenation is done automatically
then less more for error. I hope this help
Bill

 

[Van T. Dinh]

Do you know that you can create a unique index or even a Primary Key on
multiple Fields in an Access Table? This should eliminate the duplicate
problem.

When the concatenation is required for upload, e.g. the "other" system
requires the concatenated values, you can use code to do "automatic"
concatenation, i.e. without the user's intervention from the 3 component
Field values ... Thus, there shouldn't be any need for the user to do
manual concatenation ...

There are advantanges in storing them as separate values and only
concatenate then when required, e.g. you may want to select records by one
or two component values. If you store them as separate values, it is an
easy query. In you store these values as a concatenated value, you need to
break them down to component values and concatenation is always easier than
resolving 1 value to different components. That why Database Design Theory
requires that "Field value must be atomin", i.e. each Field value stores
onlu ONE item of data. In your case, you are trying to store 3 items of
data as 1 concatenated value!

My suggestion, if each of the components has a meaning of its own, store
then as a separate value in a separate Field and work out the rest, e.g.
concatenation for upload later ...

 

[Bill Davis]

Do you know that you can create a unique index or even a Primary Key on
multiple Fields in an Access Table? This should eliminate the duplicate

Van,

I think I may have not clear with my explanation. When the Monthly
upload is done the 3 fields are concatenated to create the Primary Key
and this does stop duplicate from being entered on the upload. The 3
fields are kept separate because all of the issues might not be resolved
at the same time.

The problem is when an issue is entered manually. Once the user has
entered the 3 fields it is displayed as the concatenated fields but I
can not get it to enter into the Primary Key field.

 

[Van T. Dinh]

Bill

You are trying to store calculated values in your Table which is prohibited
by the Relational Database Design Theory and that's why Access makes it
harder to store the calculated (concatenated) values - even though it is
still possible.

I wrote in my last reply:

"When the concatenation is required for upload, e.g. the "other" system
requires the concatenated values, you can use code to do "automatic"
concatenation, i.e. without the user's intervention from the 3 component
Field values ... Thus, there shouldn't be any need for the user to do
manual concatenation ... "

which is exactly what you need ... For example, if you call the Fields F1,
F2 & F3 (with a PK or a unique index defined on the 3-Field combination),
when you want to do the monthly upload, simply create a Query with a
calculated Field like:

PKForOtherSystem: F1 & F2 & F3

then upload (the data from) the Query to the other system and you have the
concatenated values uploaded!

Perhaps, a quick reading of a RDDT book will help you why I have been
advisng that you shouldn't store calculated values. Basically, storing
calculated values is a huge potential for inconsistencies in your database
and it slows the processing down also ...