Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Unanswered: Combine multiple records into one record

    Hi - Here is my scenario. Sometimes we use multiple parts to repair on one order. I need to make one record using the order# for grouping and then have each part used in a different column in the one record. Let me give you an example:

    query pulls 3 records containing parts used on this order#

    order# part# part description
    12345 3-A987 Cover
    12345 8-324 Rotor
    12345 2-H765 Tightener

    Should end up like this

    order# part#1 part desc1 part#2 part desc2 part#3 part desc3
    12345 3-A987 Cover 8-324 Rotor 2-H765 Tightener

    Actually, it would continue until the last record for that order#. I am not sure at all as to how to tackle this problem. Any suggestion is appreciated.

    Thanks,
    B&R

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Are you wanting to pull the fields together into a query field, or concatenate the records into a textbox or something? If the later, I would loop through a query recordset.
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why on earth would you want to do that?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    I've gotten really bad data before. So I had to create some code to loop through applicable fields and concatenate into a single memo field. Had to do it once a month while importing. The problem was didn't know how many records there would be, so had to loop through to find out how many, couldn't just do three or four.
    Me.Geek = True

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im with Startrekker merging stuff from multiple rows into one row sounds a baaaad idea. its near incomprehensible to users, it makes extracting that data else where tricky.

    is this a hard and fast user requirement or a way you are approaching a problem. ie is this requirement imposed on you.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Posts
    139
    The reason I need to combine these rows into one is because the customer is requiring this format in their report. It will be excel and it has the different fields for each part used and a field for the description of each part. I do not like it either obviously as I do not know how to do it. I would like to use a query that I can export in excel.

    Any help is appreciated!
    B&R

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If it was me, I would just tell the customer that it's impossible and they should find a more presentable method of displaying required data. I would then sketch out a report/subreport showing them the "normal" way to do it.

    There is no way in hell I would actually go ahead with this and in fact, I refuse to help anyone else walk straight down the path that leads to the dark side.

    What happens if there are 50 parts on an order???
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by StarTrekker
    There is no way in hell I would actually go ahead with this and in fact, I refuse to help anyone else walk straight down the path that leads to the dark side.
    But haven't you heard? The dark side has cookies.
    Me.Geek = True

  9. #9
    Join Date
    May 2006
    Posts
    178
    In my humble opinion. keep it just use 3 tables.

    1.tblOrder
    2.tblOrderDetails
    3.tblPartCat.

    Then when you export it to excel, create a pivot table.



    Regards

    aboo

Posting Permissions

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