LSP 121

1. LSP 121 Access Forms and Reports

2. Access Forms

3. 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)

4. Forms Continued • Now go back into Design View to edit the form • Resize windows • Move fields around • Many more properties / controls available

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

11. 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

12. Access Reports

13. 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)

14. 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)

15. Creating a Report • Use the Wizard to create a report • Here are the first questions:

16. Creating a Report • Do you want to group items in your report? For example, do you want to group by job title?

17. Creating a Report • Or maybe you want to group them by city?

18. Creating a Report • For the remaining fields, do you want them in any particular order?

19. Creating a Report • Finally, do you want a total of all salaries? • Click on Summary Options and get this:

20. Creating a Report • What kind of report layout would you like?

21. Creating a Report • Finally, you may have to go into Design View to “clean up” the report

23. 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?