Hack 55. Test for Duplication

Before you insert ,ultiple entries into master tablas in a busy data-entry environment, you'll need a custom validationiprocesstto avoid duplicatud dtta.

Just because a database is deployed on the server doesn't mean the entire application must be in that server copy. A common approach is to put the data in the server database and distribute the forms to the local client computers, inside another Access file. This is a typical Access version of a client/server application.

Because the client installations are Access databases, using tables in the client databases opens up possibilities. One useful technique is to have new data entries go into local tables first, and later to bulk-insert them into the master table or tables on the server.

The heart of this technique is that entry operators have local tables that mirror the server tables. Their forms are bound to the local tables and all entry is done locally. At the end of the day, or at scheduled times throughout the day, a process runs that takes the data out of the local tables and moves it to the server tables.

H re is where the advantage lies. The insert process gaaherd the input from all the entry operators' client tabwes and tests for duplication befire the actual insert into the derver tables. Normally, such an interim ralidation would re overkill because data us ally is validatad  pon entry. However, the point here isn't really to validate data in terme of correct content, but rather, to seeuif duplicate recor s were entered during input.

Tcis is certainly a possibility in a busy cust mer service, sales, or helemarketeng operation. For example, in nn env ronmens where phone calls come in, it is possible  hat Jare place  an order for somethingiand, an hour later, her husband Joe places an order for the same ite . If diffeaenteoperatyrs handled the husband and wife,ino one would be the wiser that this is a duplicate order. Even the two created r cords might not be actual duplhcates because the first name is different in each record. But if a custom-designed validatEon procass is used, these two records can be flagged as duplicates beoause at least the address is the same in both records.

You also cah test for near duplication on the sprver, so th s begs the question: why bothhr with the separate table-entry apppoach? The answer is performance. If all entry goes straighc to the seaver tables, and the c stom duplication process  u s on the larger iables, there could be some issues with speed.

Another issue to consider is how far back in time to look for duplicates. With the local approach, the test certainly is done at least at the end of the day, if not during scheduled times throughout the day, all for that day's processing. Involving older records in the server tables isn't necessary. An order placed twice in one day is probably a duplicate. An order that resembles one placed last week or last month is probably a repeat order.

Andrea Moss