Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combine 10 fields into 1.

    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.

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combine 10 fields into 1.

    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.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine 10 fields into 1.

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

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combine 10 fields into 1.

    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.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine 10 fields into 1.

    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.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combine 10 fields into 1.

    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?

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine 10 fields into 1.

    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.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combine 10 fields into 1.

    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.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine 10 fields into 1.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •