IENG 482 - Engineering Information Systems (Spring 2000)
In-Class Assignment #2
 (10 points)
Microsoft Access - Using Tables, Queries, and Forms
Consider the same customer billing information system.  We have three (3) tables of information: People (SSN, LastName, FirstName, Phone, Zip), Items (Description, Cost, InStock), and Purchases (Purchase_No, Date, SSN, ItemDescription, Quantity).
In this exercise, we want to utilize several of the functions offered through queries.  So, let's start with the above tables and proceed with developing the appropriate queries.  Make sure that the database is populated with enough records to make the query results interesting.  Save each query separately.  You can use an existing query as a starting point for the next query (highlight the query, right click, save-as, and 'within current database' with the new query name).

The following is a summary of the steps covered in this in-class assignment:
  1. Work with the automated wizard, and customize your queries in design view.
  2. Create a query that lists all purchases made by all customers.  Include the following fields:  Date, SSN, LastName, Phone, ItemDescription, Cost, and Quantity.
    • Now include a calculated field for "TotalCost" by combining Cost and Quantity.  Make sure your cost fields are data type "currency".
    • Perform a query that only shows purchases by Smith and Jones.
    • Perform a query that only returns purchases over $5,000.
    • Query all purchases between February 1, 2000 and February 29, 2000.
  3. Ensure that this query can work as an AutoLookup Query for entering new purchases.  The join field from the many-side of the relationship must be included.  The join field on the one-side must have a unique index.  Watch as you enter a new purchase.  Fields from other tables (LastName, Phone, Cost) will be filled in as you enter your information.  The "TotalCost" field will also calculate once you enter a quantity.
  4. Determine the cost per customer by grouping on the appropriate field or fields.  Remember that the last name may not be unique.  Then SUM the field to get your total cost.  (You must turn on "Totals" via a right-click anywhere on the design grid.  The query can be based on tables or other queries.)
  5. Make a Crosstab Query that shows the amount each customer has spent on each available item, and the total amount spent per customer.  To obtain all the numbers we need, you should base this query off an already existing query.
  6. Check to see if there are any duplicate records in the database.  A duplicate record will have the same customer, date, and item purchased.
  7. Use a Select Query to reduce the number in stock of each item by the total quantities sold.  This requires both a SUM calculation on quantities purchased by turning "Totals" on, and an expression to recalculate the inventory level.
  8. Create an Update Query that increases the level of InStock by 25 items each.  This will represent a shipment received.
  9. Bonus on Forms:  Create some sample forms for the tables and/or queries.  Use the wizard, and then customize your forms.  Consider creating a form based on a query.  This will be a very powerful way of entering in information.
IENG 482 Assignments

IENG 482 Home Page