7.1: Query Basics
- Page ID
Queries are far more powerful than the simple searches or filters that might be used to find data within a table. This is because queries can draw their information from multiple tables. The real power of a relational database, like Access, lies in its ability to quickly and easily retrieve and analyze data by running a query. Access queries allow users to pull information from one or more tables based on a set of search conditions the user defines. A common synonym for the word query is: question or inquiry. Queries essentially ask questions of databases. Queries do not store data. Therefore, a major benefit of queries is the ability to save a query to be re-ran at a later time after table data has been updated. This eliminates the need spend time recreating all of the fields and criteria desired to answer a popular question, such as a customer calling frequently to check on their latest account balance.
Queries can be something simple, like list all names in a table, or something a little more complex, like list all products that are priced over certain amount. A query can be very simple or very complex, depending on the user’s requirements. As tables grow in size they can have hundreds of thousands of records, which makes it impossible for the user to pick out specific records from that table without a query. The tricky part about queries is that one must understand how to construct one before one can actually use them.
When a query is ran, the results are presented in a table, but when a query is designed a different view is used. This is called Query Design View, and it lets users see how the query is put together. While the Simply Query Design Wizard can be used to display fields from one or more tables or queries with the option to choose a detailed or summary report, the wizard is limited by its inability to select data criteria. This is why most Access queries are created in Query Design View, as this allows for far more complexity to be added to create complex simple to complex queries.
After clicking the Query Design button on the Create tab, the Query Design View opens and by default the Show Table dialog window appears with a list of available tables and queries that can be added to build the query. Select one or more tables that contain the data desired to produce the answers to the potential questions. However, only add tables that contain fields that you intend to add to the query design. Tables added to the query design that do not have fields in the query grid can skew the query results. The exception to this rule is if the table is needed to join two or more other tables that are part of the query design. After the Show Table window is closed, it can be re-opened by clicking the Show Table button in the Design contextual tab.
The next step to creating a query is to add the fields from the tables/queries into the query design grid (at the bottom of the window). Fields can be added by double-clicking the fields from the tables in the Object Relationship pane. A user can also drag a field from the table to the grid. A field can also be selected from the Field drop-down list in each column of the query design grid. If the fields are added in the wrong order, the column can be deleted or moved by clicking the top of the column and dragging the field to the appropriate location.