Sort data in a column, not alphabetical
I have an Access database that sorts the data in a column alphabetical and I want it to match my Excel spreadsheet which is by area. Where do I go in the database and change it so it follows my spreadsheet?
Software/Hardware used:
Access 2010 Excel 2010
Software/Hardware used:
Access 2010 Excel 2010
ASKED:
December 27, 2012 7:25 PM
UPDATED:
December 27, 2012 7:44 PM
Answer Wiki:
Export the Excel spreadsheet to your current Access database. OR
Import data from Excel spreadsheet into your current Access Database to a new table.
Now create a view using both the tables. You can periodically refresh the data in Access to keep the changes from Excel to Access synchronized.
All Answer Wiki Contributors:
DeveloperMark 
180 pts. ,
Michael Tidmarsh 11,390 pts. ,
DAHurd 25 pts.
180 pts. ,
Michael Tidmarsh 11,390 pts. ,
DAHurd 25 pts. To see all answers submitted to the Answer Wiki: View Answer History.
You will want to create a view and you will be able to select the column you want to order the data on. When you open that view, instead of the actual table you will see the data presented in the desired order.
Yes, I know that but when I run the reports it does not keep the same order. So somehwere it is not set up right. It has to be something in a field that is marked wrong. Work Center is a text field (row source) and that field is where I want it to keep the order that it is set up in. I have it typed in the list the way I want it to appear but in the report that is where it alphabitizes it. Suggestions?
1. Create a view that sorts in the correct order.
When you open this view it should look like the table but in the desired order.
2. Create a report that uses the view .. not the table.
I can’t get it to sort in the correct order…even when creating a new view.
Sounds like you need another column – DisplayOrder
I would make it numberic and enter numbers maybe 10 or 100 apart .. so there is room to insert between them. Order on that column in your view and report.
Where do I go in the database and change it so it follows my spreadsheet?
You need to tell us the definition of the table (or tables), the method of inserting data from the table into Excel and possibly the definition of the spreadsheet before knowing how to answer that.
For example, we don’t know if “area” is even stored in the table or it’s a derived value. And if it’s stored in the table, we don’t know it’s definition. If the spreadsheet has its own sort method applied, e.g., by macro or by how the data is loaded, it might affect how it should be applied to the database.
In theory, SQL tables are never sorted. Instead, data becomes sorted upon retrieval from the table. The sort is applied by using an ORDER BY clause in the statement that executes the data retrieval. (Access isn’t really “SQL”, but it follows many SQL standards.)
In that sense, asking about having a table in a database sorted in a particular way is essentially meaningless. A sort order only makes sense when you pull the data into a report, into a view for viewing, into a program for processing or in some similar case.
So, under what specific circumstances do you want to retrieve the data sorted by area? Do you have a form that you want to see the data in?
Tom