Key Field Question

[dluhop]

I'm trying to create a database with a key field containing 1a, 1b, 2a, 2b, 2c, etc.  If I make this a text field it does not sort properly.  Is there a way to do this?  Thanks for your help.

[#2]

@dluhop 

Define "sort properly", please.

Also, what is the goal here? When Access developers see the words "Key fields" we think of something very specific, so it may be that an appropriate answer would depend on why you are creating this field and what you plan to do with it. 

[#3]

@George_Hepworth 

George, thanks for replying. I have a set of baseball cards that are numbered 1 thru 24. Some of them have variations. The variations of the first card should be numbered 1a, 1b, 1c, etc. If this is a text field, they sort 1, 11, 2, 20, etc. I could do 1.1, 1.2 as a numeric field but I really don't want to do it that way. I hope I'm explaining this so you can understand. 

[#4]

The most logical is to split the field into two fields. After all, these are two different pieces of data: card number and variation.
An other solution is to insert lading zeros (01a, 02c, 11d).

[#5]

I'm pretty much a novice when it comes to Access. I thought a key field is a unique identifier for a particular record in a table. I just want to be able to add cards to the table and have them be sorted in the right place.

[#6]

OK, good. That seems like a good solution. Text field with leading zeroes. Thanks.

[#7]

@dluhop 

Actually, this is two tables, in my opinion, not one with this concatenated code field.

The main table (the one-side of the relationship) is the "Master Card."

Each card type is listed here in this table.

Each variation is listed in a related table (the many-side of the relationship).

If a card has one and only one instance, then there are no records for it in the variation table. As you add variations, they are recorded in the variation table with a foreign key reflecting the Card's Primary Key and a sequence letter for it, e.g. "1a". That way, you can join the two tables to get a complete list.

That does leave the problem of a single card being "1" and having no "a" or "b" etc.

You could approach that in two ways. One, accept the fact that the master card is "1", "2", etc. and you'll only see the variation code IF there are actually variations. Two, finesse the original master with the "A" as suggested. I prefer the first option. 

[#8]

George, thanks for your help. I appreciate the time you put into this. I will develop my database with your suggestions in mind.

[#9]

without Splitting your field, you can sort on:

ORDER BY Val([FieldName]), Right([FieldName],1);

[#10]

I don't know where to put this expression. Can you be more specific? I appreciate your help.

[#11]

As is always the case, there are multiple approaches to problems.

One is to apply a band-aid to the symptom and move on, hoping other, similar, problems don't pop up.

The other is to identify the root of the problem and correct that, so the symptom goes away.

Choose wisely.