COMPUTING ENVIRONMENTS MS ACCESS

Download and extract student data files from www.prenhall.com/essentials. Just Level 1 for now.

Also download this example of students registered for courses, university.mdb, because I will use that example to show you everything you need to know for the exam and final.

Access Level 1 Project 1.

• database: related data organized for access such as answering queries
• instead of ``Save As ...'' use backup database
• an Access database file contains multiple objects: tables, forms, reports, queries
• database tables store data in records (rows) with fields (columns)
• forms are for more user-friendly data entry than filling table rows, also for viewing and editing tables
• design view for creating tables, forms, reports, queries
• also wizards for creating these

Access Level 1 Project 2.

• create new empty database
• primary key
• foreign key
• table design
  • PetPals database has three tables: Franchises, Franchise Owners, and Franchise Departments
  • Franchises table has fields: FranchiseID (primary key), FranchiseName, Street, City, State, ZIP, Phone, TollFree, FranchiseOwner
  • Franchise Owners table has fields: OwnerID (primary key), FirstName, LastName, Address, City, State, ZIP, HomePhone, FranchiseID (foreign key)
  • Franchise Departments table has fields: DepartmentID (primary key), Department
  • because of the FranchiseOwner field in the Franchises table, each franchise has one and only one owner; the Franchises table and the Franchise Owners table have the same number of rows; the FranchiseOwner field is deleted in Project 4 so that a franchise can have multiple owners but a person can own only one franchise
• AutoForm: simple form for entering table data one row at a time
• AutoReport: simple report that lists the data in a table
• AutoNumber field
• import table data from Excel
• create a new database using the wizard
• switchboard

Access Level 1 Project 3.

• modify table field properties
• modify table layout
• update/edit/delete records
• sort records by other than primary key, change sort order
• filter records by selection (using one field)
• filter records by form (using multiple fields, AND, OR)
• filter records by form is a simple form of query
• print table data

Access Level 1 Project 4.

• edit/add/delete table fields
• deleting a field containing redundant information, the FranchiseOwner field in the Franchises table
• after the FranchiseOwner field in the Franchises table is deleted, a franchise can have multiple owners but a person can own only one franchise; Franchises table has a one-to-many relationship with the Franchise Owners table; however, each table has the same number of records so right now each franchise has a single owner
• yes/no field data type
• picking primary keys: each table needs a field or fields with a unique value in each row designated as the primary key for the table
• the FranchiseID field is the primary key for the Franchises table
• the OwnerID field works as the primary key for the Franchise Owners table because a person can own only one franchise and each owner has just one record in the Franchise Owners table; if in the future an owner is allowed to own multiple franchises, two modifications will need to be made: (1) remove the FranchiseID field from the FranchiseOwners table and (2) create a ``junction'' table that has two fields, OwnerID and FranchiseID; now an owner could be put multiple times into the junction table to show ownership of multiple franchises (the relationship between Franchises and FranchiseOwners becomes many-to-many)
• input masks are used to control the format of field data; ensures that data entered is valid and consistent, for example, telephone numbers end up as (nnn) nnn-nnnn where n is 0--9
• validation rules also control field data, for example, range limits like no more than 40 hours worked per week
• default values for fields if no data is entered to ensure something is always in the field
• lookup fields (drop-down list for data entry) are convenient and reduce data entry errors; the drop-down list can come from a field in another table or from a list of allowed values entered in design view
• table relationships: one-to-one, one-to-many, many-to-many
  • one-to-one relationship between two tables is rare because you could combine the two tables into one table by placing the tables side by side and stitching them together
  • one-to-many example is each franchise in a franchises table is associated with several employees in an employee table but each employee works at just one franchise; another example is each franchise in a franchises table is associated with one or more owners in an owner table but each owner owns just one franchise; another example is a student table and a high school table where each student graduates from a unique high school but each high school has many graduates
  • many-to-many relationship needs a junction table; many-to-many example is a table of students and a table of courses and a registration table that is the junction table and has studentID-courseID entries or rows
• enforcing referential integrity helps keep a database consistent and valid: page 161
• performance analysis
• object definition document

Access Level 1 Project 5.

• queries: locate records matching your criteria (conditions)
• involve one or more tables in the same database file
• select which fields to be displayed from the matching records
• generate a report to print based on the query
• save the query as an object in the database
• use design view or wizard to create a query and save it
• to use a previously created query, click Queries, select the query object and click design (selecting it and clicking open actually runs the query with whatever criteria are already entered into it)
• then enter or change criteria, then run it
• the results of running a query resemble a table and you can edit the query results (doing that changes the data in the underlying table or tables)
• a select query is similar to a filter but
  • can pick which fields to be displayed in the matching records
  • can involve multiple tables
  • can have AND and OR criteria
  • can have calculated fields: a new column displayed in the results that looks like a field but it is calculated from existing fields in tables
  • can have aggregate functions (sum, average, maximum, minimum)
• crosstab query: calculations by group and subgroup (page 208) that reminds me of an Excel pivot table
• debugging queries used to find potential database errors
  • find unmatched
  • find duplicated

Access Level 1 Project 6: forms

• forms are for adding records, viewing records one at a time, and editing records
• one form can be related to multiple tables by first creating a multi-table query, then a form based on that query (also possible to do it directly)
• form wizard, form design view
• form elements (page 233)
  • form header and footer sections
  • page header and footer sections
  • controls are usually in the detail section
  • controls display data, perform actions, decorate and each control has a label and a text box
  • a bound control is tied to a database table field
  • an unbound control is not tied to or related to any fields
  • a control label is a field name or caption
  • a control text box displays field data
  • a calculated control displays data calculated from one or more fields
• working with controls in design view, control handles, page 237
• form properties, Lesson 5, for example, removing scroll bar and record selector
• tab order, Lesson 6
• form and subform, can be created in design view or from scratch with a wizard; a subform is like a form embedded in a form, for example, a franchise record and its owner records
• this is different from a multi-table form (perhaps based on a multi-table query); for example, a form for a student in a high school and student database could pull in and display for each student the data about the high school the student graduated from; a form with subform example is a form for the high school table that has a subform of all students from the student table who graduated from that high school

• report wizard, report design view
• customize a report by positioning elements; add pictures, title
• report elements resemble form elements: label control, text box control, bound control, unbound control, header section, footer section, detail section
• sorting
• grouping levels
• graphics
• Auto Formats (bold, background color)
• calculated control
• calculate summary information like group totals

• convert database files between newer and older versions of Access
• link an Excel worksheet and a database table together so that editing one affects (changes) the other
• export Access data to Excel file (we imported data from Excel on page 70), embed, editing one does not affect the other, can also export to HTML file
• merge Access data into a Word document, embed Access data into Word document, mail merge operation that takes each record (row) of a table and inserts it into a Word document, creating a new Word document for each row of the table, creates a bunch of form letters
• create a ``data access page'' object, allows browser/Web access to the database, Web page is linked to table/query/form/report, editing one affects the other but editing via the Web requires Internet Explorer browser and (insecure) ActiveX controls

Access Level 2 Project 1: designing a relational database (we did some of this in Access Level 1 Projects 2 and 3).

• relational means having to do with tables and their relationships (1-1, 1-many, many-many)
• a table is composed of rows of data where each row has fields of data
• The PetPals database has three tables
  • products table (product ID primary key, description, etc.)
  • customers table (customer ID primary key, name, address, etc.)
  • orders junction table (order ID primary key, customer ID, product ID, order date, quantity, etc.)
• creating a database
• creating a table with design view
• importing data
  • from another Access database
  • from a CSV (comma separated value) text file
• field properties
  • size
  • default value
  • input mask
  • required
  • validation rules
• autonumber field: Access generates a unique number for the primary key, for example, customer ID in customers table, product ID in products table
• lookup fields
  • use data from one table to enter data in another table
  • reduces data entry time
  • reduces data entry errors
  • gives choice of valid data
• relations among tables
  • customers-orders relation is 1-many
  • products-orders relation is 1-many
  • customers-products relation is many-many (through the orders junction table)
• referential integrity: an order record cannot exist for a non-existent customer (or for a non-existent product)
• object dependencies
• database documentation (Documenter)

Access Level 2 Project 2: complex queries

• query types listed in Table 2.1 on pages 46--47
• select query
  • AND OR criteria
  • can access multiple tables: fields common to two tables must have equal values, for example, customer ID, order ID, product ID (called inner join and is the usual case)
  • an example is a query (Lesson 2) with these fields that display all the customer and product details for each order
    • order ID, customer ID, product ID, quantity from orders table
    • last name, first name from customers table
    • description, price from products table
  • left-outer join and right-outer join also possible (see Table 2.2 on page 48)
  • filter by selection and filter by form can be applied to the results of a query
• parameter query: interactive, prompts for criteria values
• calculated field
• action query: delete record, update record, append record, create table
• crosstab query: summarize groups with counts, averages, sums
  • an example (Lesson 6) is the inventory total value (price times quantity) for each category of pet product (beds, bowls, clothing, food, toys, etc.)

Access Level 2 Project 3: customizing forms (we did some of this in Access Level 1 Project 6).

• a form for data entry of customer orders
  • customers on the main form that displays one customer's record (name, address, etc.) at a time
  • a subform that displays all of that customer's orders
• creating a form with a subform
  • wizard
  • design view
  • AutoForm: if Access detects a 1-many relationship from the main table to another table, AutoForm automatically uses the other table to create a subform
• changing the data source of a subform from the AutoForm-selected table to another query or form (to be able to have the subform draw from more than one table)
• calculated fields
  • order total on each line of the subform: use an unbound control and the expression builder to calculate price * quantity for each subform record
• putting the current date in the form footer
• disabling a field (shows as grayed out) so the value in the field cannot be edited in the form
• putting screen tips on (unlabeled) controls

home page: http://elvis.rowan.edu/~hartley/index.html
e-mail: hartley@elvis.rowan.edu