A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

No Duplicates for Full Name



 
 
Thread Tools Display Modes
  #1  
Old April 30th, 2010, 11:13 PM posted to microsoft.public.access.tablesdbdesign
Steve Stad
external usenet poster
 
Posts: 89
Default No Duplicates for Full Name

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.'
  #2  
Old April 30th, 2010, 11:52 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default No Duplicates for Full Name

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" wrote in message
...
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.'



  #3  
Old May 1st, 2010, 01:10 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default No Duplicates for Full Name

On Fri, 30 Apr 2010 15:13:02 -0700, Steve Stad
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]
  #4  
Old May 1st, 2010, 10:35 PM posted to microsoft.public.access.tablesdbdesign
Steve Stad
external usenet poster
 
Posts: 89
Default No Duplicates for Full Name

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
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]
.

  #5  
Old May 2nd, 2010, 01:24 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default No Duplicates for Full Name

On Sat, 1 May 2010 14:35:01 -0700, 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.


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.


Su 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]
  #6  
Old May 5th, 2010, 12:59 PM posted to microsoft.public.access.tablesdbdesign
Maarkr
external usenet poster
 
Posts: 240
Default No Duplicates for Full Name

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
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]
.

  #7  
Old May 5th, 2010, 04:53 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default No Duplicates for Full Name

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" wrote in message
...
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
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]
.



  #8  
Old May 7th, 2010, 09:20 PM posted to microsoft.public.access.tablesdbdesign
Steve Stad
external usenet poster
 
Posts: 89
Default No Duplicates for Full Name

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
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]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:15 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.