When you start using Access 2007 you need to think SYSTEMS! That's because an Access database consists of several components all linked together to form a single system. A database system can be seen as a set of input items, data storage areas, processing elements and output items such as paper printouts. At the heart of Access is the data storage area consisting of one or more tables. To input information into these tables you can use Forms, to process information you can use Queries and to output information you can use Reports. This article takes a look at Access 2007 from a systems point of view and summarises how the various elements link together.

When you first launch Access 2007 you're presented with a range of pre-built database templates including the latest version of the Northwinds database. All these templates let you build sample database systems with tables, forms, queries and reports all ready built. All you need to do is to add data into the tables. If you create one of these databases you can have a look at the various components accessed from the user interface.

If you want to build your own Access database you first need to decide what data to store in one or more tables. It's good practice to store each type of data in several separate tables rather than create one huge table. So you might have customers, orders and products in separate tables for example. Each of these tables stores one record in each row and has an index in the first column with the rest of each record spread over the other columns. The index identifier in the first column is used to find a particular record in the table.

Some tables can contain, as well as their own index column, an index identifier from ANOTHER table in a different column. For example an Orders table, in addition to its own index column, might contain the index identifier for the customer name in another column. The customer names are stored in a separate Customers table - don't forget the Customers table will have its own index column. You can link these table together by linking the index in the Customers table to the Customer identifier column in the Orders table. Doing this gives Access the ability to show details from more than one table at a time using a Query - we'll look at this shortly.

An important rule of table design is to store one piece of data in each table column, so for example, Surname and Firstname are in separate column headers rather than a single Name column. So if you want to, you can sort your table records in surname order.

You can type data directly into tables if you wish but if you're careless you can easily type into the wrong record row and if other people are also going to enter data into your database then this would be one way to mess up your data! A better way to input data is by using a Form. Forms can be built based on an existing table, but usually a form shows one record at a time on the screen in one or more vertical columns, which makes for easier and more accurate data entry. You can also navigate records from within a form and view each record in turn and you can make any changes if you wish. So a form can be both an input and an output item.

To process your data you use Access Queries. A query is based on a particular table (or linked tables) and can list, sort or select records from the table(s). When a query is created and saved, for example to show the customer records from the customers table in surname order, you are actually saving the query instructions rather than the table data. So if you subsequently add some new customers to the table and then run the query again, the query will list all the latest customers in surname order including the new ones.

If you have linked tables together you can use a Query to show data from more than one table at a time. We earlier described how we could link a Customers table to an Orders table. We could then create an Access Query to list all customers with their accompanying order details. You can click the "Show Relationships" toolbar button in an open Access database to see the tables links.

Try creating the Northwind database and then view the table relationships - you'll see lots of table links! You don't have to have as many in your own database, but hopefully this gives you some ideas for linking tables. The heart of a good database has several linked tables, with queries doing the hard work of extracting and displaying the required data from one or more tables.

To output data we can use an Access Report. Reports like queries can be based on one or more tables or based on a query. Reports are intended for printing so are only output items. When you create a report you can customise how the data is to be displayed, for example by choosing a sort order, presentation layout and theme.

You can also print any table or query from Access, but creating a report for printing gives you more flexibility over choice of data and how it is presented. Reports with numerical columns, for example salaries, can also include totals and subtotals. Examples of reports might be customer invoices, dispatch notes, orders summaries - in fact anything you like to print out.

So when working with Access 2007 its best to think of the database as a single system, with compartments for inputs, data storage, queries and reports. If you're interested in learning more about Access 2007 then consider signing up for an instructor lead training course - this is probably the best way to gain a rapid appreciation of the many features of Access 2007.