Tables Any database must contain one or more tables, because

1
Tables
2

• Any database must contain one or more tables,
  because thats where the data is stored.
• For any table there are two views provided
• Datasheet view
• Design view

3

• Here is an example of a table in Datasheet View.
• Each record is displayed as a separate row, and
  the column headings are the field names.

4
(No Transcript)
5

• You can navigate among the records and fields
  just as you would on an Excel worksheet, using
  the arrow keys, or Tab and ShiftTab, or Enter,
  or clicking in a cell
• You can also move among the records using the
  arrow buttons at the bottom of the window.

6

• You can even change the field names by
  right-clicking on them, and other Excel-like
  operations are possible as well.

7
Filtering and sorting

• Among the simple things you can do with data from
  a table displayed in Datasheet View is to apply a
  filter to display only those records satisfying
  certain criteria, or to sort the records
  according to the values of certain fields.

8

• Much more sophisticated versions of these are
  available through the use of queries, which we
  discuss later.
• To apply simple filters or sorting operations to
  a table, use the appropriate buttons on the
  database toolbar or select the appropriate items
  from the Records menu.

9
Designing a Table

• To design a table and specify many important
  additional details, you need to use the Design
  View.
• Here is our earlier table, shown in Design View

10
(No Transcript)
11

• In Design View, you can see (and specify) what
  the field names are for the table and what their
  datatypes are.
• In addition, there are many other properties that
  can be associated with each field, including its
  maximum size, whether it is required, etc.

12
Write a short description of what kinds of stuff,
i.e. the domain of values, that can go into this
field.
Select its data type from the drop down list.
Type in the name of the field.
Specify the length of the field and any other
properties of that field.
13
What about the Table Wizard?

• If your table closely matches one of the standard
  ones available in Access, you might consider
  using the Table Wizard.
• You can always start with one of these
  pre-designed tables and modify it using Design
  View.

14
(No Transcript)
15
Field names

• In Access field names can be up to 64 characters
  long, including spaces.
• Make your field names non-cryptic so your users
  dont have to guess what the name and the data
  mean.

16

• The next thing you have to specify about that
  field is the datatype that it has, that is tell
  the system what type of data can go into that
  field.

17
Data types that Access recognizes

• Text
• Memo
• Number
• Date/Time
• Currency

• AutoNumber
• Yes/No
• OLE Object
• Hyperlink
• Lookup Wizard...

18

• What exactly are these?

19
Text data type

• Use a Text data type to store data such as names,
  addresses, and any numbers that do not require
  calculations, such as phone numbers, part
  numbers, or postal codes.
• A Text field can store up to 255 characters, but
  the default field size is 50 characters.

20
Memo data type

• Use the Memo data type if you need to store more
  than 255 characters.
• A Memo field can store up to 65,535 characters.
• Memo fields can't be indexed or sorted.
• If you want to store formatted text or long
  documents, you should create an OLE field instead
  of a Memo field.

21
Number data type

• Use a Number field to store numeric data to be
  used for mathematical calculations, except
  calculations that involve money or that require a
  high degree of accuracy.

22

• The kind and size of numeric values that can be
  stored in a Number field is controlled by setting
  the FieldSize property. For example, the Byte
  field size will only store whole numbers (no
  decimal values) from 0 to 255 and occupies 1 byte
  of disk space.

23
Currency data type

• Use a Currency field to prevent rounding off
  during calculations.
• A Currency field is accurate to 15 digits to the
  left of the decimal point and 4 digits to the
  right.
• A Currency field occupies 8 bytes of disk space.

24
AutoNumber data type

• The AutoNumber data type is used to create fields
  that automatically enter a unique number when a
  record is added.
• They are often used as primary keys.
• Once a number is generated for a record, the
  number can't be deleted or changed.

25

• An AutoNumber field can generate three kinds of
  numbers, but the most important kind is the kind
  that increments by one each time.
• This is a good choice for use as a table's
  primary key if there is no other field with
  unique values.
• It can be formatted to display as C0001, for
  example.

26
Date/Time data type

• Storing dates and times in a Date/Time field
  ensures that dates and times will be sorted
  properly.
• Also, changes made to the date or time formats
  that are specified by double-clicking Regional
  Settings in Windows Control Panel will be
  automatically reflected in Date/Time fields.

27
Pre-formatted date/time settings

• General Date (Default)
• If the value is a date only, no time is
  displayed.
• If the value is a time only, no date is
  displayed. This setting is a combination of the
  Short Date and Long Time settings.
• Examples 4/3/93, 053400 PM, and 4/3/93
  053400 PM.

28

• Long Date Same as the Long Date setting in the
  Regional Settings Properties dialog box in
  Windows Control Panel.Example Saturday, April 3,
  1993.
• Medium Date Example 3-Apr-93.

29

• Short Date
• Same as the Short Date setting in the Regional
  Settings Properties dialog box in Windows Control
  Panel.Example 4/3/93.

30

• Access 2000 can record dates for the years 100 to
  9999.

31
Time

• Long Time--Same as the setting on the Time tab in
  the Regional Settings Properties dialog box in
  Windows Control Panel.Example 53423 PM.
• Medium Time--Example 534 PM.
• Short Time (24 hour clock)--Example 1734.

32

• Dates and times can be custom formatted as well.

33
Yes/No datatype

• The Format property provides the Yes/No,
  True/False, and On/Off predefined formats.
• Yes, True, and On are equivalent, as are No,
  False, and Off.
• This datatype only takes up one bit of storage.

34
OLE Object data type

• OLE Object fields are used to store data such as
  Microsoft Word or Microsoft Excel documents,
  pictures, sound, and other types of binary data
  created in other programs.
• OLE objects can be linked to or embedded in a
  field in a Microsoft Access table.

35

• When you embed an object, Access stores the
  object in your database file.
• If you modify the object from your form or
  report, the object is changed in your database
  file. For this reason, an embedded object is
  always available.

36

• When you link to an object, you can look at the
  object and make changes to it from your form or
  report, but the changes are stored in the
  original object file, not in your database file.

37
Hyperlink data type

• You can store hyperlinks in fields in tables,
  just as you store phone numbers and fax numbers.
• For example, our Client table could contain a
  field that would store hyperlinks to home pages
  for some of our clients.

38
Lookup Wizard

• A lookup wizard creates a field that lets you
  choose a value from some other table or from a
  list of values by using a list box or combo box.
• Clicking this option starts the Lookup Wizard,
  which creates a Lookup field.
• After you complete the wizard, Microsoft Access
  sets the data type based on the values you select
  in the wizard.

39
Lookup Wizard data type

• Using the Lookup Wizard, you can create a field
  that displays either of two kinds of lists to
  make data entry simpler
• A Lookup list that displays values looked up from
  an existing table or query
• A value list that displays a fixed set of values
  that you enter when you create the field

40
Lookup list

• The most common Lookup list displays values
  looked up from a related table

41
Lookup List
42
Value list

• A value list looks the same as a Lookup list, but
  consists of a fixed set of values you type in
  when you create it.
• A value list should only be used for values that
  will not change very often and don't need to be
  stored in a table.
• For example, a list for a Salutation field
  containing Mr., Mrs., or Ms. would be a good
  candidate for a value list.

43

• Choosing a value from a value list will store
  that value in the record. It doesn't create an
  association to a related table.
• For this reason, if you change any of the
  original values in the value list later, they
  will not be reflected in records added before
  this change was made.

44
Field Properties

• Fields can have certain properties.
• The most commonly used ones are
• Format
• Caption
• Default Value
• Indexed
• Primary
• Validation Rule
• Validation Text

45
Default Value Property

• You can use the DefaultValue property to specify
  a value that is automatically entered in a field
  when a new record is created. For example, in a
  ZIP table consisting of ZIP, City, and State, you
  can set the default value for the State field to,
  e.g. MA, if most of your clients are from that
  state.
• When users add a record to the table, they can
  either accept this value or enter the name of a
  different city.

46

• The DefaultValue property is applied only when
  you add a new record.
• If you change the DefaultValue property, the
  change isn't automatically applied to existing
  records.

47
Indexed Property
48

• You can use the Indexed property to set an index.
  
• An index speeds up queries on the indexed fields
  as well as for sorting and grouping operations.
• For example, if you often search for specific
  employee names in a LastName field, you should
  probably create an index for this field to speed
  up the search for specific names.

49

• The field (or combination of fields) can hold
  either unique or non-unique values. For example,
  you can create an index on an EmployeeID field in
  an Employees table in which each employee ID is
  unique (and which you might specify as the
  primary key) or you can create an index on a Name
  field in which some names may be duplicates.

50

• You can create as many indexes as you need, but
  do they do take up space and require extra
  computing time to update as the database is
  modified.
• Also, dont index a field which can take only a
  few values, like Yes or No.

51

• The indexes are created when you save the table
  and are automatically updated when you change or
  add records.
• You can add or delete indexes at any time in
  table Design view.

52
Settings for Indexed property

• Setting
• No
• Yes (Duplicates OK)
• Yes (No Duplicates)

• Description
• No index
• The index allows duplicates
• The index doesnt allow duplicates

53

• You normally set this property in the Field
  Properties section for that field in table Design
  view.

54

• You can set a single-field index by setting the
  Indexed property in the Field Properties section
  in table Design view.
• You can set multiple-field indexes in the Indexes
  window by clicking Indexes on the View menu.

55

• If you add a single-field index in the Indexes
  window, Microsoft Access will set the Indexed
  property for the field to Yes.

56

• If the primary key (see below) for a table is a
  single field, Microsoft Access will automatically
  set the Indexed property for that field to Yes
  (No Duplicates).

57
Primary field property

• You can use the Primary property to specify the
  primary key field for a table.
• A primary key field holds data that uniquely
  identifies each record in a table.

58

• You can set the Primary property in two ways
• In table Design view, select the field or fields
  in the order you want for the primary key and
  then click the Primary Key icon on the toolbar.
• In the Indexes window, select or enter the name
  of an index in the Index Name column and set the
  Primary property to Yes in the Index Properties
  section.

59

• Microsoft Access automatically creates an index
  on the primary key field of a table and uses it
  to find records and to create joins between
  tables.
• The primary key index requires an entry in each
  primary key field and allows no duplicates.
• The order of the fields in a multiple-field
  primary key determines the default sort order for
  the table.

60

• If there is no primary key when you save a
  table's design, Microsoft Access will display a
  dialog box asking whether you want a primary key
  to be created.
• If you click Yes, an AutoNumber data type field
  will be added to the table (with its NewValues
  property set to Increment) and set as the primary
  key. If you click No, no primary key will be
  created.

61

• Note that a table with no primary key can't be
  used in a relationship and can be slower to sort
  and search.

62
Data Integrity(correctness)--Validation Rules

• You can use the ValidationRule property to
  specify requirements for data entered into a
  record, field, or control.
• When data is entered that violates the
  ValidationRule setting, you can use the
  ValidationText property to specify a message that
  gets displayed to the user pointing out the error.

63
Setting the Validation Rule and Validation Text

• Enter an expression for the ValidationRule
  property setting and enter text for the
  ValidationText property setting.
• The maximum length for the ValidationRule
  property setting is 2048 characters. The maximum
  length for the ValidationText (error message)
  property setting is 255 characters.

64

• You can set the ValidationRule and ValidationText
  properties by using
• The Field Properties section of table Design view
  (for a field validation rule).
• The property sheet for a table by clicking
  Properties on the View menu in table Design view
  (for a record validation rule).

65

• Microsoft Access automatically validates values
  based on a field's datatype for example,
  Microsoft Access doesn't allow text in a numeric
  field.
• You can set rules that are more specific by using
  the ValidationRule property.

66

• If you set the ValidationRule property but not
  the ValidationText property, Microsoft Access
  displays a standard error message when the
  validation rule is violated.
• If you set the ValidationText property, the text
  you enter is displayed as the error message.

67

• For example, when a record is added for a new
  employee, you can enter a ValidationRule property
  requiring that the value in the employee's
  StartDate field fall between the company's
  founding date and the current date.
• If the date entered isn't in this range, you can
  have the system display the ValidationText
  property message "Start date is incorrect."

68

• Field and record validation rules are applied as
  follows
• Validation rules you set for fields are applied
  when you edit the data and the focus leaves the
  field.
• Validation rules for records are applied when you
  move to another record.

69

• ValidationRule property
• ltgt 0
• gt 1000 Or Is Null
• Like "A????"

• ValidationText property
• Your entry must be a nonzero value.
• Entry must be blank or greater than 1000.
• The value must be 5 characters and begin with
  the letter "A".

70

• gt 1/1/2000 And lt1/1/2001
• Between 1/1/2000 And 12/31/2000

• Your entry must be a date in the year 2000.
• Your entry must be a date in the year 2000.

71

• If you create a validation rule for a field,
  Microsoft Access doesn't normally allow a Null
  value to be stored in the field.
• If you want to allow a Null value, add "Is Null"
  to the validation rule, as in "ltgt 8 Or Is Null"
  and make sure the Required property is set to No.

72
Specifying relationships among tables

• If youre creating a database involving multiple
  tables, in addition to designing the individual
  tables, you must also specify how the tables are
  related.
• In particular, you need to identify the foreign
  keys and which tables these keys point to.

73

• In many cases, you may choose to use the
  identical name for a foreign key and its
  corresponding primary key, but this is not
  necessary,
• nor does Access assume that identical field names
  in different tables are intended to refer to the
  same thing.

74

• To specify these table relationships, use the
  Relationships Window, accessible by clicking the
  appropriate button on the toolbar or by doing
  Tools gt Relationships

75

• This displays all the tables in the database and
  the fields they contain.
• To specify that a field in one table corresponds
  to a field in another table, drag the field name
  from one table and drop it onto the corresponding
  field name in the other table.
• The result will look like this

76
(No Transcript)