Creating Tables and Queries in MS Access

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.


4. Under Objects select Tables and double-click Create table in Design View.
   


5. Enter Field Names. Select the appropriate Data Types and add a Description.
   
   
   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.
   


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.
   


8. You will then be asked to define a primary key. Click Yes do "Do you want to create a primary key now?"
   


9. Under Objects select Forms and double-click Create form by using Wizard.
   


10. Select all fields except for ID (this is the primary key and will be filled in automatically). Then click Next.
    


11. Columnar is a good choice for layout.
    


12. Standard is fine for style.
    


13. Name your form and click Finish.
    


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.
    


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).
    • When you make the form, make sure you select the Department Table and not the Autos Table:
      

Creating queries in MS Access

1. Under Objects select Queries and double-click Create query in Design View.
   


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.
   


3. You will then see a query building window that looks like this:
   
   • 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.
   


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


6. Run your query
   


7. The results of your query will display. Check these results with the tables given above to convince yourself this is correct.
   


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 appropriate query.
    

If you have any questions about citations or using citations in MS Word please email me: jflynn@ucsc.edu