FIT 100
Queries and Forms
Creating a Query to use with a Form
Creating a Form Using Design View
Add Data to Tables Using the Form for
Input
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:
- To create view queries that aggregate data from more
than one table for input or output of data.
- 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! Lab18.mdb is located at:
http://courses.washington.edu/gbw/fit100/Lab18.mdb
2.
Creating a Query to use with
a Form
- Generate
a query that will show the following information: product name, quantity
per unit, unit price, units in stock, units on order, notes supplier name
and country and category name.
- Name the query qslProduct and save it.
3.
Creating a Form Using Design
View
- Create a form in design view to update data to the Product
table. Name it frmProduct.
- 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.
- 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
- Drag and drop the attributes onto the form in an arrangement that suits you.
- 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! |
||
|
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.
- Switch to Form View and see the 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. You do not need to show the checkbox in the report.
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 II.