Results 1 to 4 of 4
  1. #1
    ColinISL is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    3

    Concatenate Values from within the same column

    Hi all



    As I'm new to all this please stick with me.

    I've got to 2 columns in a table one is post code the other is owner. However, post code is duplicated but has a different owner eg

    Postcode Owner
    CVXX 1CC Coventry
    CVXX 1CC Birmingham
    CVXX 1CC Warwick

    What I'd like to do is concatenate up the owner and put it into a new table of unique postcodes with its owner eg

    Postcode Owner
    CVXX 1CC Coventry, Warwick, Birmingham

    Can this be done through SQL or would it require some sort of VBA macro in Access

    I'm working in Access 2007, any help would be appreciated.

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    How can a postcode relate to 3 different areas?

  3. #3
    ColinISL is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    3
    The postcodes appear in several regions as the data is a list of postcodes within a radius of the area and some of the areas overlap therefore giving the same postcode in multiple region / owner names.

  4. #4
    ColinISL is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    3
    Hi I've developed the following code to solve my problem, see below. However it takes a huge amount of time to run through does anyone have any tips on how I can speed up the process?

    Sub test2()

    Dim dbs As Database, rst As Recordset, rst2 As Recordset
    Set dbs = CurrentDb

    Dim strResult As String
    Dim Query2 As String
    Dim MyQuery As String
    Dim Postcode As String
    Dim qryString As String, MyOwner As String


    qryString2 = "SELECT distinct COM_UNIT FROM [Copy Of ORD-39270 Postcode List]"
    qryString = "SELECT * FROM [Copy Of ORD-39270 Postcode List]"

    Set rst = dbs.OpenRecordset(qryString2)
    Set rst2 = dbs.OpenRecordset(qryString)

    rst.MoveFirst
    Do While Not rst.EOF

    Postcode = rst.Fields("COM_Unit")

    rst2.MoveFirst

    qryString = "SELECT * FROM [Copy Of ORD-39270 Postcode List] where com_unit = '" & Postcode & "'"

    Set rst2 = dbs.OpenRecordset(qryString)

    Do While Not rst2.EOF
    Postcode2 = rst2.Fields("COM_Unit")

    If Postcode2 = Postcode Then
    strResult = strResult & " - " & rst2.Fields("Owner")
    End If

    rst2.MoveNext

    Loop
    MyOwner = 0

    Query2 = "UPDATE [Copy Of ORD-39270 Postcode List] as q SET q.[Owner 4] = '" & strResult & "' Where q.com_Unit = '" & Postcode & "'"
    dbs.Execute Query2
    rst.MoveNext

    strResult = ""

    Loop

    rst.Close
    db.Close

    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-21-2009, 06:52 AM
  2. Lookup values in one column from another record
    By cjayjones in forum Queries
    Replies: 16
    Last Post: 08-05-2009, 02:27 PM
  3. Concatenate Formula
    By Shingo in forum Programming
    Replies: 1
    Last Post: 07-25-2009, 09:14 AM
  4. Need Code to Concatenate All Records
    By menntu in forum Programming
    Replies: 4
    Last Post: 06-05-2009, 09:43 AM
  5. inserting values in column based another column
    By wasim_sono in forum Database Design
    Replies: 1
    Last Post: 06-27-2006, 05:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums