combine 10 fields into 1.

Topic

New Reply

I have a database where I have 10 fields of information I want to combine. Is there anyway can copy all 10 fields into a new db all of it going into 1 field?

I am using MS Access 97.

Reply | Quote

Replies

You should be able to use an append or make table query to concatenate your 10 fields into one. But I would have to ask why you want to store more than one value in a field? Example of concatenation expression.

[FieldName] & ” ” & [FieldName] & ” ” & [FieldName] & ” ” & [FieldName] & ” ” & [FieldName] etc.

Reply | Quote

Use a make table query, calculating [field1]&[field2]&[field3]&… as the single output field. Have the table made in a new database.

Reply | Quote

I hate working on someone else’s projects. Started to try this out but I ran into a problem. I didn’t notice but the fields they created are check boxes.

So let me rephrase it. How can I combine 10 fields with check box’s into a field that will have text data? If PCLAN is checked how could I have it say PCLAN (or something like it) in the new db?

Might be better to trash it and start over.

Reply | Quote

Don’t trash it yet! This looks like a series of IIF statements could save your day.

Set up a query to analyze your existing table, using something like this:
IIF([Tablename]![PCLAN]=TRUE,”PCLAN”,””)

Do this for each of the 10 fields in your [Tablename]. Then concatenate the ten results and export to your new table.

Reply | Quote

Sorry, that is just over my head. Would I go to the field line or the Criteria line to enter this? Also what is concatenate the results mean and how do I do that?

Reply | Quote

The check boxes in your original table equate to “TRUE” if checked, or “FALSE” if unchecked. This allows the IIF function to evaluate whether the box is checked or not. If the box is checked, the formula will then return the “#text#” entered (“PCLAN” in the previous example), or an empty string (“”) if the box is not checked.

The formula is entered in the “Field:” cell in the query design template; it is not a criteria.

Concatenating is just a fancy word for stringing together the values from a number of different fields into one field. You would do this using a formula such as:
Field1&”, “&Field2&”, “&Field3″…
This will give you the values calculated by your IIF statements as a single value, with each portion separated by a comma and space.

Reply | Quote

I am trying to combine this information into a new field. In the Expression Builder I can use this line.
Expr1: IIf([dbPCon]![mfdisk]=True,”Mainframe to Disk”)

But if I try to add a second or so forth I can get it to work. I also tried:
Expr1: IIf([dbPCon]![mfdisk]=True,”Mainframe to Disk”)and IIf([dbPCon]![PCMF]=True,”PC to Mainframe”)

and
Expr1: IIf([dbPCon]![mfdisk]=True,”Mainframe to Disk”)or IIf([dbPCon]![PCMF]=True,”PC to Mainframe”)

None seem to work.

Reply | Quote

You need to add a value for IIF to report if the condition is not true. The IIF() function requires three parts:
IIF(Condition,Value_if_True,Value_if_False).

Next, develop your conversion in layers. It would be simpler if you evaluated each field separately, and then combined the results generated. The first layer is your source data ([dbPCon]). The second layer (query #1) is converting your check boxes to text values, using a separate IIF statement for each field. The third layer (query #2) is combining the results of the second layer into one field.

Reply | Quote