Sorting my Access Database

[JLovell]

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.

[#2]

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?

[#3]

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

[#4]

@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

[#5]

@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

[#6]

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.

[#7]

@Daniel_Pineault 

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

[#8]

Can you show me a screenshot of your table design structure with the data types.

Normally, one is not supposed to work in tables, so this isn't normally a consideration. You should be working through forms built upon queries.

[#9]

i@Daniel_Pineault 

I believe this is a query off the form

and 958 still show at top

I think this is an SQL of the query

these should be table design

Thanks as always for any help - Jeff

[#10]

@JLovell 

I could really use some help with the above issue folks - thanks

Jeff

[#11]

I've tried replicating the issue, but have been unable to. Any chance you could upload a copy of your database to OneDrive, GoogleDrive, Dropbox, ... And provide us with a link for closer inspection?

Be sure to delete any confidential information beforehand.

[#12]

@Daniel_Pineault 

Dan I can send it to a private email, but I cant put it up on the web.

[#13]

Contact me through my site

https://cardaconsultants.com/contact-us/

And I'll reply so you can share with me.