Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combining multiple records' field values (2003)

    I think I already know the answer to this question, but I would apreciate additional input.


    I am working with a data file from our records storage company. For some reason, probably a quirk in either the report, or there data entry methods, there are multiple records for each box in storage. The practical effect of this structure is that the "description" of the box in storage spans multiple records. I would like to gather all of this info into a single field.

    Example:

    Box ID Description
    1 text1
    1 text2
    1 text3
    ..... .....
    1 textn

    I want to convert this into a single record with a "description" field = text1 & text2 & text3 & .... & textn

    Can I do this with some sort of a query (I looked into it myself but I couldn't see any way) ? Or, will I have to write some code to run through the records and concatenate the field values myself ?

    Thanks for any comments and ideas

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combining multiple records' field values (2003)

    You could use the Concat function that is attached to <post#=301393>post 301393</post#>. You must copy that function into a module, you can then use it in a totals query that can act as the basis for an action query.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combining multiple records' field values (2003)

    Couldn't he run an update query?

    Update to: [text1] & " " & [text2] & " " [text3]
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combining multiple records' field values (2003)

    Only if there is a fixed number of descriptions per box.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combining multiple records' field values (2003

    Since the text are contained in the saem field, but diffferent records, I don't think it will work out that way.

    But, of course, I could be wrong.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combining multiple records' field values (2003

    So, I would start designing a "Totals" query, then in the line for "Total:" I could choose "Expression" and substitute my own expression (well, the author of concat()'s expression anyway,). Is that the way it's supposed to work? Sort of confusing to me for some reason.

    I just remebered that I have all the records to be concatenated contained in a subform, linked to a master record by the common ID field. Perhaps it would be more straightforward just to attach some code to that form to run through the records.

    Oh well. Instead of thinking about it, I'll get to work on the problem, and then see how it goes. Those fields aren't going to concatenate themselves.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combining multiple records' field values (2003

    One way would be to create a totals query grouped on Box ID, and with an expression using Concat to concatenate the Description field for each Box ID.

    Writing code to loop through the records would be another way (it's basically what Concat also does)

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combining multiple records' field values (2003

    The most *dirty* fix I see is to copy/paste (transpose) into excel...that should line up your records to concatenate across...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combining multiple records' field values (2003

    But if there are more than 256 records, paste/transpose would result in more than the 256 columns available in an Excel worksheet. 256 records isn't very much...

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combining multiple records' field values (2003

    Curses....foiled again. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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