Results 1 to 11 of 11
  1. #1
    peter_lawton is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Sep 2009
    Location
    London
    Posts
    29

    Access to Excel transferring multiple rows to single row

    I have (in Access 2002)

    Table 1 called SongTitles containing the fields SongTitleID and Song Title
    Table 2 called Writers containing the fields WriterID, WriterFirstName and WriterLastName
    Table 3, a junction table linking SongTitleID and WriterID both one to many from SongTitles and Writers to Table 3

    My simple query answer table gives me

    People Are Strange Alan Smith
    People Are Strange Billy Jones

    which is fine because I display this information on a form which only shows the title once

    HOWEVER

    A third party company would like me to send my data to them on an excel sheet in a format where the info is contained in a single row

    SongTitle; Writer 1 First Name; Writer 1 Last Name; Writer 2 First Name; Writer 1 Last Name



    It would come out as

    People Are Strange; Alan; Smith; Billy; Jones

    Can anyone please tell me if there a way of creating a new answer table in this format for exporting the data into excel.

    Help very gratefully appreciated

    peter

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    You may find this link useful.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    peter_lawton is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Sep 2009
    Location
    London
    Posts
    29

    Solution looks good, me less so

    Thanks for the swift reply. It looks as though it should do what I want. I tried to keep my intial foray as simple as possible. (Only once I knew it worked I would start being a clever sod). Novice at VBA, rusty at SQL but usually get there.

    I followed instructions and created a new Module which I called ConcatRelated. Ran Compile DatabaseName in Debug (which didn't do anything visible but nor did it report a bug and Compile DatabaseName is now greyed out).

    Created a simple query in SQL copying your template and ran

    SELECT SongTitle, ConcatRelated("WriterLastName","Writers", "SongTitleID = " & [SongTitleID])
    FROM SongTitles;

    Received message "Undefined function 'ConcatRelated' in Expression"

    Module is in there and I haven't modified a single character so surely the function should be recognised even if I still have to fiddle around with the query to get it to work. I've plainly missed some mindnumbingly obvious step and would be grateful if you could point it out for me.

    Thanks peter

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Rename the ConcatRelated module basConcat and see what happens. The module must not be the same name as the function.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    peter_lawton is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Sep 2009
    Location
    London
    Posts
    29
    That works thanks, need to spend some time on the SQL to make sure produces the right answer.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    That's great! Are you ready to use the Solved thread tool yet?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    peter_lawton is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Sep 2009
    Location
    London
    Posts
    29
    I'd prefer to resolve when I've actually succeeded in running the query. I'm trying to fit this into regular working day and have left my SQL bible at home so enjoying reading syntax error messages as a distraction from day job. I anticipate I will post resolved within 24 hours at most. Your help has been much appreciated.

    Peter

  8. #8
    peter_lawton is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Sep 2009
    Location
    London
    Posts
    29
    OK I've created the SQL that gives me SongTitle in Field 1 and Writer1, Writer2 in field two (separated by comma) as per the example given.

    Ideally I'd like to put Writer1 and Writer2 in their own fields. I can do this by exporting to a csv and then importing into Excel of course, but it would be neat to have Access do it. Am I asking too much?

    So far so excellent

    Thank you Peter

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I don't think you even know how many fields will come back and Access does not care much for that. You could make a temporary table for the 1st results and then do some further analysis and split up the concantenated field I suppose.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  10. #10
    peter_lawton is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Sep 2009
    Location
    London
    Posts
    29
    I can't see how to mark this solved!

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Follow the link in my Sig.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Table related to multiple tables by single ID
    By MrTumnus in forum Access
    Replies: 1
    Last Post: 11-17-2009, 02:05 PM
  2. Excel exporting in single cells
    By Paolo29011982 in forum Import/Export Data
    Replies: 0
    Last Post: 07-29-2009, 04:48 AM
  3. Replies: 1
    Last Post: 07-07-2009, 01:00 PM
  4. Combine multiple rows into one cell
    By zarfx4 in forum Queries
    Replies: 8
    Last Post: 06-08-2009, 10:42 AM
  5. Transferring Individual Records
    By bazillion in forum Programming
    Replies: 0
    Last Post: 01-25-2008, 12:13 PM

Tags for this Thread

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