No Duplicates for Full Name
in [Tables]
|
Prev: Please help! Designing database
Next: Access ID
From: Maarkr on 5 May 2010 07:59 I can't believe that you don't have a dup FN+LN. It's only a matter of time before that gets you into trouble, then what will the new John Smith use for an ID? You should switch to an alpha ID like first 3 of last name and last 4 of ssn (SMI9876), or similar to really reduce the chance of conflicts. Anyway, this is for a string Turn-In Key instead of the names: Private Sub turn_in_key_BeforeUpdate(Cancel As Integer) Dim stKey As String 'check to see if this is a duplicate value before updating stKey = Nz(DLookup("turn_in_key", "dbo_turn_in1", "turn_in_key='" & Me.turn_in_key & "'"), 0) Debug.Print stKey If stKey <> 0 Then ' this item has already been selected MsgBox "This Turn-In Number already exists." Cancel = True Me.Undo End If End Sub "Steve Stad" wrote: > John/Jeff. > > The database has multiple users from 15 depts entering Employee data for a > large organization (> 1000 recs). The employees move from one dept to > another so we want to ensure two different users can not enter the same > person in the db from their different dept forms. I checked for dups in the > Master table. There are dup last names and dup first names but NO dup > Lastname and Firstname combined. I am not sure if or what field to set to > 'no dups or unique' in the composite index set up to prevent dup first/last > name, mid init combined. > Could you be so kind to provide sample VBA code for the BeforeUpdate event > of the Form to *check* for duplicates using DLookUp; and warn the user and > give them the opportunity to compare the two records and cancel or change > their addition. > > "John W. Vinson" wrote: > > > On Fri, 30 Apr 2010 15:13:02 -0700, Steve Stad > > <SteveStad(a)discussions.microsoft.com> wrote: > > > > >I have 3 fields for Name. LastNm, FirstNm, and Middle Initial. How do I > > >ensure the full name, i.e., "LastNm, FirstNm, MiddleInitial" combined are > > >unique and not duplicated in the table? > > > > Why would you want to put in this unreasonable restriction? > > > > I once worked with Dr. Lawrence David Wise and with Dr. Lawrence David Wise. > > Larry was a tall blond affable chemist, L. David was a stocky dark taciturn > > biologist. But the company did not insist that one of them change his name to > > accommodate a flawed database design. > > > > At the most you should have VBA code in the BeforeUpdate event of the Form > > used to enter data, to *check* for duplicates using DLookUp; it should warn > > the user and give them the opportunity to compare the two records and cancel > > their addition. > > > > > I thought I could index but not sure > > >where/what to check for 'Unique' (e.g., on all three fields?) or if I need to > > >use a primary key. > > > > If you use the Indexes tool in table design view, you can enter three fields > > (or ten if you wish) in the right column, with a distinctive index name in the > > left column. There's a Unique checkbox. > > > > > I got a msg saying 'changes were not successful because > > >the create duplicate values in index, primary key, or relationship.' > > > > Sounds like you already have duplicate names; run a Totals query, grouping by > > the three fieldnames, and Counting the primary key (or any non null field); > > use a criterion of >1 to find which names are duplicated. > > > > But you should certainly NOT prohibit duplicate names. Duplicate names are > > valid in the real world, and your database should model the real world! > > -- > > > > John W. Vinson [MVP] > > . > >
From: Jeff Boyce on 5 May 2010 11:53 Even a composite key like your first 3 plus last 4 approach has issues (and yes, it looks like it would "reduce" the chance). Right off the top, not every person has an SSN. Then, there are some folks who don't have a last name, only a name (Cher & Bono come to mind). Then there's the potential for more than one Smith to have the same last four digits in their SSNs. The problem of coming up with a unique identifier for persons is not simple. The (apparently) simplest solution is to just use an Access Autonumber field, and use a totally meaningless, arbitrary number to ID each person. Other thoughts/opinions? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Maarkr" <Maarkr(a)discussions.microsoft.com> wrote in message news:3544419F-AED9-4480-9324-D4B5CB9B40CF(a)microsoft.com... >I can't believe that you don't have a dup FN+LN. It's only a matter of >time > before that gets you into trouble, then what will the new John Smith use > for > an ID? You should switch to an alpha ID like first 3 of last name and > last 4 > of ssn (SMI9876), or similar to really reduce the chance of conflicts. > Anyway, this is for a string Turn-In Key instead of the names: > > Private Sub turn_in_key_BeforeUpdate(Cancel As Integer) > Dim stKey As String > 'check to see if this is a duplicate value before updating > stKey = Nz(DLookup("turn_in_key", "dbo_turn_in1", "turn_in_key='" & > Me.turn_in_key & "'"), 0) > Debug.Print stKey > If stKey <> 0 Then > ' this item has already been selected > MsgBox "This Turn-In Number already exists." > Cancel = True > Me.Undo > End If > End Sub > > > "Steve Stad" wrote: > >> John/Jeff. >> >> The database has multiple users from 15 depts entering Employee data for >> a >> large organization (> 1000 recs). The employees move from one dept to >> another so we want to ensure two different users can not enter the same >> person in the db from their different dept forms. I checked for dups in >> the >> Master table. There are dup last names and dup first names but NO dup >> Lastname and Firstname combined. I am not sure if or what field to set >> to >> 'no dups or unique' in the composite index set up to prevent dup >> first/last >> name, mid init combined. >> Could you be so kind to provide sample VBA code for the BeforeUpdate >> event >> of the Form to *check* for duplicates using DLookUp; and warn the user >> and >> give them the opportunity to compare the two records and cancel or change >> their addition. >> >> "John W. Vinson" wrote: >> >> > On Fri, 30 Apr 2010 15:13:02 -0700, Steve Stad >> > <SteveStad(a)discussions.microsoft.com> wrote: >> > >> > >I have 3 fields for Name. LastNm, FirstNm, and Middle Initial. How >> > >do I >> > >ensure the full name, i.e., "LastNm, FirstNm, MiddleInitial" combined >> > >are >> > >unique and not duplicated in the table? >> > >> > Why would you want to put in this unreasonable restriction? >> > >> > I once worked with Dr. Lawrence David Wise and with Dr. Lawrence David >> > Wise. >> > Larry was a tall blond affable chemist, L. David was a stocky dark >> > taciturn >> > biologist. But the company did not insist that one of them change his >> > name to >> > accommodate a flawed database design. >> > >> > At the most you should have VBA code in the BeforeUpdate event of the >> > Form >> > used to enter data, to *check* for duplicates using DLookUp; it should >> > warn >> > the user and give them the opportunity to compare the two records and >> > cancel >> > their addition. >> > >> > > I thought I could index but not sure >> > >where/what to check for 'Unique' (e.g., on all three fields?) or if I >> > >need to >> > >use a primary key. >> > >> > If you use the Indexes tool in table design view, you can enter three >> > fields >> > (or ten if you wish) in the right column, with a distinctive index name >> > in the >> > left column. There's a Unique checkbox. >> > >> > > I got a msg saying 'changes were not successful because >> > >the create duplicate values in index, primary key, or relationship.' >> > >> > Sounds like you already have duplicate names; run a Totals query, >> > grouping by >> > the three fieldnames, and Counting the primary key (or any non null >> > field); >> > use a criterion of >1 to find which names are duplicated. >> > >> > But you should certainly NOT prohibit duplicate names. Duplicate names >> > are >> > valid in the real world, and your database should model the real world! >> > -- >> > >> > John W. Vinson [MVP] >> > . >> >
From: Steve Stad on 7 May 2010 16:20
Maarkr, Thanks for reply and sorry for delayed response. I suppose your code below will provide the chance to check for dups and warn the user to cancel entry if needed. What is in the lookup table 'dbo_turn_in1'? Also, do I need to create a new field called 'turn_in_Key' or can I use Full_name field (concatenation of lastnm, firstnm, and middle initial. "Maarkr" wrote: > I can't believe that you don't have a dup FN+LN. It's only a matter of time > before that gets you into trouble, then what will the new John Smith use for > an ID? You should switch to an alpha ID like first 3 of last name and last 4 > of ssn (SMI9876), or similar to really reduce the chance of conflicts. > Anyway, this is for a string Turn-In Key instead of the names: > > Private Sub turn_in_key_BeforeUpdate(Cancel As Integer) > Dim stKey As String > 'check to see if this is a duplicate value before updating > stKey = Nz(DLookup("turn_in_key", "dbo_turn_in1", "turn_in_key='" & > Me.turn_in_key & "'"), 0) > Debug.Print stKey > If stKey <> 0 Then > ' this item has already been selected > MsgBox "This Turn-In Number already exists." > Cancel = True > Me.Undo > End If > End Sub > > > "Steve Stad" wrote: > > > John/Jeff. > > > > The database has multiple users from 15 depts entering Employee data for a > > large organization (> 1000 recs). The employees move from one dept to > > another so we want to ensure two different users can not enter the same > > person in the db from their different dept forms. I checked for dups in the > > Master table. There are dup last names and dup first names but NO dup > > Lastname and Firstname combined. I am not sure if or what field to set to > > 'no dups or unique' in the composite index set up to prevent dup first/last > > name, mid init combined. > > Could you be so kind to provide sample VBA code for the BeforeUpdate event > > of the Form to *check* for duplicates using DLookUp; and warn the user and > > give them the opportunity to compare the two records and cancel or change > > their addition. > > > > "John W. Vinson" wrote: > > > > > On Fri, 30 Apr 2010 15:13:02 -0700, Steve Stad > > > <SteveStad(a)discussions.microsoft.com> wrote: > > > > > > >I have 3 fields for Name. LastNm, FirstNm, and Middle Initial. How do I > > > >ensure the full name, i.e., "LastNm, FirstNm, MiddleInitial" combined are > > > >unique and not duplicated in the table? > > > > > > Why would you want to put in this unreasonable restriction? > > > > > > I once worked with Dr. Lawrence David Wise and with Dr. Lawrence David Wise. > > > Larry was a tall blond affable chemist, L. David was a stocky dark taciturn > > > biologist. But the company did not insist that one of them change his name to > > > accommodate a flawed database design. > > > > > > At the most you should have VBA code in the BeforeUpdate event of the Form > > > used to enter data, to *check* for duplicates using DLookUp; it should warn > > > the user and give them the opportunity to compare the two records and cancel > > > their addition. > > > > > > > I thought I could index but not sure > > > >where/what to check for 'Unique' (e.g., on all three fields?) or if I need to > > > >use a primary key. > > > > > > If you use the Indexes tool in table design view, you can enter three fields > > > (or ten if you wish) in the right column, with a distinctive index name in the > > > left column. There's a Unique checkbox. > > > > > > > I got a msg saying 'changes were not successful because > > > >the create duplicate values in index, primary key, or relationship.' > > > > > > Sounds like you already have duplicate names; run a Totals query, grouping by > > > the three fieldnames, and Counting the primary key (or any non null field); > > > use a criterion of >1 to find which names are duplicated. > > > > > > But you should certainly NOT prohibit duplicate names. Duplicate names are > > > valid in the real world, and your database should model the real world! > > > -- > > > > > > John W. Vinson [MVP] > > > . > > > |