Queries allow you to combine data from multiple tables. It's often better to create several small tables and combine the data with a query. For example, let's say you want to create a database of appliances you own. Your first attempt at designing a table might look like:
For small tables of 10-15 records this table design might be adequate. However, for larger tables it may create a problem. The problem is the table contains duplicate data: Best Buy is listed twice. What if the phone number for Best Buy changes? You would need to track down and change all references to this store. The first tenet of good table design is that tables should not contain duplicate information.
A better design is to create two tables and combine them with a query:
Notice the two tables in the above example, Items and Stores. An additional field, Store Index, was created to link the two tables. The query Inventory links and displays data from these two tables. Note: Inventory doesn't contain any data, it merely displays data from the tables Items and Stores. If you change the name or phone number of a store in Stores the change is propagated to the query Inventory immediately. This is a better design because the information on each store is stored only once.
|
|
|
In the animation above notice how the change to the table propagates to the query and how a change in the query propagates to the table. A query allows you to define a "virtual table" with a format that is best for you.
