Forum Discussion
Sorting my Access Database
You need to provide us with enough information to be able to replicate the issue at our end to then be in a position to diagnose the issue. Another good option is sharing a copy of the database in question (removing any confidential information first!).
So you say it doesn't sort properly, what are your values? Which one doesn't want to sort? What is the data type of that field? How are you applying the sort?
Daniel (et al) thanks for getting back to me. I have database to be able to inventory cemeteries. The database was imported from excel and it was originally exported to excel from Alpha 5 another database program.
The database has over 7000 records. Three of the fields are lot prefix (alpha) lot number (numeric) and lot number suffix (alpha). When I try to create a query with lot prefix first (ascending), lot number second (ascending) and lot number suffix third (ascending), and then run the query EVERYTHING sorts properly with the exception of ONE record. (The last record I entered on the database and this entry was after the transfer). The record has a no lot prefix and a lot number of 859 and should certainly not be first as the list should actually start with blanks in all three fields and then lot prefix of A, lot number 1, and not lot number suffix. Now although I want to sort on these three fields I still want ALL the fields to be in query, just with that three level sort. I hope this is a better explanation. I have also tried deleting the record and re-entering it to no avail. I have several SORTS that I would like to create and several cemeteries to do this with, but I can't seem to get it right with the first SORT. Any help would be appreciated. Very frustrating. - Jeff
JLovell Just a little add on to my previous post. I just added another record and it sorted properly. I cannot see why I have ONE record that I added that will not sort and insists on be first in my sort no matter what values I put in the sort fields. - Thanks for the help - jeff
Sounds to me the issue is because there no lot prefix, so this impacts sorting.
It would also help if you gave use a sample of your data, the current sorting result and the desired sorting result. You also need to provide us with the SQL Statement you are using.
If you can share a copy of your db (please remove any confidential information first) by uploading it to an online folder (OneDrive, GoogleDrive, DropBox, ...) and then share the link we could take a closer look at the data and SQL statement you need to use to get thing sorted the way you want it to be.
These two screen shots show the top INCORRECT record out of sequence and all the other records in correct sequence and the bottom screen shot shows the sort I am using. Thanks for any help - Jeff
