MS Access (Level I)

Learning Center Course on Access 2003 Level I , taught by Sam Eskridge (help desk 5-3200).

First create folder in mydocuments, then copy 7 folders from Z:\Access\Access 2003\Level 1 to that folder. Open concepts.mdb from unit 1 folder in Access. [Problem with sound bleeding through from Pathways next door. ] Four objects to be covered in Level I: Tables, Queries, Forms, Reports. Pages and Macros come up in Levels II and III.

Modules are VB script, beyond scope of Access courses at Learning Center.

We're going to work with "Outlander Spices" retail database model. Primary key of table is product ID. These numbers are not recycled.

Database Window shows all objects in DB.

Menu-->View-->Task pane. New feature in Access 2003. Conext sensitive. Includes list of recently opened files.

Much of the first hour is exceedingly basic. More specific to Windows Office than to Access. How to use Menus, Office Assistant, etc.

Unit 2: Consider drafting process map or flow-chart before building database.

"Project" allows Access to be used as front end to MySQL or other backend databases. Try "From existing file" to modify pre-existing DB. Try "Templates" on "My computer" and Databases tab. --> "Order Entry". Rename "Order Entry1.mdb" to "Outlander_spices", save in Unit 02 folder by clicking "Create". Follow steps in "Database Wizard." Select ... Templates are fully developed databases, so may get you 80% of the way to where you want to be. Good option to consider.

My Company information form pops up during installation, see p. 2-6 in booklet.

Forms Switchboard ...

p. 2-7: Creating DB from scratch ... "CreateDatabase.mdb" . Access2000 file format allows the many non-upgraded users to work with dtabase. This is default setting.

Shift+Enter = Save, without moving forward, i.e., to avoid creating new record.

Design view (versus datasheet view):
includes columns on Data type (e.g., text, currency), field name, description, with data properties (including captions) displayed at bottom.

[10-minute break]

Create new DB

Use wizard--> click New in Design view. --> select "Table Wizard" to create populated DB. Business--> Sample Tables--> "Employees and Tasks". Single arrow pulls selection into "My new table". Double arrow pulls all fields over. Note importance of unique field names (system appends numeral 1 to end of duplicated field name).

Rename table: "tblEmployeeTasks". [This sets us down wrong path] "No I'll set primary key"--> EmployeeID-->Enter data directly into table. [By making Employee ID primary key, only able to assign one task to any given employee. Better: "Employee Task ID" representing the combination, allows one-to-one, one-to-many,many-to-one, etc. options. Switch to design view.

MS can generate autonumbers up to 1.4 billion.

Keep in mind that telephone and SS numbers, etc., are given datatype "text" not "number" since these are non-calculable numbers. Don't want dashes to convert to mathematical notation.

Field naming conventions indicate, but don't determine datatype. Not necessary to use, but can be helpful. Use captions in field properties in order to diplay more user-friendly names.

Memo-notes can store 30 pages of size-12 text.

Note smart tags, context-sensitive drop-down menus. Good for making global changes.

Second session: Nov. 9, 2006

Picking up at session 3, pg. 7, opening database page, tbEmployee table, looking at Dept column (field) with Dept. code (which will link to a separate Dept. table). Change AT code to AC. Several techniques to do this globally ... Click find button (binoculars icon) to open "Find and Replace" dialog box. "Look in field" will automatically fill-in where-ever insertion point was resting.--> Find AT--> Replace with AC--> Replace all. [Could be handy in case we change instances of department name in db-driven Web site]

Open tblDept, in this case, where codes and department names are arranged side-by-side, so they only have to be recorded once in db. For spell-checker, select entire field (becomes highlighted), and click button. But remember: "Dew knot trussed yore document to spell checque".

Shift + Enter. THen close table.

Open tblEmployee (includes names, HR#, earnings, etc.. ) "Horizontal Inegrity" principle means that re-sorting one field causes resorting of every field. This is unlike Microsoft Excel (so says Sam), which sometimes sorts single field only. But sorting thorugh database menu will impose horizontal integrity.

What happens when you select two columns and then sort? Both fields get sorted, with secondary one to right "mutual sort". If fields not adjacent, then possible to do same thing by constructing a query.

Filter indicated by funnel button. Select cell, then "filter by selection", to get extract from table.

Fildter by form. Selct any record ... Table replaced by search form ... note drop down arrow to get combo box, with list of all unique values in this field. Select one of these, then silver funnel button.

May also write criterion in form oneself, e.g., "Earnings" field: ">50000".

Deleting record button

Unit 4: Data Entry Rules

Note: "Record Navigator" is tool bar at bottom.
Setting field properties, changing these, e.g., "Required" will cause Access to test integrity of data in each record. But what happens whn property is required, but agent simply doesn't have data to put here (e.g., no fax number)? Then , "Allow zero length" property set to "yes." this is equivalent to N/A, just hit space bar.

"Field size property" and "Bounce tone".

4-10: Input-mask characters. e.g., enter 2034325660: Displays as (203) 432-5660. Punctuation is called "literal characters" and digits are called "values". "Input-mask characters" are given in table on this page. Set input mask for phone field: "(999) 000-0000;0;#". Input-mask field also provides "builder" button, which in this case activates "Input-mask wizard". Then tab, then "try it".

"Default values" property, e.g., "Portland" for "city" saves keystrokes for frequently used terms.

"Validation Rule" property: enter statement in SQL: "Like "* ox" Or Like "* lb" (for bulk chives record). Then add "Validation Text": Unit values must be oz or lb.

Unit 5: Create Queries

Query produces "Record set". Selcet "Make-Table Query" as query type in order to create entirely new (self-standing) table.

[BREAK]

Query -- double click fields from table, click red exclamation point to "run" . Extremely simple. SQL running in background of all db's, but Access can write it for us.

Unit 6: Using Forms

Section headers. Add form footers. Pull up or down like curtain. Click in Header, select Aa in toolbar. Place cross-haris where desired, and left mouse button drag to create desired area for label.

Easiest way to create a form: Tables page, select table, select New Object button arrow to right, click down arrow, and select "Auto form". Access will pull all fields from table and put into form.

Unit 7: Reports

Written off of tables or queries. Reports are updated dynamically when tables are changed. "Reports designed once can write themselves."