The Report Wizard in Access 2007 can help you create a single-table or multi-table report. Multi-table reports help you look at individual records or data collections grouped by a logical category. For example, if your database holds information about sporting goods stores, and you want to create sales reports for each store, you could create a multi-table report that shows you the sales volumes for the individual stores. A sub-report might contain information about individual product categories sold within each store.
To create a multi-table report with sub-reports in Access 2007, first you’ll need to select the table or query that will serve as the basis of your report. Select Create > Report Wizard to start the Report Wizard tool.
The table or query you selected will appear in the Tables/Queries list. To choose a different source, select a new source from the Tables/Queries list, then select each field you want to include in the report, and use the > button to add them to the Report Wizard.
You can add more tables and fields to the report by selecting a new table or query from the Tables/Queries list and choosing the appropriate fields for inclusion. Repeat this as many times as necessary to get all of the fields you want in your report. When you have added all of the appropriate tables and fields, click Next.
Choose the table that will serve as the basis for the main report. In the case of the sporting goods store example, the main report will contain sales data for each store. Other tables you may have included in your report may show types of sporting goods for sale in each store. When you have chosen your main report source, click Next.
Click the field that shows the sub-grouping you want to use in your report. In the store example, sub-groupings might include soccer products, running products and cycling products. When you have added all of your sub-groups, click Next.
Choose the sort order for your report. The sort order will determine how your report information appears when all relevant records have been sorted appropriately. Click Next.
Add any summary calculations. These calculations will allow you to create a summary total for numerical fields related to each sub-group in the report. For example, a summary report might give you total sales in pound, or the total quantity of a particular item in a report. Click Next.
Select the layout and orientation for your report. Select the report style for your report and give your report a title. You may preview your report and make any needed modifications. When you are done, click Finish.
By using the Report Wizard, you can create in-depth multi-table reports and sub-reports in Access 2007.
Claire Jarrett is the managing director of Computer Training Solutions with centres in London Bristol, Solihull and Bracknell. Computer Training Solutions offer Access and Excel courses or call 0800 019 6882
Article Source: http://ezinearticles.com/expert/Claire_Jarrett/77297
This article gives you the essential heads up on how to use the report wizard tool which covers all modern versions (from Access 97 to Access 2010). It is really a simple but effective tool for Access reports and saves some initial development time.
There are some additional points that need clarifying and must be considered when using this tool.There are:
- You will need build this type of report (multi-tables/sub-reports) probably using a query or SQL statement and not a table (unless it is very basic).
- The key fields you choose for setting criteria and sorting (to group data sets) should be indexed using the obvious primary key but also setting foreign (secondary) keys too.
- Make sure your data types are correctly set (as numeric) for any summary calculations that you intend to automate during the wizard steps.
Remember, a sub-report will be stored as a separate object and to help associate these objects with the main report object, consider using some sort of naming convention with the same named report i.e. rpt_MainReport associated with rpt_MainReport_sub.
In most cases, you will need to modify the design of any wizard generated report as this tool is quite basic in order to get achieve that rich look and feel.
Take a look at my eBook offer on How to Build Access Database Reports.
Tags: Access 2007, building access reports, creating reports and sub reports, how to create access reports, using ms access report wizard