Generating my unique ID

[YNWA]

Hi,

After creating my unique ID, I am looking to add 1 extra part to it.

I currently have the ID pull back the initials, gender and date of death of patients (eg. ABM09/10) It pulls all this info from various fields on a form and sub form.

What I need it to do now is generate this ID but also factor in a autonumber. So it would look like 1-ABM09/10 or ABM09/10-1, then ABM09/10-2 etc...

I am currently using the autonumber feature as a PK for each record, but would also like to generate this uniqueID as its required for forms between agencies.

Any help?

Thanks
Will

 

[JANR]

Look into the function DMAX and add 1 this function, there are several treads on this subject on this site.

JR

 

[gemma-the-husky]

my two pennyworth

just have an autonumber on its own, and use (just) that as the foreign key in any related tables. And understand that the autonumber may not run in sequence, and should only be used to identify a record, for use as a foreign key.

in your patient table, by all means store the other bits (gender, date of death etc etc) and use them when you want to display something - but dont include these bits in the primary key.

note that they seem insufficient to me anyway, to guaranteee uniqueness. As well as that it seems an arbitrary selection. Why include the gender in the record key? And surely you could get 2 patients with the same initials passing on the same day, which would give you a problem.

And in addition, why include the date of death anyway. What about patients that haven't passed!

 

[Galaxiom]

We often see posts about making complex primary keys. The one proposed in this post is particularly hideous. I would be curious to know from those who aspire to this kind of key - what is the point of obfuscating something that could be so simple?

Fact is they are much harder to work with than a simple number. As a key they are siginificantly inferior to a number in every aspect of database operation. They are slower to search and slower in joins. They invaribly have to include some incrementing segment making them incredibly slow and complex to generate.

Moreover the recording of this information in the key is actually a breach of normalization since it is already recorded in the fields it is derived from.

If you must use codes like this then they should be derived on the forms and reports. The absolute last place they should be included is in the primary key.

 

[YNWA]

I am already using the autonumber as a PK and FK in tables.

The uniqueID field is something that needs to be generated on the form only which it currently is. However as pointed out, its not unique, hence why I want to add the 1- part to it.

The field is not going to be used in any joins, links whatever. Its sole purpose is when a report is printed, the paper work has the uniqueID, of which the ABM09/10 part is then used in correspondance between police, fire, doctors etc.. The format of the uniqueID is something they have used for ages in paper form, so they would like the same set up, but by putting the 1- or 2- in front of the ID, its making it unique.

Previously the service would hand write this ID, and if a duplicate occured they would put a a) or b) next to their initials.

The random generating of the numbers along with the initials etc... is just something to generate them this ID without them having to write it down and mess with duplicates.

Again its not my PK, nor is it a FK.

Its a simple reference ID to be used. Much like the one you get on your tax return or something like that.

Thanks
Will

 

[Galaxiom]

Technically, from a normalization point, the unique ID should be derived as required from the parts which are stored in the other fields. The unique numeric sequence section is all that would need to be recorded.

Assuming the sex is recorded as M or F

= Left([firstname,1) & Left(surname,1) & [Sex] & Format([datefield], "mm/yy") & "-" & [thenumberfield]

Use a DMax + 1 to get a new number. The DMax condition limits the records to those which match the derived expression less the number field.

 

[YNWA]

Technically, from a normalization point, the unique ID should be derived as required from the parts which are stored in the other fields. The unique numeric sequence section is all that would need to be recorded.

Assuming the sex is recorded as M or F

= Left([firstname,1) & Left(surname,1) & [Sex] & Format([datefield], "mm/yy") & "-" & [thenumberfield]

Use a DMax + 1 to get a new number. The DMax condition limits the records to those which match the derived expression less the number field.

I record sex as Male or Female.

I am using the following codes:

In a hidden field on form called txtGetUniqueID:

=UCase(Left([fldFirstName],1) & Left([fldLastName],1) & Left(cboGender.Column(1),1)) & Format(DLookUp("[fldDateDeath]","tbl_DeathsInfo","[recordID]=" & [ID]),"mm/yy")

Then I have another field on form called txtSetUniqueID which stores the ID into a table/query field.

Then the following codes in the fields on the form:

Private Sub txtFirstName_AfterUpdate()
    If Not IsError(Me.txtGetUniqueID) Then
        Me.txtSetUniqueID = Me.txtGetUniqueID
    End If
End Sub
 
Private Sub txtLastName_AfterUpdate()
    If Not IsError(Me.txtGetUniqueID) Then
        Me.txtSetUniqueID = Me.txtGetUniqueID
    End If
End Sub
 
Private Sub cboGender_AfterUpdate()
    If Not IsError(Me.txtGetUniqueID) Then
        Me.txtSetUniqueID = Me.txtGetUniqueID
    End If
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not IsError(Me.txtGetUniqueID) Then
        Me.txtSetUniqueID = Me.txtGetUniqueID
    End If
End Sub

So I have the ID generating now, its just the autonumber type sequence of +1 I need.

I will look into DMax+1 now.

Thanks for your help.

 

[YNWA]

When using DMax, would I need to use code to go into a query as the UniqueIDs control source is a field in a query.

Or would I use the VBA code?

And as I am pulling different letters from different fields, would the fields I need to use in DMax be all those used to form the unique ID or just a couple of them?