Alpha databases

[BoroLee]

I have been apporached by our Students Union to create a database which allows them to monitor the issue of union cards.

What they want however is a form which allocates an alphanumeric ID based on the 1st letter of the surname, and a number.

So you would end up with a1, b1, ca, d1 etc etc.

Has anyone else created anything similar that they could send me please.

Thanks,
Lee.

 

[ColinEssex]

Just to start off the replies for you.

I would have an AutoNumber that is allocated to each student as they are registered, that way you won't use the same number twice.

Then concatonate the AutoNumber to the first initial of the surname. Use the Left function to get the first initial.

I suspect others will have a better way maybe.

Col

 

[The_Doc_Man]

Well, I wouldn't do it the way they are asking, but they know what they want, so...

The trick is, if you want to sequence something, you need a table or query that provides it for counting, ordering, and the like.

SO...

You need a special first-letter-counting query for which one column is

Ltr1: Left$( Trim$(LastName), 1)

and another column is the assigned AlphaNumber for that student.

From here, you can do DCount calls to get a count of the number of records in the database for which the Ltr1 field matches the first letter of the name in question and the AlphaNumber column is NOT zero. This DCount tells you how many A's, B's, C's, etc. you have ALREADY assigned. The next number is just 1 + the value of that DCount, and you can assign it through the same query.

This makes the AlphaNumber ineligible for use as a prime key but itself (it repeats), though taken together with the Ltr1 field, once all students have assigned code numbers, it would be ok as part of a compound key that included LTR. HOWEVER, while any part of the student list is still missing numbers, you will have duplicate (0) entries in the AlphaNumber field. So you cannot make too many assumptions about the number until everything is fully assigned and you are ready to lock it down.

 

[Pat Hartman]

When systems were primarially manual (back in prehistoric times) and easy alternate lookups were not available, ID numbers were given meaning so that they might be easily remembered or reconstructed. We don't need to do that any more. Most "modern" systems use system-generated number fields as ids. For somthing that would be visible to the user, the id would normally be 6 or 7 digits and start numbering from 100000 or 1000000 so as to avoid the problem of leading zeros.

It is quite easy to start an autonumber at a given value so you can then generate fixed length numbers. Simply create an append query that appends a dummy record with a value for the autonumber field of 1 less than where you want to start the automatic assignment.

I would avoid "meaningful" numbers like the plague.

 

[Mile-O]

This post was solved on this thread.