Tips Microsoft Access

Tables and relationships

2009-09-23T20:40:00.000-07:00

To 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.

1 Customer information that once existed in a mailing list now resides in the Customers table.
2 Order information that once existed in a spreadsheet now resides in the Orders table.
3 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.

Access database files

2009-09-23T20:36:00.000-07:00

You can use Access to manage all of your information in one file. Within an Access database file, you can use:

Tables to store your data.
Queries to find and retrieve just the data that you want.
Forms to view, add, and update data in tables.
Reports to analyze or print data in a specific layout.

1 Store data once in one table, but view it from multiple locations. When you update the data, it's automatically updated everywhere it appears.
2 Retrieve data by using a query.
3 View or enter data by using a form.
4 Display or print data by using a report.

All of these items — tables, queries, forms, and reports — are database objects (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.).

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.

What are these objects?

2008-05-27T18:09:00.001-07:00

When you create a database, Access offers you Tables, Queries, Forms, Reports, Macros, and Modules. Here's a quick overview of what these are and when to use them.

Tables. All data is stored in tables. When you create a new table, Access asks you define fields (column headings), giving each a unique name, and telling Access the data type. Use the "Text" type for most data, including numbers that don't need to be added e.g. phone numbers or postal codes. Using Wizards, Access will walk you through the process of creating common tables such as lists of names and addresses. Once you have defined a table's structure, you can enter data. Each new row that you add to the table is called a record. To define relationships between tables, click Database Tools | Relationships in Access 2007, or choose Relationships from the Tools menu in Access 95, 97, 2000, or choose Relationships from the Edit menu (Access 1, 2).

Queries. Use a query to find or operate on the data in your tables. With a query, you can display the records that match certain criteria (e.g. all the members called "Barry"), sort the data as you please (e.g. by Surname), and even combine data from different tables. You can edit the data displayed in a query (in most cases), and the data in the underlying table will change. Special queries can also be defined to make wholesale changes to your data, e.g. delete all members whose subscriptions are 2 years overdue, or set a "State" field to "WA" wherever postcode begins with 6.

Forms. These are screens for displaying data from and inputting data into your tables. The basic form has an appearance similar to an index card: it shows only one record at a time, with a different field on each line. If you want to control how the records are sorted, define a query first, and then create a form based on the query. If you have defined a one-to-many relationship between two tables, use the "Subform" Wizard to create a form which contains another form. The subform will then display only the records matching the one on the main form.

Reports. If forms are for input, then reports are for output. Anything you plan to print deserves a report, whether it is a list of names and addresses, a financial summary for a period, or a set of mailing labels. Again the Access Wizards walk you through the process of defining reports.

Pages (Access 2000 - 2003). Use pages to enter or display data via Internet. Pages are stored as HTML files, with data read from and written to the database. Michael Kaplan has published a free utility to convert Access forms and reports into Data Access Pages. Pages are deprecated in Access 2007.

Macros. An Access Macro is a script for doing some job. For example, to create a button which opens a report, you could use a macro which fires off the "OpenReport" action. Macros can also be used to set one field based on the value of another (the "SetValue" action), to validate that certain conditions are met before a record saved (the "CancelEvent" action) etc. Each line of a macro performs some action, and the bottom half of the macro screen provides the details of how the action is to apply.

Modules. This is where you write your own functions and programs if you want to. Everything that can be done in a macro can also be done in a module, but you don't get the Macro interface that prompts you what is needed for each action. Modules are far more powerful, and are essential if you plan to write code for a multi-user environment, since macros cannot include error handling. Most serious Access users start out with macros to get a feel for things, but end up using modules almost exclusively. On the other hand, if your needs are simple, you may never need to delve into the depths of Access modules.

Understand the basics of an Access database

2008-05-26T20:33:00.000-07:00

A 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.