Importing from Excel - data columns out of order

JuicyJuice

New Member
Joined
Feb 15, 2005
Messages
20
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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?

ξ
 
Upvote 0
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.
 
Upvote 0
Can you drag your columns around in the datasheet view?
You may not have privileges to make design changes...
 
Upvote 0
No, I can't drag the columns in data view either. I created the db...how can I get additional privileges?
 
Upvote 0
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?
 
Upvote 0
It's a table - just a simple import of data from an Excel spreadsheet to a .mdb file. Using MS Access 2007.
 
Upvote 0
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
 
Upvote 0
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.

ξ
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top