Access is database software that allows users to hold a large amount of information. The components of a database are tables, queries, forms, reports and macros. Tables allow the user to hold the raw data. Queries manipulate data in the tables. Forms can be used to enter data into the tables and navigate the database. Reports are for presenting the data in a printer-friendly format and macros help automate database tasks. The most pivotal component of a database are queries. Just as the name implies, a query allows the user to inquire about the data in the tables.
Several types of commonly used queries exist in Access: select, append, update, delete, make table. The goal of the query determines the type of query to use. A select query helps the user simply pull information from the table based upon the user-defined needs. For example, a select query can isolate customer names beginning with the letter "M." An append query is created to add information to a table. An update query can modify table records according to the user's needs. Delete queries remove records from the table. A make table query allows a user to make a new table from selected records.
Queries allow the user to calculate individual fields. The user can perform basic calculations such as the addition or multiplication of fields or more complex calculations for engineering or statistical needs. The built-in calculations of Access are the same as those found in Microsoft Excel. Through a query, items can also be grouped or sorted according to the user-specified criteria.
The data in a query is often pulled from tables in a database. The table can be in the present database, in another database or from an enterprise system such as the AS/400. More than one table can be placed in a query. Queries can also pull information from other queries. In other words, queries can be embedded within queries. Similar to tables, two or more queries can be in one query. Within an Access query, tables and queries are linked by fields common to each.
A query can be built using a wizard (step-by-step guide) or from scratch. Those new to Access databases should ideally use the wizard. It allows the user to become comfortable with queries and the how to establish links between the tables and queries the database uses.
To prevent duplication, select a table field that will be the primary key. This primary key is a field that contains unique information across each record. For example, a primary key for a collections database could be invoice number or customer account number. If a primary key is selected, there is less of an opportunity for record duplication when the query is run.