Results 1 to 3 of 3
  1. #1
    mbake085 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    21

    Adapting Excel File to Access Table

    Hello Everyone,

    I have an excel file I am trying to adapt to an access table. The original user has done this:

    ID | PJT
    1 1, 2, 3, 4


    2 5
    3 6, 7
    4 8, 9, 10
    5.. 11

    I want this:

    ID | PJT
    1 1
    1 2
    1 3
    1 4
    2 5
    3 6
    3 7
    4 8
    4 9
    4 10
    5 11

    Except there are over 300 IDs and some columns have over 30 PJT numbers separated by commas. They aren't sequential and each one has 6 digits.

    I know how to text to columns and transpose, but that only gets me to the point where I manually have to cut and paste each into one column then I have to make sure the ID number is with the corresponding PJT. Not a fan of wasting time doing it manually when there may be an easy solution.

    Any help is appreciated.

    Thanks,

    Matt

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    Step 1 is to import that sheet into a table. those comma delimited should all go into their own column. Let's call this Table1

    Next make a new target table with no data. Let's call this DataTable

    Once that is done; make an AppendQuery that is of just the ID column and 1 info column of Table1 and appending into DataTable: run it, then modify it to be different info column (always keeping the ID column), keep appending each column into one data column; lather, rinse & repeat

    Yes it is grunt work manual - but simple and fast.

    Hope this helps.

  3. #3
    mbake085 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    21
    Very good. I had a pretty quick method in excel, but that one beats it.

    Thanks!

    Sincerely,

    Matt

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

Similar Threads

  1. excel file solved in access
    By miltos in forum Programming
    Replies: 4
    Last Post: 02-19-2011, 04:44 AM
  2. Access link to excel file
    By delkath in forum Access
    Replies: 3
    Last Post: 09-13-2010, 12:28 PM
  3. making an excel file in access
    By dracula in forum Access
    Replies: 4
    Last Post: 08-27-2010, 04:24 PM
  4. Creating excel file from access vba
    By rparmar in forum Programming
    Replies: 3
    Last Post: 03-02-2010, 06:03 PM
  5. Access 2007 file size vs Excel
    By andrewalms in forum Access
    Replies: 4
    Last Post: 02-02-2010, 02:32 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