Results 1 to 9 of 9

Importing from Excel - data columns out of order

This is a discussion on Importing from Excel - data columns out of order within the Microsoft Access forums, part of the Question Forums category; I have been using the same database for years, importing new data into it monthly. The new data columns are ...

  1. #1
    New Member
    Join Date
    Feb 2005
    Location
    Ohio
    Posts
    20

    Default Importing from Excel - data columns out of order

    I have been using the same database for years, importing new data into it monthly. The new data columns are always in the same order in Excel but when I import to Access, one column moves to a different location in the table. Why? Is there a way to keep the columns in the same order they're in when importing?

    Thanks!

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,168

    Default Re: Importing from Excel - data columns out of order

    Access tables don't really care much about column order - queries and reports can easily change the ordering for users.

    If you are viewing the data directly in an Access table you should be able to open the table in design view and drag the field up or down with your mouse, to put it where you want.

    Does that do it?

    ξ

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    Feb 2005
    Location
    Ohio
    Posts
    20

    Default Re: Importing from Excel - data columns out of order

    I can't get it to let me drag the field name around in design view. I ended up going to data view, inserting a column, and copying the data into it. Then I deleted the original column and renamed the new one. Seems like there should be an easier way.

    Thanks.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,168

    Default Re: Importing from Excel - data columns out of order

    Can you drag your columns around in the datasheet view?
    You may not have privileges to make design changes...

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    New Member
    Join Date
    Feb 2005
    Location
    Ohio
    Posts
    20

    Default Re: Importing from Excel - data columns out of order

    No, I can't drag the columns in data view either. I created the db...how can I get additional privileges?

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,168

    Default Re: Importing from Excel - data columns out of order

    Is this a query (in datasheet view) or a table you are using?
    What is the file format of the database? (mdb, accdb, mde, etc.)
    Which version of Access are you using?

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    New Member
    Join Date
    Feb 2005
    Location
    Ohio
    Posts
    20

    Default Re: Importing from Excel - data columns out of order

    It's a table - just a simple import of data from an Excel spreadsheet to a .mdb file. Using MS Access 2007.

  8. #8
    Board Regular
    Join Date
    Apr 2003
    Location
    Jacksonville, FL
    Posts
    1,344

    Default Re: Importing from Excel - data columns out of order

    Juicy,

    I think you're missing one of the fundamental differences between spreadsheets and databases. It doesn't really matter what order the columns might be in a Table because in a database, you can use queries to re-order, re-format, whatever you want and...for nearly all situations, you can use the queries interchangeably with the tables.

    By the last, it sounds a lot like you're trying to change the order of the columns within the design view for the table. That's not what he's suggesting. He's suggesting that you go over to the query tab and make one based on your table and then put the columns in whatever order you'd like.

    From there...if you currently use the table anywhere such as a recordsource for a form or report...change it to use the query instead.

    Mike

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,168

    Default Re: Importing from Excel - data columns out of order

    I can't get it to let me drag the field name around in design view. I ended up going to data view, inserting a column, and copying the data into it. Then I deleted the original column and renamed the new one. Seems like there should be an easier way.

    Thanks.
    Doesn't really make sense. What are your columns named? What data are you copying in? It doesn't really make sense that you can delete columns but not change the order of them (whether tables or queries). I'm not on Access 2007 but normally you can just move them around either in design or datasheet view. I think there's probably something very obvious we are missing here. Can you describe in excruciating detail the steps you go through (including what you wish to do but cannot - up to the point that you can't go any further)? If you want to send me a copy of your db I'll PM you.

    ξ

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com