Combine four fields into one.

[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!
....

 

[ndalton]

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.

 

[Klatuu]

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.

 

[ndalton]

Yeah...what he said.^^

 

[Oleg]

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.

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.

Thanks guys, but how can I index this in a form if it is just a query?

 

[Klatuu]

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.

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.

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.

Thanks guys, but how can I index this in a form if it is just a query?