LSP 121 Access Forms Reports and Switchboard Access

LSP 121 Access Forms, Reports, and Switchboard

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 our Activity 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 Add Existing Fields button. If you can’t click on this, click on Properties and set Record Source to appropriate table • Make sure Use Control Wizards button is pressed • Click on ab| (Text Box), 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 Jones Smith Zygote Job Title Engineer Programmer Analyst April 19, 2007 ID 123 234 345 Expense $134. 40 $333. 22 $123. 33 (page footer) (detail section)

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? =avg([Taxes])

The Switchboard

The Switchboard • The Switchboard pulls it all together • After defining and populating the database, creating the forms and reports, you can roll it all into one nice package to give to your client (user)

Creating a Switchboard Click on File tab (upper left) and then Switchboard Manager (far right)

Creating a Switchboard • Then click on the Yes button

Creating a Switchboard • Then click on Edit

Creating a Switchboard • To add buttons to the Switchboard, click on New

Creating a Switchboard • Then enter the name you want to appear on the Switchboard, what the Switchboard operation is, and where the form/ or report is coming from. Note: queries have to first be turned into reports. • Let’s try an example. Open Real Estate database and make a simple form, a simple report, and a report based upon a query. • How do you make it so the switchboard is the first thing to open when a user runs Access?

Switchboard as Default • Click on the File tab in the upper-left corner • Click on Options in bottom of the menu on the left side of the screen • Click on Current Database on left margin • Under the Display Form option, select Switchboard from the drop-down list • Now when someone opens this database, the switchboard will automatically pop up

Do You Like Databases? • Data Analysis and Data Mining Minor in CDM – – – IT 240 Introduction to Desktop Databases IT 223 Data Analysis CSC 324 Data Analysis and Statistical Software II CSC 367 Introduction to Data Mining CSC 334 Advanced Data Analysis 2 CDM Electives