Example: Make-Table Query

The query Make Table-Zeroes-6thTerm from LanguageArtsClasses database brings fields from several tables and queries together. The purpose of the query is to make a table showing the assignments and tests in the 6th grading period that have a final score of 0.

Problem: I need to know who is missing which scores for the whole grading period. These are uncompleted assignments and tests.

One solution: Create a table that show this information along with the student's name.

Steps in the process:

• Select: Choose the fields to include from various tables/queries for the new table.

• Add Criteria: 6 in the Grading Period column and 0 in the Score column.
  I could have made this a parameter query by putting [Which grading period?] in the Criteria row for the Grading Period column. (This would actually have made more sense!)

• Sort:  Classes/Subjects and NameFull sorted Ascending (out of view in the illustration below)

• Which to Show: All fields except the Score field should have the Show box checked. All scores will all be 0! The Grading Period should show since I adjusted the criteria for each term and renamed the query.

• Change the Select query to a Make Table query.

Query Design View: Zeroes-6thTerm
(Some fields are out of view to the left)

• View datasheet: Clicking the View button shows the selected fields and records but does NOT create the table. Always verify that your query is selecting the correct records!

  

   Query Datasheet View:  View query results BEFORE running the query

• Run the query: Clicking the Run button   actually starts the process to create the new table.
• Message: How many rows will be in the new table and warns that you cannot Undo this change.
   
• Dialog: Name the new table. Notice that you can choose to create the table in a different database.
   
• View the new table: Once you click on OK to name the table, the table is created but it does not open for viewing. You have to go to the Tables list in the Database Window to open the new table and see your results.
   

Why did I want such a table? I don't really remember! It is one of several ways to see who needed to do make-up work and for what assignment or test.

Lessons Databases Appendix

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012