My Access Handout 1

Introduction to Databases and Microsoft Access What is a Database? A database is:

• • •

basically a collection of data or pieces of information an organized collection of information stored on a computer intended for storing and maintaining large amounts of information

The following are examples of the sort of information that can be kept in a database: • • • • • • •

Address book Telephone directory Library catalogue Stock list/Inventory List List of enrolled students, their personal information & subjects Payroll systems Music collection catalogue

Microsoft Access is a computer application used to create and work with databases. In computer jargon that means it’s a Database Management System or DBMS. With a computerized database in Microsoft Access, you can store information and do so much more. Like: o o o o o o

Print all telephone numbers in a certain area Print all telephone numbers having the same family name or beginning with a certain letter / letters Print all books with the same author Print all books that are related in topic/subject Print all students enrolled per college, per subject or per section Print the schedule or personal information of a student

Advantages of database processing: 1. Economy of scale 2. Getting more information from the same amount of data 3. Sharing of data 4. Balancing of conflicting requirements 5. Enforcement of standards 6. Controlled redundancy 7. Consistency 8. Integrity 9. Security 10. Flexibility and responsiveness 11. Increased programmer productivity 12. Improved program maintenance 13. Data independence

Disadvantages: 1. size 2. complexity 3. cost 4. additional hardware requirements 5. higher impact of a failure 6. recovery is more difficult

Relational Databases Types of database: Flat file databases -

store information in a single table A flat file is a file that contains records, and in which each record is specified in a single line.

-

-

this type of database often contains duplicate information easy to set up, but is not very flexible or efficient for storing large amounts of information The classic example is a basic name-and-address list, where the database consists of a small, fixed number of fields: Name, Address, and Phone Number.

Relational databases -

-

store information in separate tables one can use relationships to bring together information from different tables more powerful, flexible, and effectively stores large amounts of information faster and easier to maintain than a flat file database in a relational database, data is stored in tables made up of one or more columns (Access calls a column a field). The data stored in each column must be of a single data type such as Character, Number or Date. A collection of values from each column of a table is called a record or a row in the table.

Components of an Access Database

-

-

Tables Queries Forms Reports Data Access Pages Macros & Modules

Key terms Database File - This is your main file that encompasses the entire database and that is saved to your hard-drive or floppy disk. Example) StudentDatabase.mdb Table - A table is a collection of data about a specific topic. There can be multiple tables in a database. Example #1) Students Example #2) Teachers Record – A collection of values from each column of a table. Also called a row in the table. Field - Fields are the different categories within a Table; stores specific data or item. Tables usually contain multiple fields. Example #1) Student LastName Example #2) Student ID number Data-type - are the properties of each field. A field only has one data type. FieldName Datatype 1. Tables

-

-> Student LastName -> Text

A table is where the actual data being stored is kept. A table is a collection of records that can be divided into fields. A table is a lot like a spreadsheet. Each field holds a single piece of information about the record in which it resides. properties include the field names, the data type used for each field, and the indexes defined for the table, among other items.

Example of Table: Customer Table (3 Records)

Customer ID

Customer Name

Customer Address

Customer Phone

1 2 3

ABC Plumbing Jack’s Emporium Millie’s Pizza

Field#1 





Field#2

201 W. 44th St 1155 Corner Ave. 108 Ponting Field#3

(317)555-2394 (317)555-4501 (317)554-2349 Field#4

Access stores each database entry (such as each client or each inventory) in its own row; this is a record. For example, all the information about White Clover Markets, including the Address, City, State, and ZIP code, forms a single record. Each type of detail is kept in its own column: a field. For example, Client ID is one field, and Company Name is another. All the company names in the entire table are collectively known as the Company Name field. At the intersection of a field and a row is the individual bit of data for that particular record; this area is a cell.

Each database file can have many tables. For instance, you might have one table that lists all your customers and another table that lists information about the products you sell. A third table might keep track of your salespeople and their performance. Field naming rules: Field names may contain up to 64 characters and can include spaces and any symbols except periods, exclamation marks, accent grave symbols or square brackets. FIELD VALID DATA TYPES: 1. TEXT – which include numbers, letters and symbols. A text field can contain up to 255 characters. 2. MEMO – same with text data type except this one doesn’t have a maximum field length. So you can type an almost infinite amount of text. 3. NUMBER – ordinary number (not a currency value or date) 4. DATE/TIME – just that – a date or time. 5. CURRENCY – a number formatted as an amount of money. 6. AUTO NUMBER -- a number that Access automatically fills in for each consecutive record. 7. YES/NO – the answer to true/false question. It can contain either of two values, which might be Yes or No, True or False, On or Off. FIELD PROPERTIES 1. FIELD SIZE --- The maximum number of characters a user can input in that field. 2. FORMAT --- a drop – down list of the available formats for that field type. 3. Default Value – If a field is usually going to contain a certain value( for instance, a certain ZIP code for almost everyone), you can enter it here to save time. 4. DECIMALS PLACES – For number fields, you can set the default number of decimal places a number will show. 5. REQUIRED --- Choose Yes or No to tell Access whether a user should be allowed to leave this field blank when entering a new record. 2. Queries

-

-

queries are used to search, view, and modify the data that exists in the tables The typical query is used to return data that meets specific criteria. For example, you might create a query to select all students that have the same last name

Like tables, queries also have a Design View window. Actually, queries have two different design views: the Design Grid and the SQL View. The Design Grid provides you with a user-friendly means of creating and modifying queries. The SQL View allows you to modify the actual code used to define the query using Structured Query Language. This is a computer language that is used when you create database queries. 3. Forms

-

-

Forms allow you to create forms that can be used to enter, edit, and search your data After a form is created, it looks and operates like a Windows application A form may contain text boxes for data entry, labels to identify the various elements on the form, and buttons to perform other actions. The form also has a record selector. This is the series of buttons on the bottom edge of the form. The record selector allows you to navigate among the records in the underlying data.

-

-

All the data you enter into your database ends up in a table for storage. You can enter information directly into a table, but it’s a little bit awkward to do so. Most people find it easier to create a special on-screen form in which to enter the data. A form resembles any fill-in-the-blanks sheet that you might complete by hand, such as a job application. Access links the form to the table and stores the information you put into the form in the table. You can use a single form to enter data into several tables at once. These are called multitable forms.

4. Reports -

-

Reports are specially formatted collections of data, organized according to your specifications. Reports enable you to output data to any number of destinations (such as your printer or an email message) in an easy-to-read format. You can send reports to a printer or you can export the report to any number of formats. You can even publish your reports on the Internet or your company’s intranet. Reports, like forms, use an underlying data source, either a table or a query, to provide the actual data. The report’s design dictates how the data will be presented when the report is printed, previewed, or exported.

5. Data Access Pages -

are much like Forms but it display and but it can also interact data via internet or on web browser.

6. Macros

-

are sets of high level commands that can be used to process data and perform repetitive tasks. You can define a macro to open a specific report in the Print Preview window, for example. Macros are useful in automating Access.

7. Modules -

contain Visual Basic procedures and functions allow you to, for example, to work with data one record at a time and perform some operation on each record.

How the parts fit together Even though you create tables, reports, forms, and queries in separate steps, they're all related. Tables are the central focus of all activities-all the other objects do something to or with the table data. Reports summarize and organize the table data; forms help you enter information into the table; queries help you find information you want to use in the table.