Microsoft Access 2007 tutorial:master detail report

Master Detail Report

II.4. Master Detail Report

Master Detail report is the way to create the main report including sub report. Creating Master Detail report is really important when you want to see the categories of items and their items. Creating Master Detail report is similar to the way of creating Master Detail form when you use Subform/Subreport control in creating. However, you can create the Master Detail report in many ways that will be described below.

For example, we have a Master Detail form of product by category as figure below:

Microsoft Access 2007 Subform/Subreport

You may know how to create this form. If you don't know how do it, go back to how to create the Master Detail form section.

You have to drag and drop a command button control on the form and set the caption of this command button as PrintReport. For the name of the command button, you name it or not is up to you because on the form have only one command button.

The process of the form:

- If you want to see each category contains what product(show in the list), you can type the category id in the text box of category id( the first text box on the form above).

- For instance, you type Category one(Cate001) when click the Print Report command button, the Master Detail report of product by category will appear. The Master Detail report displays only the products that contain in Cate001.

Microsoft Access 2007 Master Detail Report

To create the Master Detail report, there are three ways you can do.

II.4. 1. Using Subform/Subreport control
- Create the main report in Report Design

Microsoft Access 2007 Design View Report

- Drag and drop the Subform/Subreport control on the main report

The SubReport Wizard dialog will appear

Microsoft Access 2007 SubReport Wizard

This dialog form allows you to choose any the data resources that you want to use in creating a sub report. The data resource that you can use come from the existing tables or queries, the existing report or form. For instance, you pull data from the Product table.

- Click Next (Don't select any objects in the list. If so the dialog form will select Microsoft Access 2007 Existing Report/Form)

- Click the TblProduct (Product Table) in Combo Box control

- ClickMicrosoft Access 2007 Select one field icon or Microsoft Access 2007 Select all field icon to select the fields from the table

Microsoft Access 2007 SubReport Wizard

In the figure above, all fields are selected except the CategoryID field. This field doesn't need to be taken to display in the sub report because the main report contains it already.

- Click Next

The continued SubReport Wizard dialogappear(I name this dialog as continued SubReport Wizard)

Microsoft Access 2007 Sub Report Wizard

The above continued SubReport Wizard dialog asks you to define which fields of the sub report link to the main report's field. You can define the linked fields of the sub report to the main report by yourself or choose from the defined list. For example, you select Choose from a list option.

- Click Next--> Finish

II.4.2. Using the direct table
This way is not far different from the first way:

- Create the main report in Report Design

- Drag and drop the Product table on the main report

The continued SubReport Wizard dialog appear

Microsoft Access 2007 Sub Report Wizard

For example, you click Choose from a list.

- Click Next --> Finish

II.4.3. Using the report wizard main report
In this way, you must create the main report in Report Wizard. But for the sub report, you can create it in Report Wizard or Report Design. If you create the main report and the sub report in Report Design, you will get the incorrect Master Detail report as figure below:

Microsoft Access 2007 Disqualify Master Detail Report

The Master Detail report above is incorrect because Cate001(first category ) and Cate002 (second category)contain all categories of products. Cate001 is soft drink that contains the drink products without alcohol and Cate002 is beer that contains the drink products too but with alcohol.

To create the Master Detail report third way:

- Create the main report in Report Design

- Drag and drop the sub report that you created in Report Wizard on the main report

Microsoft Access 2007 Master Detail Report

- Click View to see the result

Microsoft Access 2007 Master Detail Report Complete

Now, you created the Master Detail report already. Then you have to attach the code to command button as shown below:

Private Sub Command5_Click()

DoCmd.OpenReport "RptProductByCategory", acViewReport, , "CategoryID='" & txtCategoryID & "'"

End Sub


seo plugin comment

 seo plugin

Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword....wait there's even more Now what if i told you there was a simple Wordpress plugin that does all the On-Page SEO, and automatically for you? That's right AUTOMATICALLY, just watch this 4minute video for more information at. <a href="">Seo Plugin</a>
seo plugin


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.