Access Topics Covered

 

Access File Components

  • Tables – contain the actual data
  • Queries – represent questions to be asked (select queries) or systematic ways to alter the data (action queries)
  • Forms – used to allow user-friendly data input
  • Reports – provide a user-friendly way to summarize and display query results

 

Example we study in depth: ComputerStore2.mdb

 

Each table has records (rows)

Each record has fields (columns)

 

E.g., in ComputerStore2.mdb, the Customers table has 25 records, each containing 8 fields

 

An important concept: Primary key = one (or more) field(s) whose value uniquely identifies each record.  For example: CustomerID in Customers table, OrderID in Orders table, ProductID in Products table.  Other examples used in practice: Student ID (or Social Security Number) for distinguishing student records at Northeastern; ISBN number for books.

 

Access Tables have

  • Design View
  • Datasheet View

 

We modified the design of the Products table in ComputerStore2.mdb to include a Validation Rule and Validation Text for the UnitsInStock field.

  • Validation Rule: >=0
  • Validation Text: Units in stock cannot be negative.

 

We used the datasheet view to add and delete some records in the Customers table.

 

Another important concept: Foreign key = a field (or possibly multiple fields) that points to the data in another table; always matches the primary key of the table pointed to.  For example, CustID in the Orders table is a foreign key corresponding to the primary key CustomerID in the Customers table (essentially serves as a shorthand indicating which customer placed a given order).

 

Access Queries have

  • Design View
  • Datasheet View
  • SQL View

 

Select queries we’ve designed:

 

Single-table queries

 

  1. Which customers are in Miami?
  2. Which customers are in Miami, FL? [Save as MiamiFLCustomers]
  3. Which customers are in Miami or FL?
  4. Which customers are in Miami, FL or in MA?
  5. Which customers are not in MA?
  6. Which products have a weight over 10 pounds?
  7. Which products have a weight less than 20 pounds?
  8. Which products have a weight between 10 and 20 pounds?
  9. Which customers are in <user-specified state>?  [Parameter query]
  10. Show all customers by name in alphabetical order.

 

Multiple-table queries (important to get the joins right and not include unnecessary tables!)

 

  1. Who placed an order on 4/22/1999?
  2. Which customers in Miami, FL placed an order after 4/25/1999? [Do 2 ways: with Customers and Orders tables, or with MiamiFLCustomers query and Orders table]
  3. Which customers ordered HD Floppy Disks? [Save as WhoOrderedHDFloppyDisks]
  4. Which customers ordered monitors? [Save as WhoOrderedMonitors]

 

Queries using calculated fields

 

  1. For each product, what is the total retail value of our inventory of that product?
  2. For which products is the total retail value of our inventory of that product at least $100,000?
  3. For each order detail, what is its total weight?
  4. Give the full name of each customer.

 

Totals (aggregate) queries

 

  1. For each order, how many different products were ordered?
  2. For each order, how many total items were ordered?
  3. For each order, what is its total weight? [Save as TotalWeightPerOrder]
  4. For each customer, what is the total weight of all orders placed by that customer?
  5. What is the total weight of all orders?
  6. How many monitors did each customer order?
  7. For each customer ordering more than one monitor, how many did each order?
  8. What is the total retail value of our inventory of all products whose unit price is over $1000?

 

Queries requiring the use of subqueries

 

  1. What is the average weight per order?
  2. For each state, what was the cost of the most expensive order from that state?
  3. Which customers have placed orders for both a monitor and HD Floppy Disks (not necessarily part of the same order)?
  4. Which customers have ordered both a monitor and HD Floppy Disks as part of a single order?

 

Report Design:

  • Usually linked to (gets data from) a single query
  • Bands: report header/footer, page header/footer, group header/footer, detail
  • Possibility of subgroups, sub-subgroups, etc.
  • Labels vs. text boxes
  • Use of Excel-like expressions for performing computations within the report
    • & for string concatenation
    • Sum
    • Avg
    • Count

 

Select queries vs. action queries:

  • Select queries gather information from the database but do not alter the underlying data
  • Action queries potentially make changes to the underlying data

 

Be careful when running action queries – they can’t be undone.

 

Some action queries:

 

  1. Increase the price of all monitors by 10%. [update query]
  2. Make a copy of the Products table. [make-table query]
  3. Remove all printers from the Products table. [delete query]
  4. Add a large batch of customers to the Customers table. [append query]

 

Importing data from an Excel file into an Access table

 

Exporting result of an Access query into an Excel file