Informatics Database management system - III Petr Suchánek Informatics Outline of the lecture •Form wizard •Form with subforms •Report wizard •Organize report •Macro Form • *https://www.arkware.com/2017/11/14/create-form-form-wizard/ •A form in Access is a database object that you can use to create a user interface for a database application.* •A "bound" form is one that is directly connected to a data source such as a table or query, and can be used to enter, edit, or display data from that data source.* •Alternatively, you can create an "unbound" form that does not link directly to a data source, but which still contains command buttons, labels, or other controls that you need to operate your application.* •You can also add command buttons and other features to a form to automate frequently performed actions.* Form wizard • *https://www.arkware.com/2017/11/14/create-form-form-wizard/ •Here are the steps to creating a form:* ØOn the Create form, under the Forms group, click More Forms and click Form Wizard. ØFollow the directions on the wizard. The directions will have you select which tables and queries you want included on the form. You can take away fields or add fields in this step. When complete, hit Next or Finish. ØHit Finish on the last page of the wizard. A number of results can be generated depending on the options you chose. Form wizard • *https://www.arkware.com/2017/11/14/create-form-form-wizard/ •Benefits of Using the Form Wizard:* •Additional options - Using the wizard opens up new options because you can add new fields and take away ones you don’t need. This added flexibility puts the control in your hands. •Save time - Rather than creating forms from scratch, you can use the Form Wizard and customize it to your liking. This saves time and improves efficiency by allowing you to start working immediately. •User friendly - The Form Wizard is easy to work with. When you pull up the wizard tool, all you need to do is add the fields you want with a double click. • Form wizard • *https://www.webucator.com/how-to/how-create-form-with-the-form-wizard-microsoft-access.cfm •The Form Wizard gives you more control over your results than one-click forms do.* •The wizard lets you make decisions about certain aspects of a form's design and produces a form based on your instructions.* •To create a form based on a single table using the Form Wizard, follow these nine steps.* •On the Create tab in the Forms group, click Form Wizard. The wizard starts.* Form wizard • *https://www.webucator.com/how-to/how-create-form-with-the-form-wizard-microsoft-access.cfm •From the Tables/Queries drop-down list, select the table (or query) to base the form on.* •The fields for the selected table load in the Available Fields list box.* •Move the fields to include on the form from the Available Fields list box to the Selected Fields list box.* •To do so, double-click a field name to move it or highlight the field name and click >. To move all fields at once, click >>. * Form wizard • *https://www.webucator.com/how-to/how-create-form-with-the-form-wizard-microsoft-access.cfm •Select the layout for the form. Your options are "Columnar", "Tabular", "Datasheet", and "Justified".* •Enter a title for the form.* Form wizard • *https://www.webucator.com/how-to/how-create-form-with-the-form-wizard-microsoft-access.cfm •Select an option for the view you want to open the form in. Your options are:* ØOpen the form to view or enter information (opens in Form view). ØModify the form's design (opens in Design view). •Click Finish. The form loads in the view you selected.* Form wizard • •https://support.office.com/en-us/article/create-a-form-by-using-the-form-wizard-2786d31a-7241-4c11 -9747-1ecf88b4d04b •https://www.webucator.com/how-to/how-create-form-with-the-form-wizard-microsoft-access.cfm •https://www.dummies.com/software/microsoft-office/access/how-to-generate-forms-in-access-2016/ •https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_form_in_microsoft _access.cfm Form with subform • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_add_a_subform_to_a_for m_in_access_2016.cfm •In the left Navigation Pane, right-click on the form and select Design View from the contextual menu.* •Locate the Controls group on the Design tab in the Ribbon.* •Click the little down-arrow to expand the list of available form controls.* Form with subform • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_add_a_subform_to_a_for m_in_access_2016.cfm •Click the Subform/Subreport icon to select it.* •Then click in the form, in the location that you'd like the subform to be displayed.* •This launches the SubForm Wizard.* •You have the option of basing your subform on a table or query, or on an existing form.* •In this example, we'll select Use existing Tables and Queries.* •Once you've selected the option, click Next >.* Form with subform • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_add_a_subform_to_a_for m_in_access_2016.cfm Form with subform • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_add_a_subform_to_a_for m_in_access_2016.cfm •If you selected Use existing Tables and Queries, you will now be asked to select the fields to use in the subform.* •Select the table or query from the drop-down list.* •On the left pane, select each field that you need on the subform, then click the little > button to move it across to the right pane.* •You can select fields from more than one table or query. Simply select another table/query from the drop-down when you're done with the first one. •Once done, click Next >.* Form with subform • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_add_a_subform_to_a_for m_in_access_2016.cfm •There needs to be a field that links the main form and its subform. This is typically an ID field or similar.* •If you've previously set up a relationship for the tables, then Access will guess which field to use based on the primary key and foreign key of the relationship.* •At this part of the wizard, Access gives you the opportunity to choose your own field or select another one from the list provided.* •For this example we leave it at the field that Access guessed.* •Once you've selected a field, click Next >.* Form with subform • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_add_a_subform_to_a_for m_in_access_2016.cfm Form with subform • •https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_add_a_subform_to_a_for m_in_access_2016.cfm •https://support.office.com/en-us/article/create-a-form-that-contains-a-subform-a-one-to-many-form- ddf3822f-8aba-49cb-831a-1e74d6f5f06b •https://access-programmers.com/main-form-and-subform-concepts •https://www.microassist.com/software-tips/creating-access-subforms/ • Report • *https://support.office.com/en-us/article/introduction-to-reports-in-access-e0869f59-7536-4d19-8e05 -7158dcd3681c •Reports offer a way to view, format, and summarize the information in your Microsoft Access database.* •For example, you can create a simple report of phone numbers for all your contacts, or a summary report on the total sales across different regions and time periods.* •The design of a report is divided into sections that you can view in the Design view.* •Understanding how each section works can helps you create better reports.* •For example, the section in which you choose to place a calculated control determines how Access calculates the results.* Report wizard • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_create_a_grouped_repor t_using_the_report_wizard_in_access_2016.cfm •A grouped report (also known as a summary report), is a report where one or more fields are used to group the other fields.* •This can be handy if one field has lots of repeating values, as you can display the value once, then display all records that belong to that group.* •Click Report Wizard on the Ribbon (from the Create tab).* Report wizard • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_create_a_grouped_repor t_using_the_report_wizard_in_access_2016.cfm •Access now gives you the opportunity to specify more grouping levels if required.* •In our example, we'll leave it as it is (at one grouping level). •Click Next >.* •You can specify fields to sort the report details by. This sorts the fields within each group (not the grouped fields).* •Once done, click Next >.* Report wizard • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_create_a_grouped_repor t_using_the_report_wizard_in_access_2016.cfm Report wizard • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_create_a_grouped_repor t_using_the_report_wizard_in_access_2016.cfm •Select one of the preset layouts for the report.* •The preview will update when you select an item. Feel free to select each one to see how it affects the layout.* •You can also change the orientation to Landscape if required.* •For our example, we'll leave the report with a Stepped layout and a Portrait orientation.* • Report wizard • *https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_create_a_grouped_repor t_using_the_report_wizard_in_access_2016.cfm •Enter a name for the report.* •You also have the option of either previewing the report or modifying it's design once the wizard has finished creating it.* •For our example, we'll leave it at Preview the report.* •Click Finish to generate the report.* • Report wizard – organize the report • *https://edu.gcfglobal.org/en/access2016/advanced-report-options/1/ •Click Next when you are satisfied with the basic organization of your data.* •If you're not satisfied with the way your data is organized, you can now modify the grouping levels.* •Select a field from the list, and click the right arrow to add it as a new level.* Report wizard – organize the report • *https://edu.gcfglobal.org/en/access2016/advanced-report-options/1/ •If necessary, modify the order of your grouped fields by selecting a field and clicking the up or down Priority arrow to move it up or down a level.* •Once you are satisfied with the organization of your report, click Next.* Report wizard • •https://www.dummies.com/software/microsoft-office/access/how-to-start-the-report-wizard-in-access- 2016/ •https://www.webucator.com/how-to/how-create-report-with-the-report-wizard-microsoft-access.cfm •https://support.office.com/en-us/article/create-a-grouped-or-summary-report-f23301a1-3e0a-4243-900 2-4a23ac0fdbf3 •https://edu.gcfglobal.org/en/access2016/advanced-report-options/1/ • • Macros • •https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/create_a_macro_in_microsof t_access.cfm •https://support.office.com/en-us/article/create-a-user-interface-ui-macro-12590d3b-b326-4207-bfe5- 19234f53f08b •https://database.guide/how-to-create-a-macro-in-access/ •https://www.tutorialspoint.com/ms_access/ms_access_macros.htm • • • The end