No Duplicates for Full Name
in [Tables]
|
Prev: Please help! Designing database
Next: Access ID
From: Steve Stad on 30 Apr 2010 18:13 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? 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. I got a msg saying 'changes were not successful because the create duplicate values in index, primary key, or relationship.'
From: Jeff Boyce on 30 Apr 2010 18:52 Steve You don't mention how many persons' names you have, but is it inconceivable that your database could have more than one "John Q. Public"? I'd be very leery of trying to create a unique index on that combination. .... but yes, you can use multiple fields (no need to "combine" them) as an index. It sounds like Access is telling you that you ALREADY have duplicates! Good luck 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. "Steve Stad" <SteveStad(a)discussions.microsoft.com> wrote in message news:8BF2A9F9-8217-487A-B64C-F8F512B6004E(a)microsoft.com... >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? 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. I got a msg saying 'changes were not successful > because > the create duplicate values in index, primary key, or relationship.'
From: John W. Vinson on 30 Apr 2010 20:10 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 1 May 2010 17:35 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: John W. Vinson on 1 May 2010 20:24
On Sat, 1 May 2010 14:35:01 -0700, Steve Stad <SteveStad(a)discussions.microsoft.com> 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. Surely you have a unique EmployeeID!!??? You should be joining on that. And you may not currently have the Lawrence David Wise problem but there's no way to ensure that you won't next week. >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. Sure: however, rather than a DLookUp I'll suggest using a recordset. This assumes (perhaps incorrectly, it can be tweaked if this isn't the case) that the Form is based on a query returning all the items in the employee table, and that the primary key of the table is named EmployeeID. Private Sub Form_BeforeUpdate(Cancel as Integer) Dim rs As DAO.Recordset Dim iAns As Integer Dim strMsg As String Set rs = Me.RecordsetClone rs.FindFirst "[LastName] = """ & Me!txtLastName & """ AND [FirstName] = """ _ & Me!txtFirstName & """" If rs.NoMatch Then ' all is well, no dup for this name, do nothing Else strMsg = "This name is already in; add it anyway?" & vbCrLf & _ "Click Yes to add it, No to open that employee's record," & _ "or Cancel to undo this entry:" iAns = MsgBox(strMsg, vbYesNoCancel) Select Case iAns Case vbYes ' add it anyway ' do nothing, just let it be added Case vbNo ' jump to the other record Cancel = True ' don't update the table Me.Bookmark = rs.Bookmark ' jump to the found record Case vbCancel ' undo this addition Cancel = True Me.Undo End Select End If Set rs = Nothing ' clean up after yourself End Sub -- John W. Vinson [MVP] |