Home / Data

Introduction to database management system Part 1 - 12



Figure 4.23

Step 4 : Save and run the Form, the data entry screen will appear as shown in Figure 4.24.

Maybe you are interested!


Figure 4.24

Example 2: Create Subform using Wizard

Also with the Quanlyhanghoa database as in example 1: The request is to build a data entry form consisting of 3 nested forms as follows:

- Form “ChitietHD” has source data as table ChitietHD

- Form “HoaDon” has source data as HoaDon table and contains child Form “ChitietHD”

- The form "Khach" has the source data as the table "Khach " and contains the child form "HoaDon". The steps are as follows:

Step 1 : Build the Forms “ChitietHD”, “HoaDon” as in Example 1. In the Form “HoaDon”, do not create MaK using Combo Box but using Textbox. Select the display mode for this Form as View Allowed: Single Form .

Step 2 : Open the Form “HoaDon” in Design mode. Select the Subform/Subreport button on the Toolbox, click and drag on the Form to define the rectangular area that will contain the Subform. A dialog box appears and select “ Use an Existing Form ”, select the Subform built in step 1 as “ChitietHD”, click the Next button .

Step 3 : In the next dialog box, select “ Show ChitietHD for each record in HoaDon using SoHD ”, click the Next button. The next dialog box allows you to name the child Form in the Form being built, we name it “ChitietHD”. Click Finish to return to the Form design window.

Step 4 : Edit the size of the child Form if necessary. Record and name the newly designed Form as “HoaDon ”.

Step 5 : Create a Customer Form with the source data being the Customer table . Put the fields of this table in the beginning of the Form.

Step 6 : Select the Subform/Subreport button on the Toolbox, click and drag on the Form to define the rectangular area that will contain the Subform. A dialog box appears and select “ Use an Existing Form ”, select the Subform built in the previous step as “HoaDon”, click the Next button .

Step 7 : In the next dialog box, select “ Show HoaDon for each record in Khach using MaK ”, click the Next button . The next dialog box allows you to name the child Form in the Form being built, we name it “HoaDon”. Click Finish to return to the Form design window. The design window is as shown in Figure 4.25



Figure 4.25

Step 8 : Save and open the Customer Form for the result as shown in figure 4.26


Figure 4.26


CHAPTER 4 EXERCISES

Lesson 1 : Create a data entry form for the Thuchi table in the THUQUY database in the chapter 2 exercises.

Lesson 2 : Create a form to enter data at the same time for 2 tables Danhsach and SoBD_Phach in the THICU database in the chapter 2 exercises.

Lesson 3 : Given the following STUDENTS database:

HOSO table to keep student records including schools:

- MaSV: Text, 10 wide, primary key, save student code.

- Ho: Text, 15 wide, save the student's last name and middle name.

- Name: Text, 6 wide, save the student's name.

- Date/Time: save date of birth

- Gender:Text, 3 wide, save gender as “Male” or “Female”

- Faculty: Text, 20 wide, name of the faculty the student is studying.

- QueQuan: Text, 50 wide, records student's hometown including district and province.

- Address:Text, 50 wide, current residential address of the student

- Phone: Text, 12 wide, student's phone if available

- Giaitich, Daiso, Tinhoc, Triethoc, Anhvan: Number, Double, save the exam scores of the first semester subjects of the first year of students.

The GIADINH table is used to store information about family members who are directly responsible for the student, including the following schools:

- MaSV:Text, 10 wide, student code, used to link to HOSO table.

- Hoten: Yext, 25 wide, save the full name of the student's relatives

- Male: Number, long Integer, save year of birth.

- Surrounding: Text, 12 wide, related to SV.

- Address: Text, width 50.

- Phone: Text, 12 wide, contact phone number if available.

The relationship between the HOSO table and the GIADINH table is 1-n through the MaSV field. Build the Forms as follows:

1. Form 1: Autoform: Columnar to enter data for HOSO table

2. Form 2: Autoform: Tabular to enter data for HOSO table

3. Form 3: Autoform: Datasheet to enter data for HOSO table

4. Form 4: use wizard to create data entry form for two tables HOSO and GIADINH

5. Create a Form to view the records in Query1 in exercise 7, chapter 3.

6. Create a “Question and Answer Form” to search for students in a faculty whose date of birth is greater than or equal to a certain date. The form has 4 controls: use a Combo Box named (CBkhoa) to enter the faculty name, use a Text Box named (tbNgaysinh) to enter the date of birth, use the “Search” command button to run a query, use the “Close Form” command button to close the Form.

Suggestion : Create a query “query Khoa and NgaySinh” from the HOSO table, put the fields MaSV, Ho, Ten, NgaySinh, Khoa into the design grid. The Criteria line of the NgaySinh column enters the formula >=[Forms]![Form hoi dap]![tbNgaySinh] (the formula means the NgaySinh field must be greater than or equal to the value stored in the tbNgaySinh control of the “Form hoi dap” form, “Form hoi dap” is an object of the Forms collection), the Criteria line of the Kho column enters the formula = [Forms]![Form hoi dap]![CBKhoa].

7. Create a DSKhoa data filtering form: allows selecting the faculty name from a Combo Box, the student list appears in the DSkhoa_Subform subform.

Hint : Create a child form first using Design view, open the Form properties dialog box, click the Build button on the Record Source properties line to bring up the Query Build query design window, select the HOSO table, put the fields Ho, Ten, Ngay sinh, Gioitinh, Khoa into the query design grid. In the Criteria line on the Khoa column, enter [CBKhoa] (ie the criteria taken from the combo box).

In the main form DSkhoa, create a combo Box with the Name attribute CBKhoa.

CHAPTER 5: REPORT


Reports are a convenient tool for organizing and printing data from a table or query. Although it is possible to print data from tables, queries, and forms, reports provide a lot of flexibility to control the printing job. Reports do not support interactive controls, but we can include controls in the report to display data such as text boxes, check boxes, etc. Reports can also contain graphic images. Reports also allow you to organize and present data by group, calculate group totals, and provide statistics on data by group.

In Design View mode, the structure of a Report is similar to a Form, consisting of 5 components: Report Header (the first part of the report, printed once on the first page), Page Header (the first part of the page, containing column titles), Detail (where the fields of the table, queries, and records will be printed in this part), Page Footer (the last part of each printed page, containing page numbers), Report Footer (the last part of the report, printed once on the last page, containing summary information of the report). If the Report has groups, there are 2 more parts: group header (printing group title, column title) and group footer (summary statistics about the group, total columns, average columns).

Creating a Report using the Wizard and Design View is exactly the same as creating a Form. The functionality and usage of the Toolbox and Field List remain the same.

1. Create report using Autoreport

Select one of the two automatic report types (available from AutoReport) and press OK.

Generate automatic Tabular reports

In AutoReport: Tabular (row and column reports) fields of a record appear on one row with labels appearing once at the beginning of the report.

To create a report, select the Create tab, select a button in the Reports area.


Figure 5.1: Report creation area

Reports button: Generate automatic reports

Report Design button: create a report by designing it yourself Blank Report button: Blank report

Report Wizard: Create reports using Wizard Labels: Create labels

We consider how to create automatic reports through the following example:

For example : suppose in Quanlyhanghoa database , there is a Customer table , we create a Report for this table.

Step 1 : Click to select the Guest table

Step 2: On the Create tab, click the Report button, shown in Figure 5.2


Figure 5.2: Automatic report generation

Step 3 : Write and name the report R_Khach

If the printed report layout is not satisfactory, we can switch to Design View mode to fix it: change the size and position of controls, reset the font and color, background color, add borders to the report.

2. Create a report using the Report Wizard

Report Wizard helps us quickly create a report from one or more tables, giving us the option to group and sort data. Let's see how to create a Report through the following example:

.

For example : Suppose you need to create a report on the details of each sales invoice, the data is taken from the HoaDon and ChitietHD tables of the Quanlyhanghoa database as in the previous chapter.

Step 1 : On the Create tab, click the Report Wizard button as shown in figure 5.3

A dialog box appears.



Figure 5.3: Report Wizard dialog box

Step 2 : Select the HoaDon table in the Table/Queries section, move the fields SoHD, MaK, NgayHD to the Selected fields frame. Select the second table ChitietHD, move the fields MaH, SLban, Dongia to the Selected fields frame, click the Next button . A dialog box appears as shown in Figure 5.4.


Figure 5.4

Step 3 : Select By HoaDon, click Next

Comment


Agree Privacy Policy *