unique index

  • Thread starter Thread starter jomara via AccessMonster.com
  • Start date Start date
J

jomara via AccessMonster.com

I have a database that i am trying to eliminate duplicate entries the fields
are FirstName,LastName, WorkPhone, and HomePhone. I realize i can have more
than 1 client with the same name that is why i am using phone as well. I
started at the table level and made a unique index of the 4. That doesnt work
because i might only have 1 phone number or the other. I do not know vb at
all is there a way to make this work
 
You can't include an empty field as part of the primary key. Therefore, if
you know this is what you want to do you could put a default phone# with
zeros. This would allow you to create the primary key. If this is already an
existing database you'll need to make an update query which inserts zeros
into phone#s which are null.

A better way to create the primary key is to add an id# which is an
autoincrement data type, and you don't need to worry about duplicated names.

James
 
You can't include an empty field as part of the primary key. Therefore, if
you know this is what you want to do you could put a default phone# with
zeros. This would allow you to create the primary key. If this is already an
existing database you'll need to make an update query which inserts zeros
into phone#s which are null.

A better way to create the primary key is to add an id# which is an
autoincrement data type, and you don't need to worry about duplicated names.

James





- Vis sitert tekst -
Click to expand...

Creating an ID will not help you prevent registering the same client
twice. You need to find other ways to do it. If your clients can have
the same name and no phonenr. how will the database see them apart?
 
I have a database that i am trying to eliminate duplicate entries the fields
are FirstName,LastName, WorkPhone, and HomePhone. I realize i can have more
than 1 client with the same name that is why i am using phone as well. I
started at the table level and made a unique index of the 4. That doesnt work
because i might only have 1 phone number or the other. I do not know vb at
all is there a way to make this work
Click to expand...

Well... it's a bad idea, I fear.

Some people don't have phones.

I know a Fred Brown, Sr. and his son Fred Brown, Jr. Fred Jr. is out
of the house now but he once lived with his parents. That's a very
legitimate duplicate (unless you include the Suffix as part of the
name).

A better solution might be to CHECK for duplicates, say in the Form's
BeforeUpdate event; open a recordset or use DLookUp to see if the name
already exists and WARN the user that it's an apparent duplicate, and
let them choose whether to cancel or not. This might be something
like:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strMsg As String
Dim iAns As Integer

Set rs = Me.RecordsetClone ' get the form's records
' set up a query string resembling
' [LastName] = "O'Brian" AND [FirstName] = "Ryan"
strSQL = "[LastName] = " & Chr(34) & Me.LastName & Chr(34)_
& " AND [FirstName] = " & Chr(34) & Me.FirstName & Chr(34)
' See if there is such a person in the table
rs.FindFirst strSQL
If Not rs.NoMatch Then
strMsg = Me.[FirstName] & " " & Me.[LastName] _
& " already found. Add anyway?" & vbCrLf _
& "Yes to add, No to jump to that person, " _
& "Cancel to try another name:"
iAns = MsgBox(strMsg, vbYesNoCancel)
Select Case iAns
Case vbYes ' add anyway
' do nothing
Case vbNo ' Cancel edits, jump to record
Me.Undo
Cancel = True
Me.Bookmark = rs.Bookmark
Case vbCancel ' just cancel the add
Cancel = True
End Select
End If
End Sub


John W. Vinson [MVP]
 
Guys thanks for the help. Since i dont know vb i am going to make the default
value for the 2 phone fields 0 and make all four fields a unique key. then
do an update query to change the empty phone fields to 0. I have tried this
I have a database that i am trying to eliminate duplicate entries the fields
are FirstName,LastName, WorkPhone, and HomePhone. I realize i can have more
than 1 client with the same name that is why i am using phone as well. I
started at the table level and made a unique index of the 4. That doesnt work
because i might only have 1 phone number or the other. I do not know vb at
all is there a way to make this work
Click to expand...

Well... it's a bad idea, I fear.

Some people don't have phones.

I know a Fred Brown, Sr. and his son Fred Brown, Jr. Fred Jr. is out
of the house now but he once lived with his parents. That's a very
legitimate duplicate (unless you include the Suffix as part of the
name).

A better solution might be to CHECK for duplicates, say in the Form's
BeforeUpdate event; open a recordset or use DLookUp to see if the name
already exists and WARN the user that it's an apparent duplicate, and
let them choose whether to cancel or not. This might be something
like:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strMsg As String
Dim iAns As Integer

Set rs = Me.RecordsetClone ' get the form's records
' set up a query string resembling
' [LastName] = "O'Brian" AND [FirstName] = "Ryan"
strSQL = "[LastName] = " & Chr(34) & Me.LastName & Chr(34)_
& " AND [FirstName] = " & Chr(34) & Me.FirstName & Chr(34)
' See if there is such a person in the table
rs.FindFirst strSQL
If Not rs.NoMatch Then
strMsg = Me.[FirstName] & " " & Me.[LastName] _
& " already found. Add anyway?" & vbCrLf _
& "Yes to add, No to jump to that person, " _
& "Cancel to try another name:"
iAns = MsgBox(strMsg, vbYesNoCancel)
Select Case iAns
Case vbYes ' add anyway
' do nothing
Case vbNo ' Cancel edits, jump to record
Me.Undo
Cancel = True
Me.Bookmark = rs.Bookmark
Case vbCancel ' just cancel the add
Cancel = True
End Select
End If
End Sub

John W. Vinson [MVP]
Click to expand...
 
Guys thanks for the help. Since i dont know vb i am going to make the default
value for the 2 phone fields 0 and make all four fields a unique key. then
do an update query to change the empty phone fields to 0. I have tried this
on my test database and it works does anyone see any problems down the road
with this other than the jr , sr issue
Click to expand...

Yes, and even worse than the jr. issue.

What will you do if you have two people with the same name - and
neither one of them has a phone?

John W. Vinson [MVP]
 
we are a company that does business over the phone so all our clients have
phone numbers the only senerio i see is a son working for his dad at the same
office in which case we will use jr or sr
do you forsee any other problems doing it this way
 
we are a company that does business over the phone so all our clients have
phone numbers the only senerio i see is a son working for his dad at the same
office in which case we will use jr or sr
do you forsee any other problems doing it this way
Click to expand...

Just degraded performance from the use of three largish text fields
rather than one 4-byte number as a linking field. I'd really suggest
using an autonumber Primary Key and linking on it; if you want the
three-field unique index to insure uniqueness, it should be ok for
that purpose.

John W. Vinson [MVP]
 
I already have an autonumber primary key how would i go about linking it to
the other fields. Also there are 10 people using the system and between the
10 we only get 10 to 15 new entries a dayand at the most i could see us
getting 50 new clients a day. Thanks for the help
 
I already have an autonumber primary key how would i go about linking it to
the other fields. Also there are 10 people using the system and between the
10 we only get 10 to 15 new entries a dayand at the most i could see us
getting 50 new clients a day. Thanks for the help
Click to expand...

If the autonumber ID is in the same record, it is ALREADY linked -
it's right there already!

You would use the Primary Key as the link to *other tables* - I don't
know what information you are collecting about customers, but if you
have a Calls table or a Sales table or the like, it should have (and
may already have) a Long Integer CustomerID field. This should be used
in the Relationships window to link it to the autonumber CustomerID in
your table.

This is independent from the problem of assuring unique customer
identities; the multifield index will help, as will the Beforeupdate
code I posted earlier.

Neither of these will help, though, if one user enters "Bob Brown" and
another enters "Robert Brown" - or "Robert Browne". This is just a
problem where somebody's brain must be involved.

John W. Vinson [MVP]
 
Back
Top