O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Creating Tables with Make-Table Queries

Problem

How do you create a new table to hold the records returned from a select query?

Solution

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.

Two tables with one-to-one data

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required