Results 1 to 5 of 5
  1. #1
    trikosuave is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    3

    Creating single lined lists from reports

    I am trying to create a list of the data instead of a space hogging report fashion.



    Here is sample data:
    Name Letter Number
    name1 a 1
    name1 a 2
    name1 b 1
    name1 b 2
    name1 b 3

    The letters and numbers don't relate to each other and I would like this broken down to a single line:

    name1 a b 1 2 3

    Imagine I have hundred of names each consisting of multiple letters and numbers. Any Access generated reports would be huge if each name took a dozen lines or so. If I can get each name to take its own line, the reports would be manageable.

    Thanks!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can adapt something like this to your needs:

    http://allenbrowne.com/func-concat.html

    From the look of it, you'd want to add DISTINCT so you only get one instance of each option, and you'd want to add a second loop to get the second field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    trikosuave is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    3
    So I went ahead and added the code in the modules. I am very new to Access to I'm not sure where or in what context to put my data.

    This is my query that produces the normalized results. what line would i add here to concatenate it to a single line for each [item name].name.

    SELECT [item name].name, [item name].letter, table.number
    FROM [item name] LEFT JOIN table ON [item name].name = OS.name
    ORDER BY [item name].name;

    [item name] has names and letters. table has some but not all of the same names and matching numbers. I want 3 fields. name, list of letters, list of numbers. So i could just run the module twice. Do I add to my current query, or create a table from query and run the concat function on the new table?

    Thanks!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In Allen's example, it was called like this:

    SELECT CompanyName, ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
    FROM tblCompany;

    You would need to change the names to match yours. If you have trouble, post a sample db and I'll try to tweak it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    trikosuave is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    3
    Yes I read through the example you posted and that it as well as a couple of other sets of code. I'm not at work right now but here is an written setup of the two tables:
    TABLE: [item name]
    name letter
    name1 a
    name1 b
    name2 b
    name2 c

    TABLE: [table]
    name number
    name1 1
    name1 2
    name2 2
    name2 3

    I would like to get:
    name1 a, b 1, 2
    name2 b, c 2, 3

    I'm pretty sure you wanted me to upload a small piece of my db, but I won't be able to do that until tomorrow. I will be making a small db tonight to mess with it, so any help you can provide from what I've given would be greatly appreciated.
    Thanks!

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

Similar Threads

  1. Creating an HTML report as a single page
    By Harle in forum Access
    Replies: 2
    Last Post: 01-22-2011, 11:18 PM
  2. Single Record Reports (HELP)
    By bnckeye27 in forum Reports
    Replies: 1
    Last Post: 11-13-2009, 02:14 PM
  3. Replies: 0
    Last Post: 10-19-2009, 11:11 AM
  4. Creating Watermark in Access Reports / Forms
    By Alex Motilal in forum Reports
    Replies: 0
    Last Post: 11-21-2008, 07:11 AM
  5. Creating multiple reports from one Query
    By Mike Cooper in forum Reports
    Replies: 5
    Last Post: 04-24-2006, 05:00 PM

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