In order to create a multi-table report we must develop a query that will pull the needed information.

In the database window choose the Query page and start a new query. Choose Design View in the dialog that pops up.

A new query form will appear with the Show Table dialog. If Show Table doesn't appear you can get to it from Query in the Microsoft Access main menu bar. Choose Systems and click Add. Then add Software then Programs, then Manufacturers.

Microsoft Access will draw lines connecting key fields with linking fields in the several tables.

In this project notice that the field SysID in the Systems table is linked with Software.SysID. Software.ProgID is linked with Programs.ProgID. This linkage is the key to the way relational database systems work. This eliminates the need for repeatedly entering the same data in every table that adds a new context for reporting purposes.

Notice also that Manufacturers.MfgID links to MfgID in both Systems and Software. This becomes a problem for the query because it will not pull both the system manufacturer and the software manufacturer in the same query. So the query pulls no data at all. We solve the problem in this way: Drag the little table windows around to separate them enough so that each connecting line identifying the links is separated well enough for you to identify the line joining Manufacturers.MfgID with Systems.MfgID. Click on that line and it will become bold. Press the delete key. Now for the purposes of this query for the MfgID field only the Manufacturers _ Software link remains.

The report we intend to build will focus on the software owned by our imaginary company. We want to know which programs are installed on the various computers and which programs are not installed and remain on the shelf. For simplicity we will identify the computers by user.

Now we will select the fields to be included in our query. Drag the SysUser and SysName from the list of fields in Systems. Put SysUser in the first column and SysName in the second. The remaining fields in the query will be Programs.ProgName, Programs.ProgVersion, Software.*, and Manufacturs.MfgNfame. The asterisk from the Software table means show all the fields. P> Sort the query by user name. Click the down arrow of the Sort row in the SysUser column. Choose Ascending unless you want them in reverse order.

Click the little table icon under File on the main menu to check your work. If you haven't added some sample data to your tables nothing will happen. But if you have been testing with realistic data you should get a table of software for each computer user you have in your database. Click the design icon that replaced the table icon, then click the diskette icon and save the query. I named mine Systems Software List Query .