meets Tuesday and Thursday from 0800-0915
office hours in Manning 112
Class Schedule
10 Jan | intro
15 Jan | clients
17 Jan | servers
22 Jan | networks
24 Jan | basics lab
29 Jan | structural layer
31 Jan | presentational layer
05 Feb | working with layers
07 Feb | behavior layer |
12 Feb | images & design
14 Feb | website lab
19 Feb | document markup
21 Feb | graphics
26 Feb | document markup lab
28 Feb | spreadsheets
05 Mar | formulas & functions
07 Mar | data display
19 Mar | database tools
21 Mar | spreadsheets lab
26 Mar | relational databases
28 Mar |
tables |
05.01 |
next session
02 Apr | relationships
04 Apr | input & output
09 Apr | SQL
11 Apr | complex queries
16 Apr | databases lab
18 Apr | presentation design
23 Apr | presentation delivery
25 Apr | presentation lab
30 Apr | 0800-1100 | final in class presentation
This work
is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
home & schedule | class blog | syllabus | contact | grades
Healthy, well-constructed tables are one of the foundation stones of an effective relational database.
A database should have a separate table for every major component of the database
Data should not be duplicated in multiple tables
Duplicating data is a common error,
but it's easy to avoid if you structure your tables well
Each table contains records and fields in Access which are analogous to rows and columns in Excel.
type | ⇒⇒⇒⇒⇒⇒ | ⇓⇓⇓⇓⇓⇓ |
ERModel entity set | composed of entities | which have attributes |
Access tables | composed of records | which have fields |
Excel worksheets | composed of rows | which have column header cells |
But we can do so much more with field properties in Access than we could do with column headings in Excel.
A record is a collection of facts about a particular person, event, or other item of interest
Each row contains a record which is a single entry in a table and each record is composed of several descriptive fields.
Each column contains a field.
A field
is a single kind of fact that may apply to each person, event, or other record.
A column heading/field name is a label
describing the type of information in the
column/field.
When creating databases, sketch out the different tables you will need and the data contained in the tables before you begin
You can import data from an Excel workbook (for example) by using the External Data tool which allows you to find the needed file type.
This will start the import wizard that permits you to select the worksheets and the columns that you want to import.
The import wizard is very sensitive and often will not import data directly into an existing table because there may be an unseen formatting difference between the data in Excel and how it appears in Access
You will need to set up your Excel table header rows to match your Access table fields
Once you have created a new table, view the new table in design view
Automatically increments; used for primary key (unique identifier)
Numbers are integers that are negative or positive
not numbers that do not have numeric values like SSANs or PIDs
Used for words or non-value numbers; default setting; 255 characters max
Dollar or other currency amounts, with choice of decimal places
Dates & times. Like Excel, Access stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day. Formatting is necessary to have the numbers display as dates and times.
for Lookup tables
To distinguish one record from another, tables contain a primary key field
the primary key is an identifier - such as a part number, a product code, or an employee ID - that is unique to each record
The primary key should be a piece of information that won't change frequently
In Design View, click the "Primary Key" icon in the desired field.
all field types except AutoNumber and OLE Object have the following properties
number of characters, max is 255 (except for Memo)
Note the distinction between Long Integer (storage requirement is four bytes) and Double (storage requirement is eight bytes) for a number field
how the field's contents will be displayed
option appears when data type is numeric
limits formats that can be entered (zip code, phone number, etc.)
Should it be stored in the database in a specified format?
label that will be attached to the field on a form or report. Can be different than the actual field name
default for new entries
a range of acceptable entries; checks values
appears in status bar when field is selected
a yes/no field that indicates if a value is mandatory
a yes/no type setting that indicates whether a text string with no length ("") is valid
creates an index for the field; improves searching & sorting
You can edit in either Design View or Datasheet view, however, the only place to make permanent changes is in Design View.
As in Excel, you can use functions in Access. For example, you can use functions in forms, reports, the design of tables, etc.
Functions have the same format as in Excel
Also, you can use the expression builder to build a function. For example,
generates the current date. [You need to include the parentheses, ().]
generates a date that is 30 days from the current date
tables | 05.01 | next session