the structure of an Access database
Author: mety Labels:: the structure of an Access databaseA database is a collection of information that is related to a particular subject or purpose, such as tracking customer orders or maintaining a music collection. If your database isn't stored on a computer, or only parts of it are, you may be tracking information from a variety of sources that you must coordinate and organize. For example, suppose the phone numbers of your suppliers are stored in various locations: in a card file containing supplier phone numbers, in product information files in a file cabinet, and in a spreadsheet containing order information. If a supplier's phone number changes, you might have to update that information in all three places. In a well-designed Access database, the phone number is stored just once, so you only have to update that information in one place. As a result, when you update a supplier's phone number, it is automatically updated wherever you use it in the database. Access database filesYou can use Access to manage all of your information in one file. Within an Access database file, you can use:
  Store data once in one table, but view it from multiple locations. When you update the data, it's automatically updated everywhere it appears. Retrieve data by using a query. View or enter data by using a form. Display or print data by using a report.Note Some Access databases contain links to tables that are stored in other databases. For example, you may have one Access database that contains nothing but tables, and another Access database that contains links to those tables, as well as queries, forms, and reports that are based on the linked tables. In most cases, it does not matter whether a table is a linked table or actually stored in the database. Tables and relationshipsTo store your data, you create one table for each type of information that you track. Types of information might include customer information, products, and order details. To bring the data from multiple tables together in a query, form, or report, you define relationships between the tables. Customer information that once existed in a mailing list now resides in the Customers table. Order information that once existed in a spreadsheet now resides in the Orders table. A unique ID, such as a Customer ID, distinguishes one record from another within a table. By adding one table's unique ID field to another table and defining a relationship between the two fields, Access can match related records from both tables so that you can bring them together in a form, report, or query.QueriesA query can help you find and retrieve the data that meets conditions that you specify — including data from multiple tables. You can also use a query to update or delete multiple records at the same time and to perform predefined or custom calculations on your data. The Customers table has information about customers. The Orders table has information about customer orders. This query retrieves the Order ID and Required Date data from the Orders table, and the Company Name and City data from the Customers table. The query returns only orders that were required in April, and only for customers who are based in London.FormsYou can use a form to easily view, enter, and change data one row at a time. You can also use a form to perform other actions, such as sending data to another application. Forms typically contain controls that are linked to underlying fields in tables. When you open a form, Access retrieves the data from one or more of those tables, and then displays the data in the layout that you chose when you created the form. You can create a form by using one of the Form commands on the Ribbon, the Form Wizard, or create a form yourself in Design view.  A table displays many records at the same time, but you may have to scroll horizontally to see all of the data in a single record. Also, when you view a table, you can't update data from more than one table at the same time. A form focuses on one record at a time, and it can display fields from more than one table. It can also display pictures and other objects. A form can contain a button that you click to print a report, open other objects, or otherwise automate tasks.ReportsYou can use a report to quickly analyze your data or to present it a certain way in print or in other formats. For example, you may send a colleague a report that groups data and calculates totals. Or, you may create a report with address data formatted for printing mailing labels.  Use a report to create mailing labels. Use a report to show totals in a chart. Use a report to display calculated totals.See details about the objects in a databaseOne of the best ways to learn about a particular database is by using the Database Documenter. You use the Database Documenter to build a report containing detailed information about the objects in a database. You first choose which objects will be detailed in the report. When you run the Database Documenter, its report contains all of the data about the database objects that you selected.
Explore a table in Design viewOpening a table in Design view gives you a detailed look at the table's structure. For example, you can find the data type setting for each field, find any input masks (input mask: A format that consists of literal display characters (such as parentheses, periods, and hyphens) and mask characters that specify where data is to be entered as well as what kind of data and how many characters are allowed.), or see if the table uses any lookup fields — fields that use queries to extract data from other tables. This information is useful because data types and input masks can affect your ability to find data and run update queries. For example, suppose that you want to use an update query to update particular fields in one table by copying data in similar fields from another table. The query will not run if the data types for each field in the source and destination tables don't match.
See the relationships between tablesTo see a graphical representation of the tables in a database, the fields in each table, and the relationships between those tables, use the Relationships object tab. The Relationships object tab provides an overall picture of the table and relationship structure of a database — crucial information when you need to create or change the relationships between tables. Note You can also use the Relationships object tab to add, change, or delete relationships.
The Relationships object tab appears and shows you the relationships between all of the tables in the open database.  For more information about table relationships, see the links in the See Also section. See how objects use other objectsThe Object Dependencies pane illustrates how database objects, such as tables, forms, queries, and reports, interact with or depend on other objects. You can use the Object Dependencies pane to help avoid inadvertently deleting record sources. For example, suppose that you have a Quarterly Orders query in a Sales database, and you no longer need the query. Before you delete the query, you should find out if any of the other objects in the database, such as a form or report, use the query as a data source. You can then either modify the dependent objects to remove references to the query, or you can delete the dependent objects along with the query. Viewing a complete list of dependent objects can help you save time by removing the need to manually check object properties and minimize errors by finding the details that a manual audit might miss. When you want to change the design of a database object, the Object Dependencies pane can also be useful by showing you how other objects will be affected by the design change. You should use the Object Dependencies pane to help you plan major design changes. Use the Object Dependencies pane
Remember these facts as you use the Object Dependencies pane:
|
