Jan's Working with Databases

Design steps in the process to create a Make-Table query:

• State the Problem Clearly: Which students have assignments for the current grading period which were never turned in? A score of 0 was assigned after the due date.
• One solution: Use a Make-Table query to create a table that shows each student and assignment with a score of zero.
• Select: Add tables/queries and choose the fields to include in the new table - student name, assignment or test with description, class and subject, date it was assigned, date it was due, the grading period, score.
• Add Criteria: Enter a 6 to the Criteria row in the Grading Period column and a 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 to the left in the illustration below)
• What to Show: All fields in the query should show except perhaps the Score field. All scores will be 0! The Grading Period should show since I adjusted the criteria at the end of each term and renamed the query. At least I meant to do that! By including the period in the datasheet, it will be clear if I failed to update the query.
• Change the Select query to a Make Table query by clicking the Make Table Query button on the ribbon tab Query Tools: Design.

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

• Dialog: Name the new table.
  Notice that you can choose to create the table in a different database. But that database must already exist. You cannot create it from this dialog.
  
• View datasheet: Clicking the Datasheet View button shows the selected fields and records but does NOT create the table. Always verify that your query is selecting the correct records before running an action query! For example, it would be easy to omit the criteria for the grading period. Then the datasheet would include records from all grading periods in the database.

  

   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 if running again: Warning that the existing table (from running the query previously) will be deleted before the make-table query runs.
  
  Possible disaster: The existing table is deleted but the process to create a new version glitches, resulting in no table at all.
  
• Message: How many rows will be in the new table and warns that you cannot Undo this change.  
• View the new table: Once the table is created, it does not open automatically for viewing. You have to go to the Tables list in the Navigation Pane 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.

Possible error in thinking: Should a null score have been included in the query as well as 0? What about scores that were marked to Ignore? It depends on the question!

The process I used to create records in the Scores table created a record for every student for every assignment. I deleted records if that assignment was not given to that particular class. I marked the Ignore column if the score should not be included in averaging. Perhaps a particular student was absent on the day I gave a daily quiz. Or I might have given a test again and counted only the better score. It rapidly gets complicated in the real world!

Step-by-Step: Make-Table Query

---

What you will learn:

to create a select query to change the query to a make-table query to view the results and set a Primary Key

---

Start with: , Projects database from previous lesson open.

Making a new table out of existing data is often done for a temporary purpose. The table may help you with a complex analysis. It may be a way to export data to another database. Or, it may just be a convenient way to keep a copy of the data as it existed at a particular time.

For this project, you will create a table of "agents" of the World Travel Inc. travel agency.

Create a Select Query

1. Create a new query in Design View, based on the table Staff.
   Include in your query the EmployeeID, the three name fields, Country/Region, Title, and DateHired.
   

2. Sort the query on LastName, FirstName, MiddleName.
   (The fields must be in this order in the grid.)

   
   

3. In the Criteria row for the column Title, add "agent".
   

4. 

   View the datasheet.
   
   The query has selected records that have "Agent" in the Title column.
   

5. Save the query as QMake-Table Agents.
   It is still just a Select query at this point.
   

---

Change Query to Make-Table Query

1. Switch back to Design View.

2. Click the button Make-Table Query on the Query Tools: Design ribbon tab.
   The Make-Table dialog appears for you to enter a name.

3. 

   Name the table Agents. Leave the choice Current Database selected.
   

4. Click on OK.
   The query is still in Design View and has not run.
   

5. Run the query by clicking the Run button .
   A message box appears, telling you that how many rows you are about to add to a new table. You also get a warning that this action won't be in the Undo list.
   

6. Click on Yes.
   The table is created and shows in the Navigation Pane but it does not open automatically.

7. Save and close the query.
    

---

View Results and Set Primary Key

1. Open the new table Agents from the Navigation Pane.
   You should see the same records that you saw in the query's datasheet. The only difference is the Title tab.

 

1. Switch to Table Design View for the Agents table.
   The field EmployeeID was the primary key in the original table. The values in EmployeeID are unique, since that field is the primary key for the Staff table.

   But Access did not automatically create a primary key for the new table. Unexpected.
   

2. Select the field EmployeeID and click the Primary Key button .
   

3. Save the table and close it.
   [Agents]

Whether or not to have a primary key: There are situations when you do NOT want to have a primary key. Usually having a primary key is important to keeping the records in good order. Remember that a primary key must have a unique value for each record.