Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Southern California
    Posts
    13

    Question Unanswered: Multiple fields combined into one field, separate records by commas

    I'm working on a conference database with Speaker and Seminar tables in a many-to-many relationship. I'm trying to create a report with a master schedule showing titles, times and speakers.

    Right now it looks like this:
    The First Two Year of Urban Ministry 3-4pm
    Job Lara
    Larry Acosta
    Marvin Daniels.

    This approach makes the speaker name the "detail in the report". It takes up at least 2-4 lines per seminar. It makes the report very long (10 pages), and difficult to use. I want to be able to put each seminar on only one line (which would make the report only 2-3 pages) like this:

    The First Two Years of Urban Ministry 3-4pm Job Lara, Larry Acosta, Marvin Daniels

    The trick will be to get the multiple speaker fields to merge into one field, separated by commas.

    Any suggestions?

  2. #2
    Join Date
    Feb 2004
    Location
    Houston TX
    Posts
    23
    Mircosoft Access is a very hany tool in terms of joining fields or concatination. once you have your data source identified in the report, all you need to know are the field names that you want to show togather in the detailed section.
    For example i want to join

    Seminar_desc
    Speaker_Name
    Seminar_time
    Seminar_duration

    a total of 4 fields in one line on the detailed section of the report.
    click on the toolbox if it is not already open. From the tool box click on the text box button, then click an drag it a reasonable size in the detail section of your report (i forgot to mention, you have to be in the design mode of your report to do all this). You can get rid of the label part of the text box as you wouldn't be needing it any longer.
    Now on the other section it should be showing "unbound".
    that means so far so good.
    Now click inside of that textbox and type this exactly for the four fields shown above.

    =[Seminar_desc]&" "&[Speaker_Name]&" "&[Seminar_time]&" "&[Seminar_duration]

    if you dont need any spaces between the four fields the following also works but not recommended

    =[Seminar_desc]+[Speaker_Name]+[Seminar_time]+[Seminar_duration]

    Hope it helps
    Believe Everybody Trust Nobody

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Multiple fields combined into one field, separate records by commas

    Originally posted by JHerrick79
    I'm working on a conference database with Speaker and Seminar tables in a many-to-many relationship. I'm trying to create a report with a master schedule showing titles, times and speakers.

    Right now it looks like this:
    The First Two Year of Urban Ministry 3-4pm
    Job Lara
    Larry Acosta
    Marvin Daniels.

    This approach makes the speaker name the "detail in the report". It takes up at least 2-4 lines per seminar. It makes the report very long (10 pages), and difficult to use. I want to be able to put each seminar on only one line (which would make the report only 2-3 pages) like this:

    The First Two Years of Urban Ministry 3-4pm Job Lara, Larry Acosta, Marvin Daniels

    The trick will be to get the multiple speaker fields to merge into one field, separated by commas.

    Any suggestions?
    If there are many speakers for a given seminar, you may want to try a subreport with snaking columns to get them on one line. You would link the reports just as you do subforms to parent forms. If this sounds like something you would want to do, you can access the colums stuff by selecting page setup from the file menu and going to the columns tab. After that it's just an experiment to get what you want.

    Just an idea, but there may be a better one.

    Gregg

  4. #4
    Join Date
    Feb 2004
    Location
    Southern California
    Posts
    13

    Followup regarding combining fields

    Thanks for your reply. Your suggestion works great when there is one speaker per seminar, but what I'm trying to do is get all three of my speakers on one line with my seminar. It would have to be some sort of function that would look at all the speakers linked to each seminar and list them together in one field. Any suggestions?
    -Julie



    Originally posted by abshaw
    Mircosoft Access is a very hany tool in terms of joining fields or concatination. once you have your data source identified in the report, all you need to know are the field names that you want to show togather in the detailed section.
    For example i want to join

    Seminar_desc
    Speaker_Name
    Seminar_time
    Seminar_duration

    a total of 4 fields in one line on the detailed section of the report.
    click on the toolbox if it is not already open. From the tool box click on the text box button, then click an drag it a reasonable size in the detail section of your report (i forgot to mention, you have to be in the design mode of your report to do all this). You can get rid of the label part of the text box as you wouldn't be needing it any longer.
    Now on the other section it should be showing "unbound".
    that means so far so good.
    Now click inside of that textbox and type this exactly for the four fields shown above.

    =[Seminar_desc]&" "&[Speaker_Name]&" "&[Seminar_time]&" "&[Seminar_duration]

    if you dont need any spaces between the four fields the following also works but not recommended

    =[Seminar_desc]+[Speaker_Name]+[Seminar_time]+[Seminar_duration]

    Hope it helps

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Followup regarding combining fields

    Originally posted by JHerrick79
    Thanks for your reply. Your suggestion works great when there is one speaker per seminar, but what I'm trying to do is get all three of my speakers on one line with my seminar. It would have to be some sort of function that would look at all the speakers linked to each seminar and list them together in one field. Any suggestions?
    -Julie
    Look at the snaking columns. Set your subreport up to have say 5 columns with 1in per column. Make sure they display accross and then down.

    Gregg

Posting Permissions

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