Preventing redundant data in database 1

[rtshort]

I have a database to keep up with clients. If the client is already in the database, I want to add a number to the #Visits column when they enter their information. Their Phone # is the "primary key" so I need to compare that with the information entered on the form. If the Phone # exists in the database I need the #Visits column to be updated with one added to it. Like

.fields("#Visits&quot.value = .fields("#Visits&quot.value + 1

I'm using a reference to ADO 2.5 to open the connection.
How would I go about comparing the Phone# field in code to see if it already exists.

 

[MichaelRed]

As far as I understand what you're trying, do an additional command with the ADO object to Retrieve the record with the phone number. Check for BOF & Eof on htat record set. If both are true, it is a new phone #, so you would add the client, otherwise you would increment the # visits.

You MIGHT want to confirm the phone #, as typos are going to be real pain for you. Both problems WILL occur.

1.[tab]The wrong # goes in - doesn't match so you get a new client. Number of visits will not be accurate. Number of clients will not be accurate.

2.[tab] the wrong # goes in - matches some one else's phone. # Clients is O.K., but two clients have wrong # of Visits.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jmarler]

Primary key=phone number?
What if your client changes his/her phone number? - Jeff Marler B-)

 

[rtshort]

Really won't make lot of difference if the phone # changes because I'll still have the number of visits, just in 2 columns. I choose the phone # for the primary key because no 2 would be alike. I thought about an auto # field and that may work better, but in either case I still need to increase the #Visits column by 1 at each logon.
Thx MichaelRed I'll give it a try.

 

[edderic]

sql statement Where Telphone _field = Eric De Decker
vbg.be@vbgroup.nl

License And Copy Protection AxtiveX.

Download Demo version on my Site:

http://www.createsoftware2000.com

 

[petermeachem]

Can't you say
UPDATE TABLE thing SET Visits = Visits + 1 WHERE Phone = 'whatever'

in a db.execute way

As a by the way, should you wish to add a phone number, do

on error resume next
db.execute insert into table thing phone values( 12345)

then if its there already it will just error on, so saving you from checking. Can't remember correct syntax sadly.

 

[MichaelRed]

Peter,


... senior moment?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[rtshort]

Thanks a million guys. Those were the ideas I needed, just had to figure out the code. FYI

sql = "Select * from DB Where CustPhone = '" & txtPhone.text "'"
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
With rs
If .EOF = True And .BOF = True Then
.AddNew
.Fields("CustPhone&quot.Value = txtPhone.Text
.Update
Else
.Fields("CustVisits&quot = .Fields("CustVisits&quot + 1
.Update
End If
End With
Exit Sub

I'm very new to this (3 Months or so).If there is a way to code it shorter, please let me know. It works like it is though. Every day above ground is a GOOD DAY!!!

 

[WilMead]

rtshort,

The question presented its own answer, it's dealing with a record using it's primary key thus preventing duplication from occuring.

The real question should be: "why does one need to compare the primary key field to see if it exists?"

Use this system action to your advantage:

1) add the customer record for count = 0 -- Ignore errors

2) increment the counter -- Handle errors

I think I'm simply restating PeterMeachem's post too.

Wil Mead
wmead@optonline.net

 

[rtshort]

I needed to compare the field, really any field, to see if the customer was already in the database. If the customer was in the database, I wanted to add 1 to the "#visits" column to keep up with the number of times the program was accessed by the user. Doing so will keep redundant data from swelling up the database to a huge size over the years and still let me see how many times it was accessed by each individual user.
The partial code I posted above, going on MichaelRed's ideas and thoughts, works perfectly.
Thanks MichaelRed for answering my question, and not trying to reinvent my wheel. I am new to this, but I think I know the direction I'm going, however I have been wrong before.
Thanks to all of you. Every day above ground is a GOOD DAY!!!