Access Chapter 1

1. Access Chapter 1

2. objectives • Tables • Queries • Forms • Reports • Primary and Foreign Keys • Relationship

3. example • Download Books.accdb file. • Access database system files have .accdb as filename extension.

4. Database Window • In the Object Bar on the left • Tables – Where the data is stored • Queries – Questions asked about the data • Forms – Provide an interface to view & maintain records • Reports – Display information in a nicely formatted way

5. Opening a table • In the Tables area, double click Authors • You should see 7 records showing in Datasheet view • Datasheet view – Allows us to see the records that have been entered into a table. • Number of records is shown at the bottom bar.

6. Datasheet View • Datasheet view shows a table’s contents in rows and columns like a spreadsheet would • Each Field Name is inside a clickable “Field Selector” that will highlight that column • Each row has a clickable “Record Selector” to its left…

7. Navigating a Datasheet

8. Table views • Datasheet View :Allows the user to add, edit, and delete records within a table. • Design View :Allows the user to create tables, add and delete fields, and modify field properties. • Fields can be assigned data types, such as text (the default), number, currency, etc. • Field properties show the characteristics of the fields in more detail.

9. Primary and foreign keys • Primary Key: To uniquely identify records in a table. • Foreign Key: The primary key of other table present in it. i.e., a borrowed Primary Key. • E.g. Author ID is primary key of Author table. AuthorCode(which is Author ID) in Books table is Foreign key.

10. Adding Records • Click the “New Record” button at the bottom • Enter 18 for the Author ID • Enter “C.S.” for a Author’s Last Name. • Enter “Lewis” for a Author’s First Name. • Save the table and close it.

11. Save and close • Do remember to Save and Close each object before moving to the other one! • Work systematically. • Always name the objects etc., with appropriate names as per context.

12. Editing a Record • Edit record 7 in Author table with your first and last names. • Press Enter or Tab • The record has been saved when the pencil sign to the left disappears

13. Deleting a Record • Deleting a record is permanent  once it’s deleted, it’s gone. You can’t get it back. • Delete Author ID 12 record.. • Select the record by clicking on the left “Record Selector” next to 12 • Right Click • Delete Record – Confirm that  It’s GONE

14. Forms • They are used to add, maintain, and view records in a database • Easier to use if you are not so familiar with the database interface • Make it easy for anyone to work with information in a table in a simple interface

15. Using a Existing Form • Close the Table • Under “Forms” in the object bar, Double click Maintain Authors Click the last record button to see your entries added and edited.

16. Adding Records with Forms • Use the controls to return to the first record • You should see 8 records. • Create a new record: Author ID as 19, First Name as Morris and Last Name as Mano.

17. Reports • A report is an output of data arranged in the order you specify.  • Reports can perform calculations and display the results.  • Reports can be used to print data.

18. Using an Existing Report • Close the form • In Reports object bar area Double click Publishers, Books and Authors Report • Find your two entries under the appropriate publishers

19. Queries • A Query is a question one asks about the data stored in a database • Access responds by displaying specific records that answer the question • In creating a query, we tell access which fields are needed and what criteria needs to be met

20. An Existing Query • Close the report • In the Queries area of the objects bar, Double-click Publishers, Books, and Authors Query • You should see your 15records

21. Understanding Table Relationships • Efficiently combine data from related tables • To create queries, forms, and reports • Primary and foreign keys play an important role in creating relationships. • Tables may be joined based on a common field.

22. Types of relationships • One-to-one relationship • One-to-many relationship • Many-to-many relationship • Access will take care of it for us!

24. Referential integrity • To establish tighter relationship! • It prohibits the user from deleting a record from one table when there are related records in other tables. • It allows you to enter a foreign key value only after entering it as primary key value in the primary table.

25. Establishing Referential Integrity • Edit Relationships dialog box • Select Enforce Referential Integrity checkbox

26. Cascade options • To establish dependencies between joined tables. • Cascade Update Related Fields: When the primary key is modified in a primary table, Access will automatically update all foreign key values in a related table. • Cascade Delete Related Records: When the primary key is deleted in a primary table, Access will automatically delete all records in related tables that reference the primary key(Deletions….Be cautious!)

27. Set Cascade Options