Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42

    Unanswered: Combine separate related records into one field

    Hi,

    I have this table and I want to combine the TSP_NBR into one field related to the project for reporting purposes only. (I wouldn't want to violate normalization rules) I only want to display these multiple records into one record by PROJ:

    PROJ TSP_NBR
    2748 31-52
    2748 31-(031)
    2748 31-(032)
    2748 31-(99186)

    like this:
    PROJ New TSP_NBR
    2748 31-52, 31-(031),31-(032),31-(99186)

    Can I do this with SQL?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't think you can do this in native SQL
    if its "just" in a report you could do this in a report..
    but someone else may have a smarter solution so I leave it be for a while

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't think pure SQL will work either. Here's a couple of functions:

    http://www.mvps.org/access/modules/mdl0004.htm

    http://support.microsoft.com/kb/210163/en-us
    Paul

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Another approach is to have a subreport that shows the TSP_NBR in multiple columns. The output might look like (assume - is a space):

    2748-----31-52-----31-(031)-----31-(032)-----31-(99186)
    ----------?????-----????????------????????-----???????????
    ----------?????-----????????------????????-----???????????

    2749-----?????-----???????--------??????-------???????

    etc.


    To set up columns for the subreport, got into Page Setup and click on the Columns tab.

Posting Permissions

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