Site menu
Search
Map

View Larger Map
Calendar
«  March 2024  »
SuMoTuWeThFrSa
     12
3456789
10111213141516
17181920212223
24252627282930
31
Site friends
  • Create a free website
  • Statistics

    Total online: 1
    Guests: 1
    Users: 0
    Ads
     Relationships
    Microsoft Access - Relationships

    From this hour you will be able to learn about below topic –

    About Relationship
    Primary & Foreign key
    How to Create Relationships (One to Many)
    How to Create Relationships (Many to Many)
    How to Create Relationships: (One to One)

     
     
    About Relationship:

    An association that is established between common fields (columns) in two tables is called relationship. Or, Relationships mean a relation into two tables or more. At least two tables are needed for relationships. One is parent table which is control table. And another is child table which is related by parent table.

    Types of Relationships:
    There are 3 types of relationships.

    • One to one
    • One to many
    • Many to many

    One to one: In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A.

    One to many: In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.

    Many to many: In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A.

    About Defining Relationships:
    The kind of relationship that Microsoft Access creates depends on how the related fields are defined:

    • A one-to-many relationship is created if only one of the related fields is a primary key or has a unique index.
    • A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
    • A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields— the foreign keys from the two other tables.

    [Note: All related field's data type and value must have to be same.]

    About Primary & Foreign Key:

    One or more fields (columns) whose value or values uniquely identify each record in a table.

    Properties of Primary key:

    1. A primary key cannot allow Null values and must always have a unique index.
    2. A primary key is used to relate a table to foreign keys in other tables.

    Foreign key: A foreign key column refers to the values of primary key column of another table.

    Properties of Foreign key:

    1. A foreign key may allow Null values and duplicate values.
    2. By default, a foreign key reference the primary key attributes of the referenced table.
    To Create Relationships: (One to Many)

    Create a new database and create below tables –

    Table name:
    Authors

     

    Table name:
    Books

     

    Fields name:

    Data Type

    Fields name:

    Data Type

    AuthorID (Primary Key)

    Text

    BookID (Primary Key)

    Text

    AuthorName

    Text

    BookName

    Text

    Address

    Text

    Version

    Text

    City

    Text

    Price

    Currency

    Phone

    Text

    Qty

    Number

     

    AuthorID

    Text

    After create tables –
    • On the Tools menu, click Relationships…
    • Select Authors table from 'Show Table' list and click Add button > Select Books table and click Add button
    • Drag AuthorID field from 'Authors field list' and drop on AuthorID field of 'Books field list'
    • Select Enforce Referential Integrity > Click Create button
    • Close relationships window > Click Yes button
    • Open the Authors table and type data
    To Create Relationships: (One to Many)

    Create new database and create below tables –

    Table name:
    Departments

     

    Table name:
    Disease

     

    Fields name:

    Data Type

    Fields name:

    Data Type

    DepID (Primary Key)

    Text

    DiesesID (Primary Key)

    Text

    DepName

    Text

    DiesesName

    Text

    DepLocation

    Text

    Description

    Text

     

     

    DepID

     


    Table name:
    Doctors

     

    Table name:
    Patients

     

    Fields name:

    Data Type

    Fields name:

    Data Type

    Doctors ID (Primary Key)

    Text

    PatientID

    Text

    Doctors Name

    Text

    PatientName

    Text

    Fees

    Currency

    Address

    Text

    DiseaseID

     

    City

    Text

     

     

    DoctorsID

    Text

    After create tables –
    • On the Tools menu, click Relationships…
    • Select all tables by holding down 'Shift key' and click Add button
    • Drag DepID field from 'Departments field list' and drop on DepID field of 'Disease field list' > Select Enforce Referential Integrity > Click Create button
    • Drag DiseaseID field from 'Disease field list' and drop on DiseaseID field of 'Doctors field list' > Select Enforce Referential Integrity > Click Create button
    • Drag DoctorsID field from 'Doctors field list' and drop on DoctorsID field of 'Patients field list' > Select Enforce Referential Integrity > Click Create button
    • Open the Departments table and type data
    To Create Relationships: (Many to Many

    Create new database and create below tables –

    Table name:
    Customers

     

    Table name:
    Orders

     

    Fields name:

    Data Type

    Fields name:

    Data Type

    CustomerID (Primary Key)

    Text

    OrderID (Primary Key)

    AutoNumber

    CompanyName

    Text

    OrderDate

    Date/Time

    ContactName

    Text

    ShippedDate

    Date/Time

    ContactTitle

    Text

    ShipName

    Text

    Address

    Text

    ShipAddress

    Text

    City

    Text

    ShipCity

    Text

    Phone

    Text

    CustomerID

    Text


    Table name:
    Order Details

     

    Table name:
    Products

     

    Fields name:

    Data Type

    Fields name:

    Data Type

    Order ID (Primary Key)

    Number

    ProductID (Primary Key)

    AutoNumber

    ProductID (Primary Key)

    Number

    ProductName

    Text

    UnitPrice

    Currency

    UnitPrice

    Currency

    Quantity

    Number

    UnitsInStock

    Number

    Discount

    Number

    UnitsOnOrder

    Number

     

     

    Discontinued

    Yes/No

    After create tables –
    • On the Tools menu, click Relationships…
    • Select all tables by holding down Shift key and click Add button
    • Drag CustomerID field from 'Customers field list' and drop on CustomerID field of 'Orders field list' > Select Enforce Referential Integrity > click Create button
    • Drag OrderID field from 'Orders field list' and drop on OrderID field of 'Order Details field list' > Select Enforce Referential Integrity > click Create button
    • Drag ProductID field from 'Order Delails field list' and drop on ProductID field of 'Products field list' > Select Enforce Referential Integrity > click Create button
    To Create Relationships: (One to One)

    Create new database, create below tables and make relation (EmployeeID to EmployeeID).

    Table name:
    EmployeeOfficial

     

    Table name:
    EmployeePersonal

     

    Fields name:

    Data Type

    Fields name:

    Data Type

    EmployeeID (Primary Key)

    Text

    EmployeeID (Primary Key)

    Text

    LastName

    Text

    Gender

    Text

    FirstName

    Text

    Age

    Text

    Title

    Text

    Address

    Text

    Salary

    Currency

    City

    Text

    JoinDate

    Date/Time

    Division

    Text

     

     

    Phone

    Text

     

     

    Photo

    OLE Object

    ********************

    Copyright MyCorp © 2024