Transcript Database forms, reports and switchboard

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