Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-08-2002, 11:14 AM   #1
vangogh228
Registered User
 
Join Date: Apr 2002
Posts: 302
Thanks: 0
Thanked 0 Times in 0 Posts
vangogh228
Searching/Updating multiple tables at once

I have encountered a database that has an occasional duplication because existing customers are occasionally entered as new. Sales and Service activity on the new record's related tables needs to be moved to the original main record and the new erroneous one deleted.

The customer records and data are related by a customer number field. Currently, the database operator is going to each of the underlying tables and manually changing the entry in the customer number field (the related field) to reflect the original number, then she deletes the new main record manually.

1. Is there a way of have the system recognize duplicates based on matching entries in multiple fields and, perhaps, stop entry? For example, could it look at First, Middle and Last names plus the Zip Code to eliminate duplication?

2. If that is too difficult, can I at least automate their current process, considering they have 5 underlying tables?

Thanks so much for any help!!

vangogh228 is offline   Reply With Quote
Old 11-11-2002, 05:57 AM   #2
RV
Registered User
 
Join Date: Feb 2002
Location: UK
Posts: 1,115
Thanks: 0
Thanked 0 Times in 0 Posts
RV is on a distinguished road
Duplicate Customers

How do you define "duplicate customers"?

In practice, "duplicate customers" in fact are not duplicate when you look at the data which was entered.
But they are duplicate 'cause the data entered actually represents the one and same customer.

It's hard to "automate" customers not being entered duplicate.
One suggestion is first to look up existing customers and check whether a customer (most propably) already exists.

Another possibility is to define an Before Update event in your Customer form which checks whether a Customer exists in your table Customer having the same Firstname, Middlename, Lastname AND Zipcode.

The basic code looks like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If NewRecord = True Then
If DCount("*", "Customer", "Lastname = '" & Forms!Customer!Lastname & "' AND Firstname = '" _
& Forms!Customer!Firstname & "' AND Zipcode = '" _
& Forms!Customer!Zipcode & "' AND Middlename = '" _
& Forms!Customer!Middlename & "'") <> 0 Then
MsgBox "Customer already exists"
Me.Undo
End If
End If
End Sub

RV
RV is offline   Reply With Quote
Old 11-12-2002, 08:02 AM   #3
vangogh228
Registered User
 
Join Date: Apr 2002
Posts: 302
Thanks: 0
Thanked 0 Times in 0 Posts
vangogh228
RV: I think I understand the principle, but I'm not familiar with DCount. What is the syntax for that command? I am intrigued by the "*" portion.

Thanks so much for the help!!!

Tom

vangogh228 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 04:18 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World