One of the goals of good database
design is to remove data redundancy (duplicate data). To achieve that goal, you
divide your data into many subject-based tables so that each fact is
represented only once. You then provide Microsoft Office Access 2007 with the
means by which to bring the divided information back together — you do
this by placing common fields in tables that are related. To do this step
correctly, however, you must first understand the relationships between your
tables, and then specify these relationships in your Office Access 2007
database.
After you have created a table
for each subject in your database, you must provide Office Access 2007 with the
means by which to bring that information back together again when needed. You
do this by placing common fields in tables that are related, and by defining
relationships between your tables. You can then create queries, forms, and
reports that display information from several tables at once. For example, the
form shown here includes information drawn from several tables applies only to
a Microsoft Access database (.mdb or .accdb).
What are table relationships?
In a
relational database, relationships enable you to prevent redundant data. For
example, if you are designing a database that will track information about
books, you might have a table named "Titles" that stores information
about each book, such as the book’s title, date of publication, and publisher.
There is also information that you might want to store about the publisher,
such as the publisher's telephone number, address, and ZIP Code/Postal
Code. If you were to store all this information in the "Titles"
table, the publisher’s telephone number would be duplicated for each title that
the publisher prints.
A better solution is to store the publisher's information only one time, in a separate table that we will call "Publishers." You would then put a pointer in the "Titles" table that references an entry in the "Publishers" table.
To make sure that you data stays synchronized, you can enforce referential integrity between tables. Referential integrity relationships help make sure that information in one table matches information in another. For example, each title in the "Titles" table must be associated with a specific publisher in the "Publishers" table. A title cannot be added to the database for a publisher that does not exist in the database.
A better solution is to store the publisher's information only one time, in a separate table that we will call "Publishers." You would then put a pointer in the "Titles" table that references an entry in the "Publishers" table.
To make sure that you data stays synchronized, you can enforce referential integrity between tables. Referential integrity relationships help make sure that information in one table matches information in another. For example, each title in the "Titles" table must be associated with a specific publisher in the "Publishers" table. A title cannot be added to the database for a publisher that does not exist in the database.
Types of table relationships
There are
three types of table relationships.
·
A
one-to-many relationshi
Consider an order tracking database that includes a Customers
table and an Orders table. A customer can place any number of orders. It
follows that for any customer represented in the Customers table, there can be
many orders represented in the Orders table. The relationship between the Customers
table and the Orders table is, therefore, a one-to-many relationship.
To represent a one-to-many relationship in your database design,
take the primary key on the "one" side of the relationship and add it
as an additional field or fields to the table on the "many" side of
the relationship. In this case, for example, you add a new field — the ID
field from the Customers table — to the Orders table and name it Customer
ID. Access can then use the Customer ID number in the Orders table to locate
the correct customer for each order.
·
A
many-to-many relationship
Consider the relationship between a Products table and an Orders
table. A single order can include more than one product. On the other hand, a
single product can appear on many orders. Therefore, for each record in the
Orders table, there can be many records in the Products table. In addition, for
each record in the Products table, there can be many records in the Orders
table. This type of relationship is called a many-to-many relationship because,
for any product, there can be many orders and, for any order, there can be many
products. Note that to detect existing many-to-many relationships between your
tables, it is important that you consider both sides of the relationship.
To represent a many-to-many relationship, you must create a third
table, often called a junction table, that breaks down the many-to-many
relationship into two one-to-many relationships. You insert the primary key
from each of the two tables into the third table. As a result, the third table
records each occurrence, or instance, of the relationship. For example, the
Orders table and the Products table have a many-to-many relationship that is
defined by creating two one-to-many relationships to the Order Details table.
One order can have many products, and each product can appear on many orders.
·
A one-to-one
relationship
In a one-to-one relationship, each record in the first table can
have only one matching record in the second table, and each record in the
second table can have only one matching record in the first table. This type of
relationship is not common because, most often, the information related in this
way is stored in the same table. You might use a one-to-one relationship to
divide a table with many fields, to isolate part of a table for security
reasons, or to store information that applies only to a subset of the main
table. When you do identify such a relationship, both tables must share a
common field.
Set the join type
When you
define a table relationship, the facts about the relationship inform your query
designs. For example, if you define a relationship between two tables, and you
then create a query that employs those tables, Access automatically selects the
default matching fields based upon the fields specified in the relationship.
You can override these initial default values in your query, but the values
supplied by the relationship will often prove to be the correct ones. Because
matching and bringing together data from more than one table is something you
will do frequently in all but the simplest databases, setting defaults by
creating relationships can be time saving and beneficial.
A multiple
table query combines information from more than one table by matching the
values in common fields. The operation that does the matching and combining is
called a join. For instance, suppose you want to display customer orders. You
create a query that joins the Customers table and the Orders table on the
Customer ID field. The query result contains customer information and order
information for only those rows where a corresponding match was found.
One of the
values you can specify for each relationship is the join type. The join type
tells Access which records to include in a query result. For example, consider
again a query that joins the Customers table and the Orders table on the common
fields that represent the Customer ID. Using the default join type (called an
inner join), the query returns only the Customer rows and the Order rows where
the common fields (also called the joined fields) are equal.
However,
suppose you want to include all Customers — even those who have not yet
placed any orders. To accomplish this, you must change the join type from an
inner join to what is called a left outer join. A left outer join returns all
of the rows from the table on the left side of the relationship and only those
that match from the table on the right. A right outer join returns all of the
rows on the right and only those that match on the left.
Note In
this case, "left" and "right" refer to the position of the
tables in the Edit Relationships dialog box, not the Relationships
window.
You should
think about the result you will most often want from a query that joins the
tables in this relationship, and then set the join type accordingly.
Set the join type
1.
In the Edit
Relationships dialog box, click Join Type.
2.
The Join
Properties dialog box appears.
3.
Click your
choice, and then click OK.
The
following table (using the Customers and Orders tables) shows the three choices
that are displayed in the Join Properties dialog box, the type of join
they use, and whether all rows or matching rows are included for each table.
Choice
|
Relational join
|
Left table
|
Right table
|
1. Only include rows where the joined fields
from both tables are equal.
|
Inner join
|
Matching rows
|
Matching rows
|
2. Include ALL records from 'Customers' and only those records
from 'Orders' where the joined fields are equal.
|
Left outer
join
|
All rows
|
Matching
rows
|
3. Include ALL records from 'Orders' and
only those records from 'Customers' where the joined fields are equal.
|
Right outer join
|
Matching rows
|
All rows
|
When you
choose option 2 or option 3, an arrow is shown on the relationship line. This
arrow points to the side of the relationship that shows only matching rows.
Enforce referential integrity
The purpose
of using referential integrity is to prevent orphan records and to keep
references synchronized so that you don't have any records that reference other
records that no longer exist. You enforce referential integrity by enabling it
for a table relationship. Once enforced, Access rejects any operation that
would violate referential integrity for that table relationship. Access rejects
updates that change the target of a reference, and also deletions that remove
the target of a reference. To have Access propagate referential updates and
deletions so that all related rows are changed accordingly
The Enforce Referential Integrity option
If you
select the Enforce Referential Integrity
option Access will make sure that each record in the Order table refers to an existing record in the
Customer table. Selecting this option makes it impossible to create Order
records that refer to a non-existent customer. You should select Enforce
Referential Integrity by default, because it protects the integrity of your
data.
The Cascade Update Related Fields option
If you
select the Cascade Update Related Fields option Access will change the foreign
key if the primary key it refers to changes. In case of our one-to-many example
this means that if the primary key of a customer (one) changes, Access will
automatically update the foreign keys that refer to this customer in the Order
table (many). The Cascade Update Related Fields option also protects the integrity
of your data as it prevents records from becoming detached from their related
records.
The Cascade Delete Related Records option
The Cascade
Delete Related Records option ensures that whenever a Customer (one) record is
deleted, than the related records (many) in the Order table are also deleted.
You should select this option if the many-part of the relationship has no use
or is not needed anymore without the one-part. In case of our one-to-many
example this would mean that when a customer is deleted also his/her orders are
deleted from the order table as well.
Now let’s Start Create
Relationship in Access Database from Sql Database.
Now Select your Database from Sql
which one you want to Export>Select task>Select Export Data.
Now Sql Server Import and Export
wizard will open.
Now Click Next Choose Data Source
From will open in that Form Select DataSource, Server Name and Database from
Different DropDownlist. This is for Exporting Data from Which Database and
server.
Now Click Next to Perform Next
action. Then destination Form will open form Destination Dropdown select
Microsoft Access then Select Browser Button to open your Microsoft Access
Database and Select your Access Database.
Now your selected file will be on
that Filename Textbox.
Now click next then form will
open for where you want to Copy your data. And how many table or views you want
to take.
Click Next to Select particular
tables or views from your current database.
Click Next for Data Mapping from
your Current database.
Now run package form will open.
Click next to see your complete
form details will open from which database you are taking and where you want to
specify your database. If any error will occurs then immediately show In this
form.
Now click next to perform
operation.
After completing this step you
will get successfully complete form to complete this step in Sql.
Now in your Sql all step are
completed to take table or view. Now open your Access Database file which one
you selected while above action to store your tables or views.
Now go to database tools tab and
from that tab select relationships item to create relationship between tables.
Now right click on open
relationships form and select Show Table option from open dialog form.
Now Show table form will open and
select tables from that from how many you want to open for relationship.
Then click Add button to open it
in form. There you can see your selected tables will be on relationship form.
Now if you want to create
relationship between field and select field from table and drop in another table’s
field which field with you want to relation then Create relationship form will
open for create that. Read complete above content for what kind of relationship
you can create.
Click on Create and there you can
see your relation in that filed from different tables.
Thanks for comments.....