To Code or Not to code.

[mubbed]

In the database I am creating a table that houses “subjects’ names” and their “unique IDs”, which are both indexed with no duplicates. The Unique ID is used as a FK to connect all the tables that contain information from the subject. When subjects are enrolled in one of our studies they are assigned a “Unique ID”, which is assigned sequentially. The IDs are alphanumeric, and consists of 4 characters with the first character being a letter:

A001, A002…A999, B001, B002…C001 etc.:

To help avoid data entry errors, I would like to automate the process of assigning these Unique IDs as much as possible. So my question is:

Is it better to manually insert all the possible Unique IDs into this table upfront and then use a code that allows the user to assign the next Unique ID to the subjects they are enrolling (ie search the subject names column for the first null field and enter value there)? Or would it be better to set up a code that looks through the IDs that are already in the list and then automatically generates and assigns the next sequential ID to the person they have entered.

Also, where might I find some pre-established code to get me heading in the right direction?

Thanks in advance!

 

[namliam]

Create a function and use the DMax to retrieve the highest value allready created

Then do a little smart manipulation to change it to the new one required....

Edit: I think there is some sample on the forum about this

 

[neileg]

Just as an observation, I don't think it's right to have subject names set as no duplicates. People names are not unique.

 

[KKilfoil]

You didn't describe how the letter prefix is chosen.
Is it assigned at random?
Is it one of their initials, etc?
Do you go A001, B001, ..., Z001, A002, B002 etc?
Does the user get to pick it?

This will affect your 'best way'.

BTW, this only gives you 26*1000=26000 possible combos. Will this be enough forever? You may need a format like A0001 instead.

 

[mubbed]

The letters are given in alphabetical order starting with A. For example, once all number combinations have been assigned to A (i.e. A000-A999) the next letter ‘B’ will be given. The letters have no other purpose than to give us more combinations with out increasing the number of characters used. I know this only gives us 26,000 combinations but this is a relatively small research group and 26,000 ID’s been enough for quite some time. Plus, my hope is that eventually the group will fall in love with databasing and wont mind dropping the time and effort into building more robust SQL database. At that time they can re evaluate their coding system.

So far, I was thinking about using the MAX function and some fancy coding (which I have yet to figure out) to assign the next number in the sequence. To get around the alphanumeric bit I could have it generate 5 digit numbers. Then I could append that number to a text field which would house the actually Unique ID. During the append process I would change the first two numbers to the appropriate letter. So ‘00’ = ‘A’, “01” = ‘B’…. ‘25’ = Y, ‘26’ = ‘Z’

Example: 00000 = A000 and 26989 = Z989

I thought I remember seeing some code that would let me append the first two numbers to a given number but I have yet to relocate it.

Any thoughts on this? Am I making this harder than it needs to be?

 

[KKilfoil]

If you are still working on this...

I would simply use an autonumber 1 to n for your actual [subjectPK] primary key that connects your data to the subject, and use a long integer FK in all of the related tables.

Then you can use all of the typical Access techniques to build your db.


HOWEVER, create a query that contains all of the fields in your subject table, and create a field with an expression like:

[SubjectID]: Chr(Int([subjectPK]/1000)+65) & format(([SubjectPK]-(Int([subjectPK]/1000)*1000)),"000")

So, [SubjectPK] = 4023 becomes [SubjectID] = "E023", and [SubjectPK]=4 becomes [SubjectID]="A004"

Then use your new query (c/w the new field) everywhere in your db where you would have used the actual table, and use [SubjectID] everytime you need to show the users' "A001" field on a form or report.