How do you create a new table to hold the records returned from a select query?
When you query records from an existing table or tables, you may want to place the returned records into a new table. This is accomplished with a make-table query.
If data already exists in tables, why bother making new tables holding the same data? Here are a couple of reasons:
To combine related, nonhierarchical data into a single table.
To segregate a larger table of data into a number of smaller tables. The segregation is typically based on values in one or more key fields.
To illustrate the first example, Figure 3-14 shows two tables that are essentially related—they share a common EmployeeID field. However, there is no one-to-many relationship. Each table contains a single record per employee. Keeping the employee names in one table, and the hire date/department information in another table, might have some business relevance, but it serves no particular design purpose. Combining this data into a single table has merit. The combined table will have one EmployeeID field and three data fields.
Figure 3-14. Two tables with one-to-one data
To produce the combined table, place the two existing tables into an Access query design, and place all the fields in the grid (including only one instance of the EmployeeID field). Designate the query as a make-table ...