Textbook Notes (362,807)
Canada (158,054)
Dr.Hurst (18)

Computer Science - Access Notes.docx

11 Pages
Unlock Document

McMaster University
Computer Science

ACCESS – TUTORIAL 1 Introduction to Database Concepts Organizing Data  Field: single characteristic/attribute of a person, place, object, event or idea  Ex. Customer ID, first name, last name, phone number, contract amount, etc.  Table: collection of fields that describe person, place, object, event or idea  Field value: value or content in a field  Record: set of field values (each row of field values is a record) Databases and Relationships  Relational database: collection of related tables  Common field: a field that occurs in separate tables that can be used to connect records in those tables  Primary key: field/collection of fields whose values uniquely identify each record in a table (no record has the same primary key value)  Foreign key: when a primary key from one table exists in another table but not as the primary key  Foreign key does not have to have unique values, two tables are related Relational Database Management Systems  Database management system (DBMS): software program that lets you create databases and manipulate data in them  Relational database management system: o Data organized as collection of tables o Relationship between tables formed through common field o Allows you to create database structures containing fields, tables, table relationships o Can easily add new records, change field values in existing records and delete records o Contains built-in query language (lets you get immediate answers to questions about data) o Has built-in report generator (lets you produce professional-looking formatted reports) o Protects database through security, control and recovery facilities  More than one user can enter, retrieve and analyze data from database  Holds massive amounts of data Creating a Database  First screen that appears is Backstage view  Can get info about current database, create new one, open existing one  Template: predesigned database that makes it quick and easy to create new database Creating a Table in Datasheet View  Tables contain all of data in database  Should know the purpose of each field in a table  Should use descriptive names for fields that won’t take up a lot of space Renaming the Default Primary Key Field  ID field is default primary key  Can rename default primary key field to select most appropriate field Changing the Data Type of the Default Primary Key Field  Data type: determines what field values can be entered in a field  AutoNumber: a data type that automatically inserts a unique number into field for every record (good for primary key)  Text: data type that allows values with letters, digits, other characters not used in calculations Adding New Fields  Can use Add & Delete group on fields tab or Click to Add column in table to add new fields to datasheet  Datasheet view: shows contents of a table in rows (records) and columns (fields)  Field selector/record selector selects entire column or row Saving a Table  New records immediately stored in database when entered  Table’s design (field names, layout changes) not saved automatically  Save a table using a name that best identifies the info it contains  Access will sort records in primary field in order when saved  Cannot add new record in middle of table, has to be at bottom Opening a Table  Open table (or any Access object) by double-clicking its name in Navigation Pane Copying Records from Another Access Database  Can copy/paste records from a table into a database  Must have same structure as existing table  I.e. same fields, same design and characteristics in same order Navigating a Datasheet  Can use vertical and horizontal scroll bars to navigate  Navigation buttons can also be used to move vertically through records  Buttons and descriptions on AC p. 27 Creating a Simple Query  Query: a question you ask about the data sorted in a database  Tell Access which fields you need and what criteria should use to select records to answer question  Access displays only desired info so don’t have to navigate though all of data  Use Simple Query Wizard to create queries  Select wanted fields from Available Fields and move to Selected Fields  Query results not stored in database  Query design stored as part of database with specified name Creating a Simple Form  Use a form to enter, edit and view records in a database  Present data in many customized and useful ways  Displays one record at a time so you can focus on values for one record  Layout view: view forms are displayed in  Use navigation buttons to display different records in form Creating a Simple Report  Report: formatted printout (screen display) of the contents of one or more tables/queries  Creates a report based on selected table or query  Offers a more visually appealing format for data  Dotted lines show where text will print on the page  Layout view can be used to resize columns so they appear on the same page  Print Preview shows how report will look when printed Viewing Objects in the Navigation Pane  All Access Objects: lists all database objects (tables, queries, forms, reports)  Arrow on All Access Objects bar displays menu for ways to group/display objects in Navigation Pane Managing a Database  Manage databases to ensure they operate in most efficient way and data they contain is secure Compacting and Repairing a Database  Space occupied by deleted/replaced records does not automatically become available for other records  Compacting: rearranges data and objects in a database to decrease file size  Makes more space on disk and can open and close database quicker  Use Compact & Repair Database option to fix damaged database  Might become damaged if exit Access too suddenly  Use Compact on Close option to compact and repair every time you close Backing Up and Restoring a Database  Backing up: making a copy of database file to protect against loss or damage  Access provides default filename for backup copy using same filename as database, an underscore, then the current date ACCESS – TUTORIAL 2 Guidelines for Designing Databases  Identify all the fields needed to produce the required information  Organize each piece of data into its smallest useful part (ex. Last Name is more useful than using first and last name)  Group related fields into tables (ex. Use different tables for contracts, invoices and customers)  Determine each table’s primary key o Some tables have one primary key, some have two o Composite key: two primary keys o Include a common field in related tables (use common field to connect one table logically with another)  Avoid data redundancy (when same data is stored in more than one place) o Wastes storage space and can cause inconsistency if something is typed a different way (ex. Short forms) o Only used when forming connection between two tables  Determine the properties of each field o So that DBMS knows how to store, display and process field values o Ex. Field name, max number of characters, description, valid values Guidelines for Setting Field Properties Naming Fields and Objects  Must name each field, table, other object in database  Access stores items using names  Should choose a name that describes purpose or contents of the field/object Assigning Field Data Types  Data type determines what field values can be entered for field and what properties field will have  AC p. 55 has list of data types and their descriptions Setting Field Sizes  Field Size property: defines a field values maximum storage size  Used for Text, Number and AutoNumber fields only Setting the Caption Property for Fields  Caption property: specifies how field name is displayed in database objects  Access displays field name as column heading if you do not set Caption property Changing the Format of a Field in Datasheet View  Formatting a field changes way data is displayed but not actual values  Formatting group on the Fields tab in Datasheet view allows modifications  Ex. Do not need decimal places if know the amounts will never contain cents  Can add custom formats for ones that are not already pre-set (ex. For dates)  Yes/No data type used to define fields that store true/false, yes/no and on/off field values Creating a Table in Design View  Enter field names  Define properties for the fields o Enter values for Field Name, Data Type and Description field properties o Select values for all other field properties in the Field Properties pane o Properties will appear when move to first row’s Data Type box  Specify a primary key for the table o Primary key uniquely identifies each record in a table o Does not allow duplicate values in the primary key field o Entity integrity: Access forces you to enter value for primary key field o Null value: if no value is entered for a field o Records are stored by default in order by values of the primary key  Save the table structure o Table is stored in the database file when you save the table structure o Can use table to enter data into the table once it is saved Modifying the Structure of an Access Table  Changes Made in Design View  Moving a field: o Use mouse to drag field to new location in Table Design grid  Adding a Field: o Select row below where you want new field to be inserted o Click Insert Rows button in Tools group on Design tab  Changing Field Properties o Can change default size of 255 in a Text field (or other default settings for other data types) o May be too large for data contained in these fields o Setting a Caption property for a field can include a space between words in the field name Adding Records to a New Table  Add records to a table in Datasheet view  Spacebar can insert/remove a checkmark from a Yes/No field  To insert same value for a column as in the previous record press Ctrl + ‘ Importing Data from an Excel Worksheet  Can bring data from other files (ex. Excel or Word) into Access  Import: process that allows you to copy data from a source without having to open the source file  Columns in Excel worksheet must match the names and data types of the fields in the Access table  Actual Access file names must match not just column headings displayed because of the Caption property Creating a Table by Importing an Existing Table Structure  Can import an Access table and its records or just the table structure into database  Can modify imported table to produce the structure you want  Adding fields Using the Data Type Gallery: o Allows you to add a group of related fields to a table at the same time o Quick Start selection: group of fields that are added o Ex. Address Quick Start adds Address, City, State, Province, etc. Modifying the Imported Table  Deleting Fields from a Table Structure: o Can delete fields from Datasheet view or Design view o Deleting a field also deletes all the values for that field  Renaming Fields in Design View o Edit the names in the Table Design grid o Can also edit names in Datasheet view  Changing the Data Type for a Field in Design View: o Can also change in Datasheet view o Design view shows the data type in its own column o Shows list of all the dat
More Less

Related notes for COMPSCI 1BA3

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.