Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Question Unanswered: Concatenate related records in a access table?

    Hi there,

    I am new to the forum and looking for some assistance please.

    I have an access table that has been imported straight from Excel. The table has records that should really be related through 2 tables, but they are all in the one table at the moment.

    The table contains individual property lists and each property is allocated to a vehicle which goes to that property. I.e there is a [RoundNumber] field which details which vehicle visits the property. I also have a [BuildingNumber] field and a [StreetName] field which details the individual property number and street address for each record. The street address field may contain the same value. I also have a [NoItems] field which details the number of items to be picked up from that particular property.

    What I want to do is identify all the records with matching [RoundNumber] and [StreetAddress] values and for each record that matches I want to concatenate the [BuildingNumber] data and separate in by a comma into a separate field called [HouseNoList]. I also want to total the number of items [NoItems] field to detail the total number of items to be picked up for that particular street. I then want to transfer the concatenated records to another table within MS Access.

    Can anyone advise me of the best way to do this in Access?

    Any help is much appreciated.

    Regards
    Davie
    Last edited by yackamd; 03-30-08 at 10:24.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you provide some sample data and epected output for clarification please?
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2008
    Posts
    4

    Sample Data

    Hi

    Thanks for your response.

    Sample data below:

    Existing Table data
    [RoundNumber] [BuildingNumber] [StreetName] [NoItems]
    [1] [23] [John Street] [1]
    [1] [44] [John Street] [2]
    [1] [34] [Kerr Street] [1]
    [1] [54] [Kerr Streer] [1]
    [2] [48] [High Street] [1]
    [2] [57] [High Street] [4]
    [2] [68] [High Street] [1]
    [3] [3A] [Main Street] [2]
    [3] [32] [Main Street] [2]
    [3] [67] [Main Street] [1]

    Expected Data Output
    [RoundNumber] [HouseNoList] [StreetName] [NoItems]
    [1] [23, 34, 44] [John Street] [3]
    [2] [48, 57, 68] [High Street] [6]
    [3] [3A, 32, 67] [Main Street] [5]


    Hope this helps.

    Cheers
    Davie
    Last edited by yackamd; 03-30-08 at 18:55.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sounds like a minor change to this:

    http://www.dbforums.com/showthread.php?t=1626996

    Should fit the bill?
    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

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nice link dump ST - you're clearly getting into the swing of things now, eh!

    Davie, if you have any specific problems or questions after following the solution in the linked post please feel free to post them back here

    Good luck!
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, but I still need to remember where a lot of other solutions are

    No doubt about it... I'm a dbforums junkie now ^^
    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

Posting Permissions

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