Populating a field with a concatenation of 2 other fields

[Brian Beck]

I have a table (tIncidentFromForms) and I'm dealing with 3 fields within
that form: CDC1, CDC2, and CountyDistrictNum. CDC1 and CDC2 both have data
in them and I would like to concatenate the data in both of those fields and
put it in CountyDistrictNum. Essentially I want:

[tIncidentFromForms]![CountyDistrictNum] = [tIncidentFromForms]![CDC1] &
[tIncidentFromForms]![CDC2]

for every record that is in tIncidentFromForms.

What is the best way for me to go about this? A macro? A module? I've
gone in and done it all by hand before, but I know there has to be an easier
way.

-Brian Beck

 

[BruceM]

Best way is not to do it. Concatenate on the fly in a form, report, or
query when you need to view the combined result.

 

[Tom Ellison]

Dear Brian:

For almost all purposes, Bruce is right on.

There are two exceptions: You want a snapshot in time that shows what was
in CDC1 and CDC2 at one time, even though they may have changed. Then you
must concatenate and save them now, before they change again. It is
unusual, but not unheard of, to need this.

The other exception is that you need to sort the table on this. Assuming
they're both strings, you could just sort on each in order and get that
effect. But if they are of different types, and you want them sorted by
their combined string value, then the sort may not work the same on them
separately.

Again, you could do this dyanamically in the query in the sorting of that
query without sorting. But, in even rarer instances, you may need an index
on the combination for performance purposes.

In this case, you should switch from Jet to MSDE where you can create a
calculated column and where you can build an index on that. When the value
of CDC1 or CDC2, the calculated column would instantly and automatically
change as well, and the index be modified accordingly.

Except for the first case above, there are better ways to do this. What you
suggest will not track any changes to the CDC1 and CDC2 columns. If you
want it to track, choose another way.

Tom Ellison

Best way is not to do it. Concatenate on the fly in a form, report, or
query when you need to view the combined result.

I have a table (tIncidentFromForms) and I'm dealing with 3 fields within
that form: CDC1, CDC2, and CountyDistrictNum. CDC1 and CDC2 both have
data in them and I would like to concatenate the data in both of those
fields and put it in CountyDistrictNum. Essentially I want:

[tIncidentFromForms]![CountyDistrictNum] = [tIncidentFromForms]![CDC1] &
[tIncidentFromForms]![CDC2]

for every record that is in tIncidentFromForms.

What is the best way for me to go about this? A macro? A module? I've
gone in and done it all by hand before, but I know there has to be an
easier way.

-Brian Beck

 

[Brian Beck]

Immediately after posting this it occurred to me that I'm wanting to do
something I should really never do, i.e. duplicate information in my
database. So let me explain the whole situation (why didn't I do that to
begin with?) and see if you have any ideas.

In addition to the table tIncidentFromForms I also have a table
tOrgDistrict. That table is indexed on a field called CountyDistrictNum. I
need to be able to create a relationship between tIncidentFromForms and
tOrgDistrict because I need to run a query that will grab information from
both tables. If I can create the field CountyDistrictNum in
tIncidentFromForms then the relationship is easy to establish and my query
runs fine. Unfortunately, there is no other field in tOrgDistrict that is
unique so that is what I have to base this relationship on.

Knowing that, how can I best go about creating this relationship so that I
can run my query?

My query is as follows (if it's important to know that in figuring this all
out):
SELECT tIncidentFromForms.DistrictName, tOrgDistrict.SuperintendentFullName,
tOrgDistrict.StreetAddress, tOrgDistrict.City, tOrgDistrict.State,
tOrgDistrict.ZipCode, tIncidentFromForms.CampusName,
tIncidentFromForms.Involved1FirstName, tIncidentFromForms.Involved1LastName,
tIncidentFromForms.Involved2FirstName, tIncidentFromForms.Involved2LastName,
tIncidentFromForms.Administration, tIncidentFromForms.Year,
tIncidentFromForms.Grade, tIncidentFromForms.TestType,
tIncidentFromForms.Subject, tIncidentFromForms.Description,
tIncidentFromForms.DTCFullName
FROM tOrgDistrict RIGHT JOIN tIncidentFromForms ON
tOrgDistrict.CountyDistrictNum = tIncidentFromForms.CountyDistrictNum
ORDER BY tIncidentFromForms.DistrictName;

Dear Brian:

For almost all purposes, Bruce is right on.

There are two exceptions: You want a snapshot in time that shows what was
in CDC1 and CDC2 at one time, even though they may have changed. Then you
must concatenate and save them now, before they change again. It is
unusual, but not unheard of, to need this.

The other exception is that you need to sort the table on this. Assuming
they're both strings, you could just sort on each in order and get that
effect. But if they are of different types, and you want them sorted by
their combined string value, then the sort may not work the same on them
separately.

Again, you could do this dyanamically in the query in the sorting of that
query without sorting. But, in even rarer instances, you may need an
index on the combination for performance purposes.

In this case, you should switch from Jet to MSDE where you can create a
calculated column and where you can build an index on that. When the
value of CDC1 or CDC2, the calculated column would instantly and
automatically change as well, and the index be modified accordingly.

Except for the first case above, there are better ways to do this. What
you suggest will not track any changes to the CDC1 and CDC2 columns. If
you want it to track, choose another way.

Tom Ellison

Best way is not to do it. Concatenate on the fly in a form, report, or
query when you need to view the combined result.

I have a table (tIncidentFromForms) and I'm dealing with 3 fields within
that form: CDC1, CDC2, and CountyDistrictNum. CDC1 and CDC2 both have
data in them and I would like to concatenate the data in both of those
fields and put it in CountyDistrictNum. Essentially I want:

[tIncidentFromForms]![CountyDistrictNum] = [tIncidentFromForms]![CDC1] &
[tIncidentFromForms]![CDC2]

for every record that is in tIncidentFromForms.

What is the best way for me to go about this? A macro? A module? I've
gone in and done it all by hand before, but I know there has to be an
easier way.

-Brian Beck

 

[Tom Ellison]

Dear Brian:

I'm glad to see you don't really want to duplicate this.

Now, are these two really separate quantities? If they are, then why not
split them in the other table where they're already joined.

The database design rule about atomicity should apply uniformly. Make
sense?

If, in your opinion, the two together are really one value, join them
together and remove the two separate columns.

If they are two values, split them in the other table and remove the one
column there.

Until you do one or the other (and correct all the forms and reports that
depend on the old way) you can still JOIN on the combination, but not so
well. Indexing won't support the performance of the JOIN as it could.
Also, you won't be able to properly enforce the relationship.

Tom Ellison

Immediately after posting this it occurred to me that I'm wanting to do
something I should really never do, i.e. duplicate information in my
database. So let me explain the whole situation (why didn't I do that to
begin with?) and see if you have any ideas.

In addition to the table tIncidentFromForms I also have a table
tOrgDistrict. That table is indexed on a field called CountyDistrictNum.
I need to be able to create a relationship between tIncidentFromForms and
tOrgDistrict because I need to run a query that will grab information from
both tables. If I can create the field CountyDistrictNum in
tIncidentFromForms then the relationship is easy to establish and my query
runs fine. Unfortunately, there is no other field in tOrgDistrict that is
unique so that is what I have to base this relationship on.

Knowing that, how can I best go about creating this relationship so that I
can run my query?

My query is as follows (if it's important to know that in figuring this
all out):
SELECT tIncidentFromForms.DistrictName,
tOrgDistrict.SuperintendentFullName, tOrgDistrict.StreetAddress,
tOrgDistrict.City, tOrgDistrict.State, tOrgDistrict.ZipCode,
tIncidentFromForms.CampusName, tIncidentFromForms.Involved1FirstName,
tIncidentFromForms.Involved1LastName,
tIncidentFromForms.Involved2FirstName,
tIncidentFromForms.Involved2LastName, tIncidentFromForms.Administration,
tIncidentFromForms.Year, tIncidentFromForms.Grade,
tIncidentFromForms.TestType, tIncidentFromForms.Subject,
tIncidentFromForms.Description, tIncidentFromForms.DTCFullName
FROM tOrgDistrict RIGHT JOIN tIncidentFromForms ON
tOrgDistrict.CountyDistrictNum = tIncidentFromForms.CountyDistrictNum
ORDER BY tIncidentFromForms.DistrictName;

Dear Brian:

For almost all purposes, Bruce is right on.

There are two exceptions: You want a snapshot in time that shows what
was in CDC1 and CDC2 at one time, even though they may have changed.
Then you must concatenate and save them now, before they change again.
It is unusual, but not unheard of, to need this.

The other exception is that you need to sort the table on this. Assuming
they're both strings, you could just sort on each in order and get that
effect. But if they are of different types, and you want them sorted by
their combined string value, then the sort may not work the same on them
separately.

Again, you could do this dyanamically in the query in the sorting of that
query without sorting. But, in even rarer instances, you may need an
index on the combination for performance purposes.

In this case, you should switch from Jet to MSDE where you can create a
calculated column and where you can build an index on that. When the
value of CDC1 or CDC2, the calculated column would instantly and
automatically change as well, and the index be modified accordingly.

Except for the first case above, there are better ways to do this. What
you suggest will not track any changes to the CDC1 and CDC2 columns. If
you want it to track, choose another way.

Tom Ellison

Best way is not to do it. Concatenate on the fly in a form, report, or
query when you need to view the combined result.

I have a table (tIncidentFromForms) and I'm dealing with 3 fields within
that form: CDC1, CDC2, and CountyDistrictNum. CDC1 and CDC2 both have
data in them and I would like to concatenate the data in both of those
fields and put it in CountyDistrictNum. Essentially I want:

[tIncidentFromForms]![CountyDistrictNum] = [tIncidentFromForms]![CDC1]
& [tIncidentFromForms]![CDC2]

for every record that is in tIncidentFromForms.

What is the best way for me to go about this? A macro? A module?
I've gone in and done it all by hand before, but I know there has to be
an easier way.

-Brian Beck

 

[BruceM]

The combination of CDC1 and CDC2 is guaranteed 100% to be unique? If so,
one option is to use a multi-field primary key. Another is to add an
autonumber PK field, or an incrementing number field (which would amount to
about the same thing as an autonumber). There are other possibilities, such
as using another form to enter the CDC1 and CDC2 values, and combining them
into a single field on the main form. If either field is a constant length
you can break them apart as needed to see them on the form (or report) as
two separate values.

Immediately after posting this it occurred to me that I'm wanting to do
something I should really never do, i.e. duplicate information in my
database. So let me explain the whole situation (why didn't I do that to
begin with?) and see if you have any ideas.

In addition to the table tIncidentFromForms I also have a table
tOrgDistrict. That table is indexed on a field called CountyDistrictNum.
I need to be able to create a relationship between tIncidentFromForms and
tOrgDistrict because I need to run a query that will grab information from
both tables. If I can create the field CountyDistrictNum in
tIncidentFromForms then the relationship is easy to establish and my query
runs fine. Unfortunately, there is no other field in tOrgDistrict that is
unique so that is what I have to base this relationship on.

Knowing that, how can I best go about creating this relationship so that I
can run my query?

My query is as follows (if it's important to know that in figuring this
all out):
SELECT tIncidentFromForms.DistrictName,
tOrgDistrict.SuperintendentFullName, tOrgDistrict.StreetAddress,
tOrgDistrict.City, tOrgDistrict.State, tOrgDistrict.ZipCode,
tIncidentFromForms.CampusName, tIncidentFromForms.Involved1FirstName,
tIncidentFromForms.Involved1LastName,
tIncidentFromForms.Involved2FirstName,
tIncidentFromForms.Involved2LastName, tIncidentFromForms.Administration,
tIncidentFromForms.Year, tIncidentFromForms.Grade,
tIncidentFromForms.TestType, tIncidentFromForms.Subject,
tIncidentFromForms.Description, tIncidentFromForms.DTCFullName
FROM tOrgDistrict RIGHT JOIN tIncidentFromForms ON
tOrgDistrict.CountyDistrictNum = tIncidentFromForms.CountyDistrictNum
ORDER BY tIncidentFromForms.DistrictName;

Dear Brian:

For almost all purposes, Bruce is right on.

There are two exceptions: You want a snapshot in time that shows what
was in CDC1 and CDC2 at one time, even though they may have changed.
Then you must concatenate and save them now, before they change again.
It is unusual, but not unheard of, to need this.

The other exception is that you need to sort the table on this. Assuming
they're both strings, you could just sort on each in order and get that
effect. But if they are of different types, and you want them sorted by
their combined string value, then the sort may not work the same on them
separately.

Again, you could do this dyanamically in the query in the sorting of that
query without sorting. But, in even rarer instances, you may need an
index on the combination for performance purposes.

In this case, you should switch from Jet to MSDE where you can create a
calculated column and where you can build an index on that. When the
value of CDC1 or CDC2, the calculated column would instantly and
automatically change as well, and the index be modified accordingly.

Except for the first case above, there are better ways to do this. What
you suggest will not track any changes to the CDC1 and CDC2 columns. If
you want it to track, choose another way.

Tom Ellison

Best way is not to do it. Concatenate on the fly in a form, report, or
query when you need to view the combined result.

I have a table (tIncidentFromForms) and I'm dealing with 3 fields within
that form: CDC1, CDC2, and CountyDistrictNum. CDC1 and CDC2 both have
data in them and I would like to concatenate the data in both of those
fields and put it in CountyDistrictNum. Essentially I want:

[tIncidentFromForms]![CountyDistrictNum] = [tIncidentFromForms]![CDC1]
& [tIncidentFromForms]![CDC2]

for every record that is in tIncidentFromForms.

What is the best way for me to go about this? A macro? A module?
I've gone in and done it all by hand before, but I know there has to be
an easier way.

-Brian Beck

 

[Brian Beck]

Yeah, it finally occured to me that time to concatenate this data together
was when I was grabbing it from the Word document initally, not after I
already had it in the database. I added a line to my Word macro and now it
concatenates CDC1 and CDC2 and then imports that into the database as
CountyDistrictNum. Works like a charm.

I should have known as soon as I started trying to duplicate data in my
tables that this was completely the wrong road to go down.

Thanks for the help!!

The combination of CDC1 and CDC2 is guaranteed 100% to be unique? If so,
one option is to use a multi-field primary key. Another is to add an
autonumber PK field, or an incrementing number field (which would amount
to about the same thing as an autonumber). There are other possibilities,
such as using another form to enter the CDC1 and CDC2 values, and
combining them into a single field on the main form. If either field is a
constant length you can break them apart as needed to see them on the form
(or report) as two separate values.

Immediately after posting this it occurred to me that I'm wanting to do
something I should really never do, i.e. duplicate information in my
database. So let me explain the whole situation (why didn't I do that to
begin with?) and see if you have any ideas.

In addition to the table tIncidentFromForms I also have a table
tOrgDistrict. That table is indexed on a field called CountyDistrictNum.
I need to be able to create a relationship between tIncidentFromForms and
tOrgDistrict because I need to run a query that will grab information
from both tables. If I can create the field CountyDistrictNum in
tIncidentFromForms then the relationship is easy to establish and my
query runs fine. Unfortunately, there is no other field in tOrgDistrict
that is unique so that is what I have to base this relationship on.

Knowing that, how can I best go about creating this relationship so that
I can run my query?

My query is as follows (if it's important to know that in figuring this
all out):
SELECT tIncidentFromForms.DistrictName,
tOrgDistrict.SuperintendentFullName, tOrgDistrict.StreetAddress,
tOrgDistrict.City, tOrgDistrict.State, tOrgDistrict.ZipCode,
tIncidentFromForms.CampusName, tIncidentFromForms.Involved1FirstName,
tIncidentFromForms.Involved1LastName,
tIncidentFromForms.Involved2FirstName,
tIncidentFromForms.Involved2LastName, tIncidentFromForms.Administration,
tIncidentFromForms.Year, tIncidentFromForms.Grade,
tIncidentFromForms.TestType, tIncidentFromForms.Subject,
tIncidentFromForms.Description, tIncidentFromForms.DTCFullName
FROM tOrgDistrict RIGHT JOIN tIncidentFromForms ON
tOrgDistrict.CountyDistrictNum = tIncidentFromForms.CountyDistrictNum
ORDER BY tIncidentFromForms.DistrictName;

Dear Brian:

For almost all purposes, Bruce is right on.

There are two exceptions: You want a snapshot in time that shows what
was in CDC1 and CDC2 at one time, even though they may have changed.
Then you must concatenate and save them now, before they change again.
It is unusual, but not unheard of, to need this.

The other exception is that you need to sort the table on this.
Assuming they're both strings, you could just sort on each in order and
get that effect. But if they are of different types, and you want them
sorted by their combined string value, then the sort may not work the
same on them separately.

Again, you could do this dyanamically in the query in the sorting of
that query without sorting. But, in even rarer instances, you may need
an index on the combination for performance purposes.

In this case, you should switch from Jet to MSDE where you can create a
calculated column and where you can build an index on that. When the
value of CDC1 or CDC2, the calculated column would instantly and
automatically change as well, and the index be modified accordingly.

Except for the first case above, there are better ways to do this. What
you suggest will not track any changes to the CDC1 and CDC2 columns. If
you want it to track, choose another way.

Tom Ellison


Best way is not to do it. Concatenate on the fly in a form, report, or
query when you need to view the combined result.

I have a table (tIncidentFromForms) and I'm dealing with 3 fields
within that form: CDC1, CDC2, and CountyDistrictNum. CDC1 and CDC2
both have data in them and I would like to concatenate the data in both
of those fields and put it in CountyDistrictNum. Essentially I want:

[tIncidentFromForms]![CountyDistrictNum] = [tIncidentFromForms]![CDC1]
& [tIncidentFromForms]![CDC2]

for every record that is in tIncidentFromForms.

What is the best way for me to go about this? A macro? A module?
I've gone in and done it all by hand before, but I know there has to
be an easier way.

-Brian Beck