Remove duplicates from four different fields/columns

[gautam89]

Hi everyone,

I am working on a database to manage newsletter subscriptions. Each subscriber record has the option of having up to four email addresses registered to his/her name.

Is there a way to check for duplicate email addresses in the entire database? It would have to compare all values in all four fields of all records.

Any ideas on how to implement such a thing? I'm clueless...

Thanks!

 

[Fernando]

Hi, If ur design is like,

tblSubscriber:
fldSubscriberID as Pk
fldSubscriber
....

tblEmail:
fldSubscriberID as Fk
fldEmail

On General properties of fldEmail set Indexed to: Yes (No Duplicates)

If its like

tblSubscriber:
fldSubscriberID as Pk
fldSubscriber
fldEmail1
fldEmail2
fldEmail3
fldEmail4
...

Correct me if im wrong but I think you should change it to the first one because u never know if someday a subscriber can have 5 or more. Anyways i can think of a solution for this.

Every time a User is adding a record you will have to either use .findfirst with DAO or use 4 Dlookup Functions. Make sure u index those email fields.

Again (correct me if im wrong) as your db increases in size it will be slower with the second aproach than the first one.

 

[Rabbie]

Hi, If ur design is like,

tblSubscriber:
fldSubscriberID as Pk
fldSubscriber
....

tblEmail:
fldSubscriberID as Fk
fldEmail

On General properties of fldEmail set Indexed to: Yes (No Duplicates)

This illustrates the advantages of having a normalised database without repeating fields. This way the trapping of duplicates can be done easily and at very low cost.

The rules/recommendations actually make life easier.

End of sermon