A happy table relationship? While it may conjure up images of a couple clasping hands across their entrée in a restaurant, it has a more serious meaning in business - removing data that you don't need.
Surely you're thinking, well, why would I put unwanted data in my Access database anyway? The key to table relationships is to remove data you didn't know was duplicating itself. Think of any typical commercial database and it will be full of this kind of data. Let's say you have a small database of customers for a mail order business. You'll have data relating to names, addresses, products purchased and so on. However, think of other lumps of data - your mailing list (repeating the names and addresses), same with keeping tabs on customers behind on a payment (repeating everything), or even the obvious - a repeat order will create duplicate data if you're entering the person as a new customer every time, (you wouldn't remember everyone's individual order, after a while, so this is entirely possible). The answer to reducing this kind of redundant repetition comes with table relationships.
If you're a regular Access user you may already know that Access can have common fields of data - such as names and addresses - that can be called upon by other parts of the database, which removes the risk of duplicity. However, you can co-ordinate this data further with a table relationship. There are several different kinds.
One-to-many is the simplest. Taking the above example of a repeat order from a customer - you have the one (customer) and their (many) orders. The table relationship would link them up. The next type is many-to-many. Using the same example, let's say that the customer's order had more than one thing in their shopping cart. Therefore the (many) repeat orders contain (many) other items within them. Finally, there's the one-to-one. Let's say that you have a "bad debt" list - it will flag up should the (one) customer try to place an order and they have (one) strike against their name.
Access makes it relatively easy to create table relationships from your existing data. There is a Relationships window available that you can use with your database objects. You can either tell Access to join up different tables of data (e.g. names, addresses) or it has a built-in facility to create a join with one of the help wizards. Table Relationships are then visually represented with a line between the two (ore more) tables you are referring to in your query.
Once created, table relationships are quite malleable, much like the rest of your database. You can view, delete, edit or have Access provide you with a breakdown report of all the different table relationships that you currently have in your database. Some of the more commonly used ones (usually one-to-many) can be created by dragging your data field and consulting the lookup wizard - automatically making the relationship for you.
Although they seem quite complex at first, once you start with some of the automatic relationship features in Access, you will find editing and creating new ones much easier. It prevents your data from getting clogged with unnecessary repetitions, while also reinforcing the integrity of your database. Your working relationship will Office be a lot happier as a result!
You have permission to publish this article for free providing the "About the Author" box is included in its entirety.
Do not post/reprint this article in any site or publication that contains hate, violence, porn, warez, or supports illegal activity.
Do not use this article in violation of the US CAN-SPAM Act. If sent by email, this article must be delivered to opt-in subscribers only.
If you publish this article in a format that supports linking, please ensure that all URLs and email addresses are active links, without the rel='nofollow' tag.
Software Training London Ltd. owns this article. Please respect the author's copyright and above publication guidelines.
If you do not agree to these terms, please do not use this article.
"Really enjoyed the course and Wendy was fab ! Thanks"
PSI CRO AG CRA II Ivin Cabatic
"Awareness of behavior styles of colleagues and people we are working with is beneficial in many ways."
Faculty Of Occupational Medicine Professional Development Manager Alannah Harrison
"The training was good, it expanded on parts of the process I was already doing, albeit without the structure or consciously thinking of 'why' I was doing it that way - which will help me improve in future. Most helpfully, it also taught me more of the process to start doing.
Training via video meeting would not be my first choice for delivery - I appreciate there isn't another option at present due to the pandemic. Thankfully, there were no IT issues to distract attention from the content.
Dennis is a knowledgeable trainer, patient and has a very approachable attitude. Some other varied practical examples/scenarios to put the training into context would have helpful."
Access articles
