FIT 100

Queries and Forms

Creating a Query to use with a Form. 1

Creating a Form Using Design View. 1

Add Data to Tables Using the Form for Input 3

Query the New Data. 3

Generate a Report 3

 

Introduction:

You have created forms for users to input data to a single table.  Today you will generate the query needed allow input to a single table while outputting from more than one table.

 

Objectives:

  1. To create view queries that aggregate data from more than one table for input or output of data.

  2. To create forms based on those same queries that will allow a user to add data or view data using a friendly interface.

 

TO BEGIN: 

1.      Download a copy of a database to use just for this lab!

lab20.mdb is located at:
http://courses.washington.edu/fit100/au02/labs/lab20.mdb  

 

Working with Queries
 

2.     Creating a Query to use with a Form

 

  1. Generate a query that will show the following information: product name, quantity per unit, unit price, units in stock, units on order, shipovernight?, notes, supplier name and country and category name. 

    You will need to gather these fields from different tables, so look at the design view of each table provided to see what tables need to be included in your query.

  2. Name the query qryProduct and save it.

 

Forms Based On Queries

 

3.     Creating a Form Using Design View

  1. Create a form in design view to update data to the Product table.  Name it frmProduct.

  2. To add the fields to the form, simply access the Form properties

·         Right click on the form title bar, select Properties

·         Make sure the Form object is selected in the window

·         Select as the record source qryProduct from the list.

 

 

  1. A window with a list of the attributes for the qryProduct virtual table should pop up.  If it doesn’t:

·         Go to View>Fields List on the Menu bar

 

  1. Drag and drop the attributes onto the form in an arrangement that suits you.

 

  1. Add a combo box to the form to look up countries.  Add the following values as part of the combo box:

 

Country

If you’re feeling confident in your query skills, figure out how to create a query with just the country values in it and then use as the source for the combo box!
 
Australia

Brazil

Canada

Denmark

Finland

France

Germany

Italy

Japan

Netherlands

Norway

Singapore

Spain

Sweden

UK

USA

         

          Have the combo box place the values selected in the Country field.

  1. Switch to Form View and see how it looks.

4.     Add Data to Tables Using the Form for Input

Using frmProduct, go through 10 different records and do the following:

A.     Find records where the country is NOT already USA and change the Country values to USA.

B.      If the Supplier country is the USA, then the product can be shipped overnight. (Check the checkbox)

5.     Query the New Data

You made changes in several records.  Any product supplied by a supplier in the USA can be shipped overnight, anywhere in the world. 

 

Using the QBE:

A.     Display all product names, their category and the supplier name if they do ship overnight.

B.      Display all product names, their category and the supplier name if they do ship overnight AND they are not discontinued.

 

6.     Generate a Report

 

A.     Generate a report of products that can be shipped overnight that aren’t discontinued.  Generate one report with the checkbox showing.  Generate a second report off the same query without the check box showing.

 

B.      Go into design view and make sure the report label is understandable and not just the name of the query the report is based on.

 

 

 

This lab is designed to give you practice that is related, but not identical to, Project 3, part B.