Access 2007 enables you to create multi-table queries that can help you make sense of information you have stored in related tables in your database. When you create a multi-table query, you can join together related information that resides in different tables and make queries that produce efficient, logical and useful results. Ultimately, you can use your multi-table queries to create multi-table reports in Access 2007.

To create a multi-table query, select the Show Table dialogue box. You can start a new query from this location. If you are in the query design window, you can select Design > Show Table to reach this point.

Select a table that you need for the query and click Add. Repeat to select other required tables. As you add tables, Access will show you the tables you’ve selected and the relevant links between the tables.

Once you have added the appropriate tables, you may drag the fields you need for your query to the Field cell in the design grid. Repeat to include any other relevant fields from other tables. You can also select fields from the dropdown list in the Field cells. If you choose this method of identifying fields, note that the Field name is always preceded by the name of the table it belongs to. You can reduce the number of fields displayed in the drop-down menu by selecting the appropriate table first. Only the fields contained within the selected table will appear in the drop-down list.

You can also identify the sort criteria and sort order, and select or de-select fields that appear in the report. The resulting collection of information, drawn from multiple tables, is called a dynaset. You may use a dynaset as the source for a report.

Using a dynaset as a source for a report is likely to be the easiest way to create a multi-table report, since the query has already been structured, and all of the related information is already grouped together.

Microsoft also provides a graphical Report Wizard to help you construct reports. You may access the Report Wizard by selecting the table or query you want to use as the basis for your report and clicking Create > Report Wizard. You may construct single and multi-table reports using the Report Wizard tool.

Notes for editors: Claire Jarrett is the managing director of Computer Training Solutions in Bristol, Solihull and Bracknell. Computer Training Solutions offer Excel training call 0800 019 6882 for more details.

Article Source:  http://ezinearticles.com/expert/Claire_Jarrett/77297

So this is a very brief outline view of using two or more Tables for a query and the usual practice is to ensure they are all connected (mustn’t leave any as a stand-alone).

The Simple Query Wizard tool is too basic and serves no real added value other than allowing you to pick and choose fields. You will need to set its criteria, sorting and possibly want to add a calculation or two.

Finally, the process described in this quick article applies to all versions of Access even those who may be still running a 16 bit application (pre Access 2000).

You may want to learn more about queries which is the heart of an Access database bringing life to your reporting. Why not invest in a couple of eBooks on building Access queries and learning about more queries?

Tags: Access 2007, building an access query, building multi-table queries, how to create a query, ms access queries, simple query wizard