access
Append Query
An append query will add its results to the end of an existing table in the database.
Query Types
By default, all Access queries are select queries, which combine fields from multiple tables into one datasheet. However, there are other kinds of queries that perform actions on the data in your tables. For example, append queries add new records to tables and delete queries remove records based on specific criteria.
Select Query
By default, all Access queries are select queries. A select query uses criteria to pull information from one or more related tables and displays the data on a single sheet.
Open
Click the Open tab to open an existing database from your computer.
Crosstab Query
Crosstab queries use one field as row headings, another field as column headings, and a third field as a numerical value. The numerical value can be the sum or average of a series of numbers or a count of individual records
Delete Query
Delete queries completely remove the selected records from the database.
Add & Delete Group
Each button in this group adds a new field of that type to the table. Delete removes the currently selected field.
Data Type
Once you've added a field name, use this column to choose its data type. The most common data types are Short Text (for text content up to 255 characters), Date/Time, and Number.
Queries
Queries are a simple and effective way to extract useful information from your tables. One of the most basic functions of a query is to combine data from multiple tables into a single datasheet. For example, if a database has people stored on one table and their phone numbers stored on another, a query can instantly give a directory of each person's phone number. Queries can also perform calculations on data, such as totals, averages, and counts.
Tables
Relational databases store data in tables. Instead of rows and columns, database tables consist of records and fields. Each record in a database relates to a unique person, object, or event. For example, each record on the Employees table would store information about a unique employee.
Reports
Reports present information from tables and queries in an easy-to-read and printable format. One of the best features of Access reports is that they are dynamic—once a report is designed, it is automatically updated to include any new records.
The Print tab displays print options for the database object that is currently open and selected.
Forms
The main purpose of forms is to serve as a user-friendly interface for data in tables. Using a form prevents users from accidentally changing important table settings and makes it easier to add data without errors. This is especially important when the database is being shared with inexperienced computer users.
Totals
This adds the Total row to the query design grid. By default, this row is set to Group By for all fields, effectively doing nothing. However, by changing its value to Sum, Count, or Average, you can create queries that perform mathematical analyses on your data.
Database Properties
This allows you to enter database properties, such as a database title, subject, author, and category.
Field Size
This allows you to set the maximum number of characters entered into a short text field. The highest number possible is 255.
View
This button (also found in the Home tab) lets you switch between datasheet view and design view. Generally speaking, you'll create queries in design view, then switch to datasheet view to see the information they assemble.
Show
This check box lets you choose whether a field will appear in datasheet view for the query. This is useful when you need to include a field in the query but don't need to see it in the spreadsheet.
Field Name
This column allows you to create new table fields by adding a field name.
Table
This creates a new table and opens it in datasheet view. When you create a table this way, Access automatically adds an AutoNumber primary key field called ID.
Table Design
This creates a new table in design view. When you create a table this way, Access does not automatically designate a primary key field.
Data Type
This drop down displays the data type of the currently selected field.
Make a Table Query
This is similar to a select query, but instead of showing the results on a datasheet it will create a new table with results.
Modify Lookups
This launches the Lookup Wizard. Lookup fields can pull information from a table or query, or you can enter a list manually.
Name & Caption
This lets you add a field name and a user-friendly caption to the currently selected field.
Default Value
This lets you assign a default value for new records in a field. Default values do not affect records that have already been entered.
Primary Key
This lets you choose which field to use as the primary key. In the image above, the EmployeeID field is the primary key, as designated by the small key icon next to its row.
Show Table
This opens the Show Table dialog box, which allows you to add new tables to the relationships diagram for the query.
Relationships
This opens the relationships diagram where you can view existing relationships between tables, as well as create new ones. The Relationships button can be found in the Design and Table contextual tabs, as well as the Database Tools tab.
Sort
This row allows you to choose how to sort your data. The most common way to sort is Ascending, which means lowest to highest for numbers or alphabetical order for text
Total
This row doesn't appear by default and must be turned on by clicking Totals in the Show/Hide group of the Design tab. This row allows you to perform summary calculations on your data. In the image above, the query is grouped by product names and types, and it shows the
Caption
This row lets you add a caption or user-friendly title to a field.
Criteria
This row lets you create a query that shows only data that meet certain criteria. Criteria can be text, numbers, or a formula. A common formula is Between, which returns all records that fall between two values. Another is date(), which returns the current date. To find all records where the date falls within the past seven days, use the formula Between date() and date()-7.
Field Name and Caption
This row shows the name of the field to be included in the query. By default, it matches the field name of the table. However, by adding a caption, the name seen by the user can be changed. Add a caption to a field by typing the caption and a colon before the field name. For example, the price field in the image above has been given the caption "Total Price".
Table
This row shows which table the field comes from.
Run
This runs the query you have designed. In the case of select queries, it has the same function as switching to datasheet view. However, with other types of queries, it will execute an action on your data tables.
View
This switches between design and datasheet views.
Update Query
Update Queries are similar to "find and replace" in other Office applications. They select records based on criteria, then make changes to all of the chosen records at once.
New
Use the New tab to create a new database.
Control Margins
Use this button to adjust the margins of individual fields within a form. Margins are the space between the edge of the field and the field content.
Control Padding
Use this button to change the padding of the selected elements on a form. Padding is the space between form elements.
Anchoring
Use this button to choose how a form element will be anchored. Anchoring a form element makes an element stretch, shrink, or move relative to the size of the form. By default, all elements are anchored to the top left of the form, and will stay there regardless of the size of the form. However, you can also anchor elements to any of the other corners, or set an element to stretch across the whole form.
Run
Use this button to execute an action query. Action queries are queries that directly modify tables, rather than simply presenting data. On the ribbon, action query types are designated by an exclamation point on their button.
