How To Resolve Access Data That Gives You Déjà Vu

Access databases need solid, accurate foundations to work properly and a small mistake could lead to major problems further down the line if they're not sorted out quickly.

You might already be aware of the importance of these kinds of documents, especially if you have spent time and energy creating them. A major use for databases in companies is to keep track of customers and clients in relation to sales, delivery addresses and invoices. One wrong figure in this information could result in your customers receiving many more/less items than they actually require. The need for accuracy is paramount and when issues do crop up you have some techniques at your disposal allowing you rectify problems.

Unplanned problems

When you first putting together an Access database, it's likely that'll you'll bring in information from other sources. For example, Excel often provides the starting blocks you need when it comes to making tables that contain related data. You may want to link the customer name in one table, to their orders, which are held in a different location.

Sometimes when you blend tables and files there can be duplicates of data that cause confusion. This does not really relate to info that is exactly the same as that previously entered, but instead describes two (or more) records that define the same thing but in a different way, such as two spellings of the name of one customer.

Defining duplicates

As mentioned previously, duplicated data is not necessarily replicas of the same information. It can result in a number of ways and it's a good idea to keep a lookout for actions that lead to these outcomes. Sometimes fields contain very similar information, such as one company having two contacts when they are only meant to have one - this could have resulted by simple human error or blended tables could have added the extra contact.

Other examples of duplicated data may occur when two tables bearing nearly identical information are brought together into one database, which is common when inheriting data from other sources. Not all data that appears similar is duplicated and it's wise to recognise this before you go ahead and edit/delete the guilty fields.

For instance, you could have lots of orders for customers with the same name, but if other identifying information is different then there is not cause for concern - the name concerned is likely to be common. Queries are a good way of making sense of numbers, and data returned following equations may appear to be repeated, but this is often due to a lack of identifying factors in the information produced.

Editing/disposing of duplicates

Before you begin to make small but potentially impactful changes to your database, it's worth backing up the data in case things don't go as you planned. Also, consider allowing only yourself or the person making the changes to have exclusive access to the files concerned while editing so information conflict issues can't occur. When it comes to altering data, there are some simple steps that you can take and what you do depends on the type of duplicate you have.

If there have been some blending of information concerning two customers then edit as appropriate and make them distinct. If you have similar data regarding just one customer then it's wise to delete the extra record but check the remaining one features the correct information. Sometimes both fields may reflect two different clients, but the data is not correct in either of the fields - make adjustments as necessary.

Unfortunately if you're blending lots of information then you might have quite a few duplicates to find and alter. Rather than going through databases to find these, you're able to create queries that can bring back exact duplicates or those bearing similar information, as long as expressions are added to the relevant calculations/queries.