How Do I Sort a Renumbered Table Made from an Update Query?

[warrenpage]

Hi everyone, I'm having a little bit of trouble sorting a table whose Auto Number values I've updated via a query. I'm using Access 2013.
I have a table with the last names and first names of musicians. I've imported it into Access via an Excel spreadsheet. The table looks like this:

tblMusicians


MusicianID (primary Key)
MusicianLastName
MusicianFirstName

I added a MusicianID auto number field, then ran a Duplicates Query to remove duplicate names. Of course, that leaves the auto number field with missing numbers--the numbers aren't consecutive.

I read a KB article that explains how to reset the auto number field:
http://support.microsoft.com/kb/812718

I Updated the table as per the instructions, and it worked. The query is properly sorted, and I sorted the updated table.

The problem is this: When I add a new MusicianID/AutoNumber field to the table, it does not assign numbers in alphabetical order.

I realize this isn't a BIG deal, but is it possible to assign the Auto Numbers to the records alphabetically? If it is, I haven't figured out how to do that yet.
If it can be done, what is the procedure? Thank you. Warren Page

[JoeM]

Why do you feel the need to remove the gaps in the Autonumber field at all?
That shouldn't be necessary. Autonumber is really just intended to be used to guarantee unique IDs, nothing more.
If you are trying to do something else with it, there may be better ways to go.

By the way, if you are starting with your data in Excel, why not remove all duplicates and sort the data there first before importing?
Excel has a very easy "Remove Duplicates" button.

[warrenpage]

Thanks for replying. First of all, I'm not very familiar with Excel and honestly didn't even know it had a remove duplicates button. Besides, my main goal is to learn Access, not Excel. That comes later.

Now...I feel the need to remove the gaps because it's beyond my scope of comprehension why there SHOULD be gaps in the first place. However, like I said in my original post, it's not a BIG deal.

I just want to know if it's possible to get the numbers to correspond with a sorted list.

When I generated the Auto Number field, the list began with the second half of names that began with the letter S. The numbering continued through Z.

Only THEN did the numbering continue through the A names. It went on to the first half of names that began with the letter S.

I'm having a lot of difficulty understanding why that happened.

Like I said, it's not really a big deal. I can resort the list, and the numbers aren't really all that critical. But if it CAN be done--if the numbering can indeed correspond with a sorted list of names--then I'd like to learn how to do it, just for the sake of learning and self-improvement. However, if it's too complicated, then it's not worth my effort.

I hope that makes sense. WP

[JoeM]

Quite frankly, I would have to say that it isn't really worth the effort, especially when there isn't really any reason to do it in the first place.
In Access, you can sort by any field, so there isn't really any reason to have the Autonumber field line up with the alphabetic sorting of your records.
And since the Autonumber field isn't really to be used for anything other than assigning a unique value, there shouldn't be any concern about gaps.

If you really want to get the autonumber fields in a certain order with no gaps, the easiest way is to import the cleaned up data in the order you want it, like I was suggesting. Then you don't need to mess with all those special unnecessary steps in Access. It really is incredibly simple to do that part in Excel. You really don't need to know anything about Excel to do it.

Quite frankly, I have been creating Access databases for over 10 years, and have never had the need to do what you want. I can't really think of any reason why one would want to. It is simply not necessary. If the issue is how users will see the data, note that you NEVER want your users to interact with the data tables directly. You want to control all access to the data through Queries, Forms, and Reports. And you can sort those by any fields you like.

Hope that helps explains things a little.

[warrenpage]

It isn't critical, true. Thanks for replying. WP

[JoeM]

I get the feeling that I haven't quite convinced you that not only is it not necessary, but there really isn't any benefit to trying to do it (so it is a a lot of extra work for no gain).
If that is how you feel, if you can tell me what benefit you are hoping to gain by doing this, I can try to explain it better and suggest the recommended way of going about what you are hoping to ultimately accomplish.

[warrenpage]

You didn't have to convince me, because I already knew it wasn't a serious issue. In my original post, I even said it's not a big deal.

The numbers themselves aren't really important to me. What's important to me is finding the data I need rapidly. Let me briefly tell you what I'm doing: I've created a form with a subform. The subform is for displaying the names of musicians. The subform is based on a junction table. The subform contains the album's ID number, the musician's ID number, and the musician's full name. (The "full name" field is calculated.)

Anyway, when I enter records, I have the Musician's table open, so I can grab the Musician's ID number. I place that number in the proper field in the subform, and voila, the musician's full name is placed in that field. The subform is continuous, and I can have an unlimited number of entries in the subform.

I have 337 names in the Musicians table. If I have the table sorted alphabetically, it won't be a big task to find a name and its corresponding number. But it doesn't make sense to me why the numbers CAN'T be consecutive AND connected to a sorted list.

That's where the convincing comes into play. You don't have to convince me that it's not necessary. I just want to know why it's not POSSIBLE--or not EASILY accomplished.

I hope what I've said makes sense. And I do have another question, but I'll post that in a separate thread.

[JoeM]

I have 337 names in the Musicians table. If I have the table sorted alphabetically, it won't be a big task to find a name and its corresponding number.

OK. Here is what you want to do here. Typically, when working with data, you NEVER really work with the table directly. You usually work through Forms or Queries. Queries can also be the source of your Forms. You can sort the data in your Queries any way you like.
So if you use a query, and sort it how you like, that should solve this issue for you, right?

I just want to know why it's not POSSIBLE--or not EASILY accomplished.

Its not impossible, just a pain in the neck to do, and even harder to maintain (as new records get added, or other records get deleted). Its really not worth the effort, especially when there are better ways of doing it.
Also note, that if you really want "meaningful" ID numbers, you can use something other than AutoNumbers. I have some processes that use VBA to create ID numbers. They can be based on things like names, dates, counters, etc.

[warrenpage]

My goal is to keep all of this as simple as possible, because I'm doing this for my own personal benefit--a hobby, basically. Since I'm not doing this for anyone else, and am not doing this for any type of compensation, my philosophy is "keep it simple."

If it's going to be a royal pain in the neck to do, then you're absolutely right...don't bother.

I did have a question about something else, which I posted elsewhere. It's another one of those "Not a Big Deal" questions I'm full of. WP

[JoeM]

I have lots of databases like this also. You still will want to go the query route. It is very easy, and gives you a lot of flexibility (you can sort, filter out certain records, etc).
It really isn't any extra work to work on the data from a query as opposed to a table.

[orange]

Warren,

Further to JoeM's comments, with which I agree totally, the autonumber is simply a meaningless number that is unique. It is simply used by the system to have a unique identifier on a record. There is an excellent article on what an autonumber is Not
at http://www.utteraccess.com/wiki/index.php/Autonumbers


As a somewhat trivial analogy (which may be off base), consider the following:

You have 5 bills in your wallet.

2 --- twenties (serial numbers 123243353 and 882772632 )
3 --- fives (serial numbers 435543322, 837664422 and 5006949390)

The serial numbers, which I'm sure are important to the Treasury department or Central bank, are pretty meaningless to you and I. Whether or not they are sequential has no real significance.

As for queries:
How much money do I have?
How many bills do I have?
How many 20s?
How many 5's?
How many 10's?

Queries are the means to deal with the bills.
Good luck with your project.

[JoeM]

Orange,

Nice link! That is quite a comprehensive explanation; much better than my attempts.
I'll have to bookmark that one.

[orange]

Glad you like it. It's a good reference.

[ssanfu]

And I have found a couple of other sites about AutoNumbers that are helpful:

http://access.mvps.org/access/general/gen0025.htm
http://www.fmsinc.com/free/newtips/primarykey.asp
http://www.bluemoosetech.com/microso...ial.php?jid=54

[orange]

Hi Steve,

Yes they are good references as well. The mvps site has lots of good things and has had for a long time.
FMS --Luke Chung - has excellent articles - and has made a living with this stuff.

How's the weather with you? We're having 50-60km/hr winds all day today.