Combine four fields into one.

  • Thread starter Thread starter Oleg
  • Start date Start date
O

Oleg

Hi,

I can't seem to find a way to do this. I have a large database that
has over 20 fields but I would like to figure out a way to combine the
contents of 4 fields into one so that I can index it in a form in order
to jump to that record.

Example:

Field 1: Year - 06
Field 2: Source - ABC
Field 3: Area - IT
Field 4: Part - 2c

I want another field to auto combine the contents of the other four for
each new record.

06-ABC-IT-2c
06-ABC-IT-2d
06-ABC-IT-2e
06-XYZ-BC-2a
....

The field will be unique because the first three fields will repeat but
the Part field will be unique for each repetition. So on the entry
form I want to index this new field and the user will be able to
quickly jump to the desire record. This field does not have to be a
primary key, as I already have a record number ID field.

Any help is appreciated. Thanks!
....
 
I don't think you would be able to store this data as a separate,
conglomerate field in a table unless you constructed the value
piecemeal--manually or programatically, (via a form, perhaps)--each time a
new record was entered.

However, a query would be perfectly suited for abstracting out this type of
information. Create a query that includes the fields Year, Source, Area, and
Part, but uncheck the "show" box. Then create a fifth field, as follows:

Field5:[Year] & "-" & [Source] & "-" & [Area] & "-" & [Part]

If you select "Ascending" in the sort box of the query, it should sort the
resulting fields the way you want them. As far as creating a formal
index--if that's what you need--I don't know.

Hope that helps.
 
Do not do this in your table. It will create more problems that it will
solve. The better technique is base your form on a query based on the table.
Then in the query, create a calculated field and concatenate the other
fields in that field:

MyFindField: [Field1] & "-" & [Field2] & "-" & [Field3] & "-" & [Field4]

Now you can do your positioning using the concatenated field.
 
Klatuu said:
Do not do this in your table. It will create more problems that it will
solve. The better technique is base your form on a query based on the table.
Then in the query, create a calculated field and concatenate the other
fields in that field:

MyFindField: [Field1] & "-" & [Field2] & "-" & [Field3] & "-" & [Field4]

Now you can do your positioning using the concatenated field.

Oleg said:
Hi,

I can't seem to find a way to do this. I have a large database that
has over 20 fields but I would like to figure out a way to combine the
contents of 4 fields into one so that I can index it in a form in order
to jump to that record.
Click to expand...
Click to expand...


Thanks guys, but how can I index this in a form if it is just a query?
 
Sort the query on the calculated field.
As to adding new records, you will need to requery the form, but you would
have to do that if you were using a table anyway to see the new records.

Oleg said:
Do not do this in your table. It will create more problems that it will
solve. The better technique is base your form on a query based on the table.
Then in the query, create a calculated field and concatenate the other
fields in that field:

MyFindField: [Field1] & "-" & [Field2] & "-" & [Field3] & "-" & [Field4]

Now you can do your positioning using the concatenated field.

Oleg said:
Hi,

I can't seem to find a way to do this. I have a large database that
has over 20 fields but I would like to figure out a way to combine the
contents of 4 fields into one so that I can index it in a form in order
to jump to that record.
Click to expand...
Click to expand...


Thanks guys, but how can I index this in a form if it is just a query?
Click to expand...
 
Back
Top