Database Management Systems

1
IT 244
Database Management Systems
Lecture 2 Microsoft Access Practical 1-
4 Practical 1 Creating Tables
2
Microsoft Access

• For the first part of our practical database
  management, we will focus on Microsoft Access XP.
• We will cover how to create databases, create
  tables, create forms, queries, reports,
  PivotCharts, Access Webpages and Macros
• By the end, you should be familiar with Access to
  create and manage your own database (which you
  will do for your project)

3
Create Database and Tables

• To start, you want to go to File-gtNew and a menu
  on the side will appear
• This will let you choose Blank Database, Blank
  Access Page and Project
• The Project is for when you will be connecting to
  a Microsoft SQL server over a network. The Access
  Page also requires a networked SQL connection
• To just make a local database, we will choose
  Blank Database. You can enter a name for your
  database after that and choose where it will be
  saved

4
Creating Tables

• The Database menu will come up and let you choose
  what action you want to perform
• You can select Tables and this will let you
  create tables, edit or delete them
• If you have a standard database that you are
  creating (like a database of Customers), choosing
  Create table by using wizard will be the
  easiest to make a new database
• A screen will appear that lets you choose what
  fields you want to have on your database

5
Create tables by wizard

• The wizard lets you select from a wide variety of
  different categories that might be applied to
  different circumstances
• Select the category, either personal or business,
  then select the field you want to add to your new
  tables. You can select all the fields in a
  category by clicking on the gtgt button
• After you select your fields, press next, and you
  can choose a primary key if you want and give the
  table a new name
• Then you can choose if there will be
  relationships between tables.
• Finally, you will be asked to enter information
  into the database right away

6
Tables and words

• So in Access what we call Tables are also known
  as Entities
• Fields in Access are also known as Attributes
• Relationships are still called relationships
• The primary key is a field that is chosen to be a
  special field and works the same way as in theory

7
Primary Keys in Access

• The power of a relational database system such as
  Microsoft Access comes from its ability to
  quickly find and bring together information
  stored in separate tables using queries, forms,
  and reports.
• In order to do this, each table should include a
  field or set of fields that uniquely identifies
  each record stored in the table.
• This information is called the primary key of the
  table. Once you designate a primary key for a
  table, Access will prevent any duplicate or Null
  values from being entered in the primary key
  fields.
• There are three kinds of primary keys that can be
  defined in Microsoft Access
• AutoNumber, Single Key and Multiple Key

8
Autonumber

• An AutoNumber field can be set to automatically
  enter a sequential number as each record is added
  to the table.
• Designating such a field as the primary key for a
  table is the simplest way to create a primary
  key.
• If you don't set a primary key before saving a
  newly created table, Microsoft Access will ask if
  you want it to create a primary key for you.
• If you answer Yes, Microsoft Access will create
  an AutoNumber primary key.

9
Single valued primary key

• If you have a field that contains unique values
  such as ID numbers or part numbers, you can
  designate that field as the primary key.
• You can specify a primary key for a field that
  already contains data as long as that field does
  not contain duplicate values or Null values.

10
Multiple value primary keys

• In situations where you can't guarantee the
  uniqueness of any single field, you may be able
  to designate two or more fields as the primary
  key.
• The most common situation where this arises is in
  the table used to relate two other tables in a
  many-to-many relationship.
• For example, an Order Details table can relate
  the Orders and Products tables. Its primary key
  consists of two fields OrderID and ProductID.
  The Order Details table can list many products
  and many orders, but each product can only be
  listed once per order, so combining the OrderID
  and ProductID fields produces an appropriate
  primary key.
• Each product can be listed only once per order.

11
Create table in Design view
12
Create table in design view

• Click on field name, enter in the field
  (attribute) that you need to put in
• Choose a field type This will say what kind of
  data is stored inside the field.
• You can choose from text, number, date/time,
  currency, autonumber and so on
• In most cases, text will suffice
• Then on the bottom you can change more attributes
  about the field, like..

13
Attributes about Fields

• Field size put the maximum number of characters
  per entry
• Format Changes how the data will be displayed.
  You can enter in custom formats, like changing
  the color of something, put in Blue, Green
• Input Mask Changes the way you can enter data.
  For example, you can make a phone number look a
  certain way before being entered
• Caption Provides a label for reports and forms.
  If this is blank, then it will just say the field
  name
• Default value A value that will be entered
  automatically in a record, unless it is changed
  to be something else

14
Attributes for fields

• Validation Rule Make the user enter data in a
  certain way.
• Examples
• ltgt0 ----- Entry must be not zero
• gt1000 or Is Null ----- Entry must be blank or
  greater than a 1000
• Like A???? ----- Entry must be 5 characters and
  begin with A
• gt 1/1/04 And lt 1/1/05 ----- Entry must be a
  date in the year 2004
• Validation text the message that appears when
  someone tries to enter a bad format

15
Attributes for fields

• Required Does the user need to enter this data?
• Allow Zero-length can the entry be NULL or
  nothing in it?
• Indexed If you do a lot of searching and sorting
  on the database, you can enter an index, which
  will store the data specially. The only problem
  is that it might make updating stuff slower
• The Lookup Tab If you click on lookup you can
  change what kind of thing will be displayed on
  forms for this field. Textbox, List Box, Combo
  Box

16
Lookup fields

• If you choose your field to be a combo or list
  box, then you also have to specify some other
  things
• Row Source type this will determine what is
  shown in the box of things to choose on the form
• Row Source The table where the data will come
  from that will go inside the box

17
Enter data into tables

• After you have made your table, you can close it
  and save, or you can go to view and choose
  Datasheet view. This will take you to a window
  where you can enter data
• Vice versa, to go back to the designing a table
  window, go to view and choose Design view
• In the datasheet view, you merely need to enter
  the data you want. To go from one field to the
  next, press tab.
• To go to a new entry, press return

18
Finding data in tables

• Open the table, and then activate the field for
  which you plan to enter a value.
• On the Standard toolbar, click the Find button .
• In Find and Replace dialog box, in the Find What
  box, type what you are looking for.
• In the Look in box, a field is automatically
  entered. In the Match box, Whole Field is
  automatically entered. Click Find Next.

19
Sorting data

• Access 2002 displays information in a table in
  alphabetical or numerical order based on the
  primary key.
• However, you can sort information so that it
  appears in an order that works best for you.
• In Datasheet view, you can sort all of a tables
  records in ascending or descending order, but you
  cannot use both sort orders on more than one
  field.
• To sort records in Datasheet view
• With the table open in Datasheet view, click the
  header of the field to sort so that the whole
  column is selected.
• Note   If you select multiple columns, Access
  sorts records starting with the leftmost selected
  column.
• Or on the Datasheet toolbar, click the Sort
  Ascending button or the Sort Descending button .

20
Filtering Data

• A filter is a set of criteria applied to data in
  order to display a subset of the data or sort the
  data. In general, you use a filter to temporarily
  view or edit records that contain a specific
  item. The rest of the records are then hidden
  from view. Handy when records are so many.
• In Access 2002, you can filter records in three
  ways
• Filter By Form.   Use this option when you want
  to choose the values youre searching for from a
  list without scrolling through all the records,
  or when you want to specify multiple criteria at
  once.
• Filter By Selection.   Use this option when you
  can easily find and select the value you want the
  filtered records to contain.
• Advanced Filter/Sort.   Use this option to search
  for records that meet multiple criteria, search
  for records that meet one criterion or another
  criterion, or enter expressions as criteria.

21
Filter by form

• With the table open in Datasheet view, on the
  toolbar, click the Filter By Form button to
  switch to the Filter by Form window.
• Click the field in which you want to specify the
  criterion that records must meet to be included
  in the filtered set of records, and then in the
  pull-down list that appears, click the criterion.
• On the toolbar, click the Apply Filter button .
• When you want to view the full table again, on
  the toolbar, click the Remove Filter button.

22
Filter by selection

• With the table open in Datasheet view, click the
  item that you want to use to filter data.
• On the toolbar, click the Filter By Selection
  button .
• When you want to view the full table again, on
  the toolbar, click the Remove Filter button.

23
Advanced Filter

• Advanced filter lets you specify exactly what you
  want to filter out based on specific criteria
• When you open Advanced filter you will see the
  list of tables and fields. For each field that
  you want to filter by, you can drag it to the
  bottom, where the filters are created
• Then on the bottom, you can tell how you want the
  sort order to appear and the criteria
• The criteria field uses expression builder to
  determine what it will do

24
Expression Builder

• Expression builder is a tool that Microsoft has
  made that will let advanced users do whatever
  they really need to do, based on simple
  programming ideas
• If you right click on criteria, you can go to
  Build and be taken to the Expression Builder.
• You will see a confusing screen. We will focus on
  simple things the criteria builder can do.
• First select something you want to filter on, by
  opening up a folder for a table and selecting a
  field. A bunch of text will appear in the window
  where the expression is built
• Now you can tell this field to behave in some
  way, such as make sure that this equals Hello
  or make sure it is not null. The range of things
  you can do here is too much to cover in entirety

25
Expression Builder

• So to make something filter by being equal to
  something else you can put in an equal sign and
  then either type in the value you want it to
  equal, or select another field you want it to
  equal to.
• You can also add in Or and And and Not statements
  to chain together different filters and match
  many things at once.
• Then press OK and apply filter. If nothing
  comes up, then your filter is not correct and it
  couldnt match anything

26
Creating relationships

• You can also use Access to outline your
  relationships between tables.
• Click on the Tools-gtRelationships menu item.
  The relationships editor will appear
• You can add in tables here from your database
  with a right click and Show table
• The table you chose will appear and you can now
  create a relationship between two or more tables
  by highlighting a field and dragging it to
  another field in another table.
• A window will appear asking about the
  relationship that you have just created

27
Creating relationships

• The window that appears will show you the fields
  that will be connected through a relationship.
• Referential integrity is a system of rules that
  Microsoft Access uses to ensure that
  relationships between records in related tables
  are valid, and that you don't accidentally delete
  or change related data.
• If you also click on cascading, Access will
  delete and update data in related tables when
  changes are made in another table.
• Press Create and you will see a line
  (relationship) has been created between the two
  entities.
• This is an important step before you can create
  Queries and form and reports that draw from
  multiple tables.

28
Summary

• The first section on Microsoft Access practical
  is trying to ensure that you can create tables
  and enter data efficiently.
• You should also be able to sort, and filter data
  using different methods
• You should at least understand how to make
  relationships in the relationship builder. If for
  nothing else, this is good way to have the
  computer draw the ER diagrams for you

29

• Microsoft Access
• Practical 2
• Making Forms and Reports

30
About forms

• A form is a type of a database object that is
  primarily used to enter or display data in a
  database.
• You can also use a form as a switchboard that
  opens other forms and reports in the database, or
  as a custom dialog box that accepts user input
  and carries out an action based on the input. 
• Most forms are bound to one or more tables and
  queries in the database. A form's record source
  refers to the fields in the underlying tables and
  queries. A form need not contain all the fields
  from each of the tables or queries that it is
  based on.
• A bound form stores or retrieves data from its
  underlying record source. Other information on
  the form, such as the title, date, and page
  number, is stored in the form's design.

31
Creating Forms

• The easiest way to build a form is by using the
  Form Wizard. The Form Wizard helps you choose the
  layout of records in the form and also the
  background, color, and format of the display. You
  can also preview the layout and style options
  when you create a form by using the Form Wizard.
• A form can be based on a table or a query. After
  you create a basic form in the Wizard, you can
  customize it in Design view.

32
Creating forms

• In the your database window, under Objects, click
  Forms, and then double-click Create Form by using
  wizard.
• In the Tables/Queries list box, click the table
  or query from which you want to create the form.
• Select the fields that you want your form to
  include. To base the form on the fields in the
  table, select all the fields by clicking the
  double forward arrow . Then Click Next. Then
  select the style you want

33
Creating Forms

• Choosing a layout
• Access will now let you choose the layout of your
  form
• Columnar Gives you each entry in the table by
  rows
• Tabular Gives you entries by rows
• Datasheet Looks just like the way you normally
  enter data
• Justified Displays the data in a way that it
  looks put together in a unified format
• PivotView and PivotChart two methods we will
  talk about later

34
Customizing Forms

• After your form is created, you can edit it
  further by selecting the form and going to
  Design view
• You will now be able to change the form in ways
  that are very similar to Visual Basic
• You are able to extend the header and footer so
  that you can make each page of the form look
  similar.

35
Creating forms in Design View

• If you want to control your form yourself,
  without using a wizard, you can use the design
  view.
• After you open up the design view, youll see a
  black sheet. You can edit the color and add
  headers and footers by right clicking on the
  screen and choosing those.
• But before your form will work, you need to bind
  it to a record source (meaning that the form
  needs to have some connection with some sort of
  data)
• You need to click in a small square at the top
  left hand corner.

36
Changing Form properties

• A small screen will appear
• and you can change the
• record source for this form.
• You can also add in a filter to
• let you choose which forms
• you want to see
• You can also select a special
• ordering based on one of the
• fields.
• Other things to choose,
• default view lets you see each
• entry on a separate page, or just a continuous
  form.
• You can also decide if you want people to edit
  the data

37
Changing form properties

• After you select a record source in the property
  box, a smaller window will appear with the fields
  from that table.
• If you select fields here, it will automatically
  insert the data into your form, which you can
  edit from that point.
• Now that you have your fields in the form, you
  are able to position them around the field as you
  desire, just by selecting and moving.
• You will also see familiar buttons on the side
  from visual basic. They will let you enter in
  more fields, in different formats.
• Try putting in new fields, list boxes, and combo
  boxes that are connected to different data
  sources

38
Header and Footer

• Header and Footer parts let you customize your
  form so that it always looks the same way
• A form is a good way for a regular user to use a
  database and enter data into it.
• You might want to add a company logo to the top
  and your name to the bottom.
• Try to put pictures in the header and the footer
  by going to Insert picture. For your final
  project you should have nice headers and footers
  on your forms

39
Reports

• Reports let us create an entire outline of all
  the information in the database, sorted and
  grouped by whatever means we want.
• It also has special feature to provide summing
  and averaging for numbers and gives you good
  control over the look and feel
• The easiest way to create a new report is to use
  the wizard

40
Report Wizard

• It will first ask you to select the fields you
  want in your report, much like other wizards.
• Then it will ask for grouping levels. If you
  double-click on a field, it will sort the records
  by that field. You can have as many levels as you
  need.
• Thus if you want to sort a database of customers
  by city, it will group them all by city first.
  Then maybe within the city, you want to group by
  lastname. So double-click on city, and lastname
  and youll have a report ordered by those two
  fields

41
Report Wizard

• You can add how you want to sort the records as
  well, on at least four different levels
• You just need to select a a field and choose
  whether it is ascending or descending
• If you click on summary options, it will open a
  window that lets you add more information for
  numbered fields.
• For example, if you have age of everyone stored
  in a database, you can find the average of all
  the ages, or the minimum or the maximum.
• Then you need to choose how to layout your data.
  If you click on the different formats, you can
  get a small preview of what they look like
• The last step is to choose the look and feel of
  the report, and you can select whatever looks the
  nicest

42
Reports in Design View

• Again, we can also create reports using the
  design view. It will look very similar to the
  design view of the forms.
• Youll need to select a record source for your
  reports by clicking in the little box at the
  upper left hand corner.
• Youll also need to add in the fields you want
  and how you want them displayed.
• To see how it looks, go to View-gtPrint Preview,
  since reports are made specifically for printing
  purposes.
• If you want to add more grouping, you can go to
  Sorting and Grouping, in Views. This will let you
  add and remove different sorting and grouping
  categories.

43
Microsoft Access Practical 3 Queries
44
Queries

• You use queries to view, change, and analyze data
  in different ways.
• You can also use them as a source of records for
  forms, reports, and data access pages. There are
  several types of queries in Microsoft Access.
• Select Queries
• Parameter Queries
• Crosstab Queries
• Action Queries
• SQL Queries

45
Select Queries

• The simplest type of query is a select query that
  will let you select certain data based on
  criteria you provide
• This is the query that will come up if you choose
  Create query by wizard
• It retrieves data from one or more tables and
  displays the results in a datasheet where you can
  update the records sometimes
• You can also use a select query to group records
  and calculate sums, counts, averages, and other
  types of totals.

46
Select Queries

• If you create a query by wizard, you will see the
  familiar screen letting you choose what you want
  to include in your query
• The next screen will ask for Detail or
  Summary. Detail will put all the information in
  your results. Summary will let you summarize
  different field types.
• For example, you can take the average of all the
  ages instead of displaying just the age of a
  person.
• Then when you are done with the wizard, you will
  see the datasheet with the data you selected

47
Select Queries Design View

• So to get the good functionality out of Access we
  need to look at the Design view of the query.
• The design view will show tables at the top half
  (you can add more by right clicking and going to
  Show Table)
• Then you can drag fields you want to appear to
  the bottom.
• The bottom contains all the fields in your query
  and whether you want to show them. You can also
  sort based on certain ordering here

48
Select Query Criteria

• There will also be many times when you want to
  select items based on different criteria
  (Example, select all people with name of sione)
• If this is the case, you can add in stuff in the
  criteria field on the bottom of the design view
• For example, if you have a field called names and
  you just want to see Sione, inside the Criteria
  field, you will put
• Sione
• This works for other cases, like if you have a
  field called age and you want all the people over
  50, put
• gt 50

49
Action Queries

• An action query is a query that makes changes to
  or moves many records in just one operation.
  There are four types of action queries 
• Delete a query to delete a number of records
• Update a query to update records
• Make-Table create a new table from parts of
  other tables
• Append add records from one table to the end of
  another table.

50
Action Queries

• To make an action query, go to the design view
  and select one of the actions (delete, update,
  append, make-table) from the Query menu
• If you choose Update for example, the parts
  below will be changed to have an Update to
  field appear. This is where you can put in a new
  value that the records will be updated to
• If you choose Delete a Delete field will
  appear. It will say Where and this lets you add
  criteria.
• So to delete all people over fifty, you would add
  in the criteria field
• gt 50

51
Action Queries

• Append By choosing an append query you can add
  records from one table to the end of another.
  When you choose it, a new window will appear
  asking for where the data will be appended to
• Then you can construct a query, and the results
  of that query will be put into the table you have
  chosen.
• This can be helpful if you have another database
  and you need to put the data in, but dont want
  to type everything
• Finally, when you are done making your query in
  design view, make sure to hit the ! button to
  make it run.

52
Crosstab Queries

• You use crosstab queries to calculate and
  restructure data for easier analysis of your
  data.
• Crosstab queries calculate a sum, average, count,
  or other type of total for data that is grouped
  by two types of information  one down the left
  side of the datasheet and another across the top.
• They are just a different way to display data
  that limits the presentation to three categories
  (the row header, the column header and the
  values)

53
Crosstab Queries

• To create a crosstab query, you can start with
  your standard query and then go to design view.
• Then select from the Query menu, Crosstab.
• Another row on the bottom level will appear that
  says Crosstab
• Here is where you will select one row, one header
  and one column that will contain the values.
• For example, if you had a table of family members
  and you wanted to see their ages grouped by their
  name and their gender
• Select name as the Row Header, Gender as Column
  Header, age as Values

54
Crosstab Queries

• Crosstab queries are also a good way to see
  calculations on data. When you select a field as
  the Values you can also change how the data is
  computed from the line that says Total
• The column and the row headers will say Group
  by and you are able to change the Value field to
  Sum, Min, Max, Avg and so on.
• If you have data that is similar and can be
  grouped together, the Value field will then be
  displayed as a composite of all the Values that
  match

55
Parameter Queries

• A parameter query is a query that, when run,
  displays a box prompting you for information,
  such as criteria for retrieving records or a
  value you want to insert in a field.
• You can design the query to prompt you for more
  than one piece of information for example, you
  can design it to prompt you for two dates.
  Microsoft Access can then retrieve all records
  that fall between those two dates.
• Parameter queries can also be good for reports
  and forms that ask the user for a certain of data
  to be entered.

56
Parameter Queries

• To create a parameter query, click in the
  criteria section and enter an expression that
  will be used by the query
• Examples
• To make the user enter a name to select by, put
  in the criteria field
• Enter Name
• To make the user enter an age that will be
  greater than all the ages selected
• lt Enter Maximum Age
• The general rule is that you put the symbol you
  want to compare against (,gt,lt, LIKE, etc), then,
  inside brackets, put the message you want to
  appear

57
Parameter Queries

• Suppose you had a date and you wanted the user to
  enter two dates to mark all the acceptable dates
• Inside the criteria field, we can put two dates
  with the following code
• Between Enter First Date and Enter Second
  Date
• The dates the user enters will bound the select
  query

58
The Union Operator

• Just like in the theory part of the class, there
  is a real world Union operator. It will take two
  tables and combine all their fields into one
  beautiful table
• Go to the Query menu, then SQL Specific and then
  choose Union.
• A window will appear that will be blank.
  Unfortunately, we have to paste in or write SQL
  queries here.
• What we can do is make two queries, then paste
  them in and put union between them.
• The result will be a new table with both values

59
Union

• Simple union example.
• Say we had two tables. One is mothers, the other
  is children.
• First we make a query to select the name and age
• select name,age from Mothers
• Then we make a query to select name and age from
  Students
• select name,age from Students
• Now we take them both and put them in the Union
  window
• select name,age from mothers UNION select
  name,age from Students
• The result will be one big table

60
SQL Queries

• Most of the queries you create with the Wizard or
  design have counterparts in the SQL language.
• In fact, all your queries are turned into the SQL
  language before they are actually executed.
• You can see what the SQL code is for any query
  after you create it by clicking on SQL View in
  the View menu.
• You are also able to create your own SQL queries
  in here, which you can do once we learn the
  language.

61

• Microsoft Access
• Practical 4
• Data Access Pages, Importing/Exporting and
  PivotThings

62
Data Access Pages

• A data access page is a special type of Web page
  designed for viewing and working with data from
  the Internet or over a network from data that is
  stored in a Microsoft Access database or a
  Microsoft SQL Server database. The data access
  page may also include data from other sources,
  such as Microsoft Excel.
• The idea is that people dont even need to have
  the Access database on their computer in order to
  use the database. Instead, they can connect over
  a webpage and edit the database or view it

63
Data Access Pages in a web browser

• A data access page is connected directly to a
  database. When users display the data access page
  in Internet Explorer, they are viewing their own
  copy of the database.
• That means any filtering, sorting, and other
  changes they make to the way the data is
  displayed affect only their copy of the data
  access page.
• However, changes that they make to the data
  itself such as modifying values, and adding or
  deleting data  are stored in the underlying
  database, and are available to everyone viewing
  the data access page.

64
Creating Data Access Pages

• In Access, choose the Data Access icon on the
  left hand side. Youll then be able to edit an
  existing page or create a new page.
• Once again, the easiest way to create a page is
  through the wizard. Youll also be presented with
  familiar forms and buttons.
• With the wizard youll be asked to choose your
  table and the fields you want to include in your
  Access page.
• You can then add grouping. This will let you
  choose how you want the data to be organized.
• The final property sheet lets you choose the
  sorting order, if any, for your page. Then click
  finish and it will be saved.

65
Data Access Pages

• You are then, of course, able to edit the page in
  the Design view, similar to Forms and Reports. It
  provides an easy way to edit this new web page,
  and you can insert pictures and text with the
  Insert menu
• If you want to see how it operates, you can use
  the View choice, but what you really want is to
  see it work in a web browser
• So save the Data Access Page somewhere and open
  Internet Explorer. Inside IE, you can open the
  Data Access page, which will connect to the
  database and youll be able to use your database
  through the web.
• If you have a network, you can share the database
  over the network and let other people connect to
  it.

66
The PivotTable

• PivotTables and PivotCharts are new features in
  Access 2002 that give a whole new way to view and
  analyze data from databases.
• The PivotTable view is mostly about presenting
  the details of data. PivotCharts are about
  showing that data in nice pictures and graphs.
• The PivotTable and PivotChart are actually the
  same entity, but viewed in different ways. So if
  we make changes to one, it will affect the other
  one

67
The Pivot Chart

• Well start with the pivot chart, as it is easier
  to see what is happening.
• After you change the View to the Pivot Chart, a
  window will appear and the field list of all the
  possible fields will appear on the right. Youll
  be able to drag those fields onto the bigger
  window into three places category fields, data
  fields and filter fields

68
PivotCharts

• Category Fields these are like the x axis of a
  graph. They are responsible for grouping the data
  that you want to display. So if you want to group
  by Name and Phone Number, you can drag both of
  them to the bottom, to the Category fields
• Data fields these provide the data that will be
  summarized in your PivotChart or table. By
  dragging fields in over to here, youll begin to
  see the new information as the data is entered
  and counted in
• Filter fields This lets you decide which data
  you want to see. You drag a field in there and
  then by clicking on the little right arrow, you
  can decide which of the values inside that field
  you want to see

69
Different Calculations

• After you drag in fields into the Data fields,
  normally, the PivotChart will do a counting
  calculation, such as it will count up all the
  numbers of each, or take the sum of each,
  depending on how you chose your category fields.
• You can change that field though, so that it
  displays a number of different calculations. The
  ? symbol will be available after you select a
  data field. Then a list of different math
  operations will come down and let you choose what
  you want

70
PivotTable View

• After you create the PivotChart, you can change
  views to PivotTable. This will show the data that
  you used. You can also create a PivotTable first,
  then go to PivotChart.
• The PivotTable works in the same way. The top row
  will be the data fields, the column on the left
  is the category fields.
• You will also be able to change different math
  operations. Additionally, if you select a
  category, a small button will appear that looks
  like a calculator. This button lets you make your
  own calculations.

71
User Calculations
This window will let you create your own
calculations. What you do is select the
reference you want to use, then click on Insert
Reference To. That will place it in the window.
You can then use standard math things like,
,,-,/ to create new math formulas. When you are
done, you can press change and you will have
inserted a new field with this user defined
calculation.
72
Importing/Exporting

• When you have another source of data, maybe a
  text file, maybe an Excel file, you may need to
  put it into your database.
• You dont want to have to retype everything, so
  the word to take an entire file and put it into
  the database is called importing.
• Conversely, when you send data out of a database
  to another format or file, it is called
  Exporting

73
Importing

• To import another file, like a tab-delimited
  file or a comma-delimited file (a file that
  separates fields and data by commas or tabs) go
  to File -gt Get External Data and then Import.
• We will create a comma delimited file in Notepad
  first. Then we will import this and see how the
  importing process works.
• When we make our flat-file database in Notepad,
  we want to make sure to add a header line that
  will tell what the field names are for the
  different categories.

74
Flat File Example
Name,City,Address,Salary,EmployeeID Vil
iami Finau,Fasi,Road 1,20.00,1 Analisa,
Nukualofa,Road 2,10.00,2 Seletute,Mua
,Raod 3,5.00,3

• When you try to import a file like this, Access
  will automatically
• assume that you want the first line to be the
  field names
• and everything else after that to be the tuples
  that you enter
• You will also be able to specify what format you
  are using
• to separate in case Access cannot guess for you

75
Exporting

• When you want to send your data to another
  format, such as to Excel, or even to another text
  file (maybe you want to open the data in a
  program), you can use the Export function, which
  is under the File menu
• Then youll be asked for the place to save and
  under the File Type choice you can save what
  type of format you want to use.
• We will try to export to Excel format and to text
  format, and see if we can additionally import to
  excel.