Results 1 to 9 of 9
  1. #1
    zarfx4 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    15

    Combine multiple rows into one cell

    Hello...

    This is bugging me to death...

    I need to make a query that combines data that is Un-similar...

    Heres what I have

    Table1:


    OrgID: OrgRef:
    1001 12345
    1001 6789
    1001 7412
    1002 6581
    1002 9982

    I need a query to return results like this

    OrgID: OrgRef:
    1001 12345, 6789, 7412
    1002 6581, 9982

    I need this to also be dynamic as I do not know how many combos there willl be at any time..

    Ive tried many different codes ive seen, but they do not seem to work...

    I TRULY appreciate your help!!!!

  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,922
    I would look at a CrossTab query or maybe a Pivot Table.

  3. #3
    zarfx4 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    15
    I have tried this and it does not seem to work for what I need to do later on in taking the information into a form and then processing it through a module that will send an email to people via Lotus Notes.

    Need something that will concatenate the data into one cell for me, but be dynamic

  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,922
    Are you looking for something like this code?

  5. #5
    zarfx4 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    15
    YES! This is what I want exactly...now my only problem is, everytime i try to use an example like this, when I enter the code and go to Compile it, to see if Access if going to understand it, it gives me an error

    Compile Error:
    User-defined type not defined

    and then in the code, (for this example) it highlights this:
    Dim rs As DAO.Recordset 'Related records


    Thoughts?

  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,922
    Have you set a reference to the DAO Library? <ALT> F11 then Tools>References

  7. #7
    zarfx4 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    15
    Ya...got that figured out right after I typed it...wasnt loaded...So..now I run the query, and I get:
    error 3464 data type mismatch in criteria expression

    Grrrr

  8. #8
    zarfx4 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    15
    I GOT IT!!!!!
    It was a formating issue....needed to format my table columns as Number not TEXT!!

    WOW!!! Thank you SO! MUCH!!!!

  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,922
    Glad I could help. Thanks for posting back with your success.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-02-2009, 10:12 PM
  2. I have 4 Tables in Access - can I combine them?
    By officespace in forum Access
    Replies: 6
    Last Post: 02-22-2009, 07:21 AM
  3. change cell color
    By bishop743 in forum Programming
    Replies: 0
    Last Post: 02-01-2009, 11:00 AM
  4. Inserting into multible cell
    By Intersysop in forum Access
    Replies: 1
    Last Post: 10-19-2006, 08:07 AM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 AM

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