Creating Tables and Queries in MS Access

(1)

02/23/2006 05:59 PM MS Access: Getting Started Guide

Page 1 of 20 http://www.cse.ucsc.edu/classes/ism050/Fall05/guide/guide1.html

Creating Tables and Queries in MS Access

These screenshots and steps are from MS Office 2003. Other versions should be similar.

Creating and populating a table in MS Access

.

1 Launch MS Access .

2 Select File > New and then Blank Database... from the New File side menu.

.

3 Name and save your database. .

(2)

.

(3)

The Data Type you select depends on the format of data for this field. If you need more help with data types, press F1.

.

6 Create a table with the following fields: Make, Model, Price and Discount (either as two separate fields or one), MPG (you may want to do City and Highway depending on your data), Seating (how many passengers it holds), Towing Option, and Cargo Space (you may want to do Min Cargo and Max Cargo depending on the format of your data). When you are done click the Close box.

(4)

.

7 Click Yes when prompted "Do you want to save changes to the desing of table 'Table 1'?" Choose an appropriate name for the table.

(5)

.

(6)

.

(7)

.

(8)

.

(9)

.

(10)

.

(11)

.

14 Your form will open. You can now fill in the information for each automobile. After you finish with one record, click the right arrow, or Next button to enter the next record. When you are done you can use the other arrow buttons to check your data.

(12)

.

15 After you have finished inputting all the records click the close button. .

16 Repeat these steps to create the table for the results of your department survery.

In this case you should make a field for your department name and then one field for each question/response in your survey.

If you allowed ranges as a response, do one field for the min and a second field for the max value of the range You can also make fields for the needs of the department as listed in the project description.

You may want a field for your recommendation (which can be filled out later).

(13)

Creating queries in MS Access

A "query" is a search condition you apply to your data. For the following examples, the tables below will be used:

.

(14)

.

2 You will be prompted for which tables to show. Select the table of your automobiles then click Add. That is the only table we need to see now so then click Done.

(15)

.

(16)

In the Field: box you select the field you which to search on.

In the Table: you would specify which table the field is from. Since we are only using the Autos table in this

example this will be filled in automatically.

Select a value in the Sort: box if you wish to sort the results on the field you selected.

Select Show: if you want this field to be visible in the query results. In Criteria: specify the criteria you are searching on.

Or: is used to specify ranges

.

4 Build your query: here is the example query I built to search for autos for Dept1 in my table above. This will display the

Make, Model and Price of all cars with Seating >= 4 and MPG >= 25. Notice how I do not specify any criteria for Make,

Model or Price. This means the results will not be filtered on those fields. Also, notice that the Seating and MPG fields will not show in my results.

(17)

.

5 Save your query. I called mine Dept1 since it searches based on the criteria from the first department. .

(18)

.

(19)

.

8 Experiment with other search criteria including the use of the "or" condition. You should be able to use greater than (">") and less than ("<") criteria to extract ranges on MPG, etc.

.

9 Repeat these steps for each query you wish to make and run .

10 Note that the you can later access the results of each query by selecting Queries under Objects and then opening the

(20)

If you have any questions about citations or using citations in MS Word please email me: [email protected]

This guide was created based on Shivkumar Shivaji's database guide for previous offerings of ISM 50. More advanced material can be collected from http://www.teachers.ash.org.au/jfuller/access/access2000.htm