LSP 121 Access Forms and Reports
Access Forms
Displaying Data – The Form • One way to start a form is to use the Form Wizard • Let’s create a form for our Real Estate database, for the Listings table (we’ll do a little bit of Activity 4 but not all of it)
Forms Continued • Now go back into Design View to edit the form • Resize windows • Move fields around • Many more properties / controls available
Toolbox Basic Controls • Label controls – headings, labels, captions, instructions • Text box controls – data is displayed or entered here • Toggle buttons, option / radio buttons, check boxes • Option group – contains multiple toggle buttons
Toolbox Basic Controls • List box – a pull down menu which is always down • Combo box – a pull down menu which you must pull down, and also lets you add an option that is not on the list • When working with forms, notice Table button (or Field List), Toolbox button, and Wizard button in Toolbox
Sample Form • Let’s try another example • Create a form using Name text 50, Married Yes/No, Employed Yes/No, Home Owner Yes/No, City text 50, and State text 2 • Make Name a text box, Married a toggle button, Employed a check box, Home Owner an option button, City a combo box, and State a list box
Sample Form • Click on Field List button. If you can’t click on this, click on Properties and set Record Source to appropriate table • Click on Toolbox and make sure Wizard button is pressed • Click on ab|, then click and drag on Name field • Click on Toggle button, then click and drag on Married field • Now put a caption or a picture in the toggle button using Properties
Properties • Pretty much everything in Access has a list of properties associated with it • To display properties, you can click on View Properties from the menu bar • Or you can right click on an item • Let’s look at some properties
Creating a Form Using Multiple Tables • Create form for main table • On toolbox, click on subform button (make sure the Wizard button is pressed) • Answer the wizard’s questions
Forms Continued • Note – if you change the data on the form, you are changing it in the database! • You can use the form for entering new data • You can create a form for a query and if you enter new data on the query form, it changes the data in the table! • Let’s stop here and try our activity
Access Reports
Parts of a Report • A report has the following parts: • Report header (one per report) • Page header (one per page) • Detail (each line of information) • Page footer (one per page) • Report footer (one per report)
ABC Monthly Report (page header) Employee ID Expense Job Title (detail section) Jones 123 $134.40 Engineer Smith 234 $333.22 Programmer Zygote 345 $123.33 Analyst April 19, 2007 (page footer)
Creating a Report • Use the Wizard to create a report • Here are the first questions:
Creating a Report • Do you want to group items in your report? For example, do you want to group by job title?
Creating a Report • Or maybe you want to group them by city?
Creating a Report • For the remaining fields, do you want them in any particular order?
Creating a Report • Finally, do you want a total of all salaries? • Click on Summary Options and get this:
Creating a Report • What kind of report layout would you like?
Creating a Report • Finally, you may have to go into Design View to “clean up” the report
Let’s Try an Example • Using the Real Estate database, create a report which uses the fields from the Listings table and includes the Listing Number, Address, City, Frame Type and Taxes. Group by City. Total the Taxes. • Let’s say the report is done but now you want to add the average of all taxes to the report footer? How do we do that?
