Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    1

    Question Unanswered: concatenation of fields (multiple rows merged in one)

    Hi,

    When selecting the address of a customer, Access retrieves multiple rows. For example:

    [customid] , [address]
    john - street home
    john - company place
    john - phonenr home
    john - phonenr bussiness

    will - street home
    will - company place
    will - faxnr bussiness

    There are 2 tables, the customer table, and a table with address information. Customers and Addresses have a many-to-many relationship.
    When retrieving the addresses by customer. The result is as described above. What result I need is one record with a unique Customerid, with all the AddressInformation (the corresponding address fields merged) in one or more fields.
    So... an example:

    [customerid],[address],[address], [etc]
    john - street home, company place, phonenr home, phonenr bussiness

    The following is also a solution for my problem
    [customerid], [all addresses concateneted(merged) together]
    will - street home company place faxnr bussiness

    Does anyone know how to work this out?
    Many thanks in advance,
    rico

    p.s. I know this is against normalization rules, but I use this table for indexing so a boolean search can be done, the search engine can only find matches when data occurs in the same row.

  2. #2
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: concatenation of fields (multiple rows merged in one)

    If the two tables have a many to many relationship, without a link table, what are you doing about promary keys ...

    Where do you want this result to be displayed ....

    Could you give me an idea of your tables ... and the fields ... maybe a screenshop of ypur relationship diagram...

    Originally posted by ricopauw
    Hi,

    When selecting the address of a customer, Access retrieves multiple rows. For example:

    [customid] , [address]
    john - street home
    john - company place
    john - phonenr home
    john - phonenr bussiness

    will - street home
    will - company place
    will - faxnr bussiness

    There are 2 tables, the customer table, and a table with address information. Customers and Addresses have a many-to-many relationship.
    When retrieving the addresses by customer. The result is as described above. What result I need is one record with a unique Customerid, with all the AddressInformation (the corresponding address fields merged) in one or more fields.
    So... an example:

    [customerid],[address],[address], [etc]
    john - street home, company place, phonenr home, phonenr bussiness

    The following is also a solution for my problem
    [customerid], [all addresses concateneted(merged) together]
    will - street home company place faxnr bussiness

    Does anyone know how to work this out?
    Many thanks in advance,
    rico

    p.s. I know this is against normalization rules, but I use this table for indexing so a boolean search can be done, the search engine can only find matches when data occurs in the same row.
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

Posting Permissions

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