MS Access 2010 Relationship & Query Interconnect

Advertisement

Access 2010 is very useful tool for designing database and provides intermediate-level data mining. It has done and continues to do wonders especially when it comes to managing, creating and most importantly making use of Relational Database up to its acme. This post will cover how relationships and queries interconnect to make database more dynamic.

To make the labyrinth easier to understand, go through how to create table and build relationships among them. To start off with, create tables, and apply desired constraints on the data types.

For Instance: Create three simple tables (defined primary key in each of them).

clip_image001

clip_image002

clip_image003

Create a new table by the name of Store Management, which will encapsulate all field labels (selected as primary key), in their respective tables. Add exactly same field names in Store Management table.

Insert Customer_ID, Product_ID and Staff_ID in Store Management table and set their Data Type to Number for avoiding conflict eruption. Set the ID as Primary key as shown in the screenshot below.

clip_image004

Now start building relationships among tables, navigate to Relationship Tools and click Relationships. Its tab will appear, add all the tables and connect primary key of Customer, products and Staff table with corresponding field name in Store Management table window. In Edit Relationship dialog enable Enforce Referential Integrity and Cascade Delete Related Records. Save and close it for later use.

clip_image005

Make query for pulling down data from tables and make use of relationships in a better way, go to Create tab and click Query Design. You can also make queries by using the wizard.

clip_image006

Upon click, Show Table dialog will appear, select all the tables and click Add. You can also add queries but since we haven’t made any query yet, we will leave it.

clip_image007

It shows Relationship among tables, as shown in the screenshot below.

clip_image008

For creating query, drag Customer_ID from Store Management table Window and drop it over the first Field column in bottom pane, then drag Customer Name from Customer table window and drop it over on second Field column (1st row), place second item from Customer table on third Field column and so on. Do exactly the same with Product_ID, drag it from Store Management and drop on the field column adjacent to Customer_Phone. Drag Staff_ID from Store Management and drop it over on field column next to sales. Once finished with populating fields, name the query and save it.

clip_image009

Click the screenshot above to enlarge

Now view this query in table view, On adding Customer_ID, you will notice that query will automatically update the info corresponding to specified customer id, this is because of relationship between tables have been built and Enforce Referential Integrity and Cascade Delete Related Records are enabled. So on deleting any record it will automatically deletes the corresponding records in its original table.

clip_image010

Click the screenshot above to enlarge

Now the relationships among tables and queries both working together, it make the database more cohesive and dynamic.

Advertisement