There are three types of relationships in Microsoft Access: one-to-many, many-to-many and one-to-one. Table relationships are used for various reasons such as informing query, form and report designs. Fortunately, MS Access makes it easy to create table relationships before you develop other database objects.
Let’s take a closer look at the three different ways that data in one table can be related to data in another table.
One-to-Many Relationship
A one-to-many relationship is the most common type of relationship. It means that Table A has many matching records in Table B, but Table B only has one matching record in Table A.
Let’s look at an example. If you run a home business selling homemade scarves, the customer’s information will go into Table A. They may be assigned an ID number, or you may go by their name. In Table B, you list out their orders. Some customers may have one or two orders, while others will have 10 or more. However, when going from Table B to Table A, the records in Table B will always match with just one record.
Many-to-Many Relationship
A many-to-many relationship is a bit more complicated. You must consider both sides of the relationship because Table A and Table B will have many records.
To make this relationship work, you will need a third table, called a junction table. This table is responsible for clarifying the different relationships that can occur. Using the example above, this would mean that for every order, there might be many products, and for every product, there might be many orders.
One-to-One Relationship
In a one-to-one relationship, records in Table A have only one matching record in Table B, and vice versa. This relationship does not happen often because information that is related in this manner can be stored in the same table. However, if you have a table with many fields or you want to separate some of the information, you might benefit from a one-to-one relationship.
Real-world relationships are complicated, and so are Access relationships! If you have questions on creating table relationships, call the Arkware team. We are relationship experts – so to speak – and we’ll help you work through your troubles and come out stronger on the other side!
NICE
I have 1 table where it required to have 2 columns of ‘Now()’ expression.
I have 2 forms to key in the data. 1 form for pre data and another for post data. Both form based on same table. Unfortunately only the 1st form able to capture the ‘Now()’, when I key in date in 2nd form, it unable to record the ‘Now()’.
Please advice a way to solve this issue
Now() is an expression that does not require user entry of data. MS Access automatically supplies the “Now” moment in the field based on the system time.
thanks
with some examples in access with real tables and relationships
Hello Maike and thank you for your comment. I will send you an email so we can communicate and review real-world examples of tables and their relationship types.
What’s this…give direct definitions and stop giving trash to students!!!
Hi Amber, Thank you for reading our blog. We aim to provide helpful content for our readers and we continue to look for areas of improvements. Your comment about direct definitions has been noted.