I am having trouble sorting my database. ONE entry will not sort properly. I have erased the entry and re-entered it without success. I am sorting on three fields out of maybe thirty and everything sorts properly but this one entry.

I'm afraid you haven't given us a lot to go on to be in a position to help you. Remember, we can't see your database, your values, ...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?

JLovell If you find it too onerous to describe the relational database application in sufficient detail, one alternative is to upload a copy of the accdb with enough sample data to illustrate the problem. Remove or modify any sensitive data, of course.

Daniel_Pineault 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.

Sorting my Access Database | Microsoft Community Hub

Daniel_Pineault
MVP
Sep 01, 2020
I'm afraid you haven't given us a lot to go on to be in a position to help you. Remember, we can't see your database, your values, ...

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?
- JLovell
  Copper Contributor
  Sep 02, 2020
  Daniel_Pineault
  
  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
    Copper Contributor
    Sep 03, 2020
    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
George_Hepworth
Silver Contributor
Sep 01, 2020
JLovell If you find it too onerous to describe the relational database application in sufficient detail, one alternative is to upload a copy of the accdb with enough sample data to illustrate the problem. Remove or modify any sensitive data, of course.

Forum Discussion

Sorting my Access Database

Share

Resources