You Can Do It!
Creating a Database for your
CME Program Manager
The Reading Hospital and Medical Center
Director of Education
The Movement Disorder Society
We have nothing to disclose with regard to
l#sec_tables (referenced 11/07/08. Microsoft Access Tutorial . Zicklin
School of Business - Baruch College City University of New York.
Microsoft Access Tutorial. Richard Holowczak Computer
Information Systems Department. Zicklin School of Business,
Baruch College, CUNY
Determine specific outputs such as the annual
report, activity list and disclosure forms that are
to be produced from the system.
Identify both common and unique elements of
these outputs that will form the building blocks
for your system.
Categorize both the common and unique
elements as the basis for developing the tables
within your system.
Describe the next steps for making your system
~ DECISIONS ~
Building a database means that
you first need to make decisions –
many decisions – before you can
actually start the design process
WHY do I need a database?
Eliminates activity files, except ones you
need to produce for your Accreditation
Keeps all the information in one place,
easy to find and make adjustments
Eliminates repetitive completion of forms,
postings, invitation letters, and more
To make things easier!
Identify Your End Result
What do I want to be able to do with the
What kind of information will I need to track?
What kind of reports will I need to produce?
What kind of forms will I need to print out?
Step One: Set the Foundation
Work from the end to the beginning
Identify the desired end result
Determine the main function of the
How will it help you meet the needs of
your CME Program and of the ACCME?
What data is needed to reach these goals?
What do I expect to do with the
Maintain demographic information on each
Maintain essential information on each
Produce activity documentation as
needed, especially those that satisfy CME
What kind of information is needed?
Identify the information you need to keep.
Include everything you can think of that
might be relevant.
Determine the type of reports or
forms do you need to produce
CME Application Financial Summary for
Invitation Letter Individual Activity
Financial Summary for a
Disclosure Form span of time (annual)
Simple Brochure or Flyer Evaluation Summary
Check requests Speaker Evaluation of
Deposit memo CME program
Evaluation Form Needs assessment report
noted from evaluations
Thank you Letter
List of activities for a
span of time
What’s a table?
Tables are the main units of
data storage in Access.
A table is made up of one or more
columns known as fields.
One or more of the fields in the table
may appear in more than one table so
that it builds a relationship between
Step Two: Decide on your Tables
Speaker Activity Classification
Table Table Table
Tables might include:
COMMERCIAL SUPPORT TABLE
COURSE DIRECTOR TABLE
At each table,
there are a
you want to
Step Three: Decide what information
needs to be kept for each table?
TABLE TABLE TABLE
The Matching Game
•Room Capacity •Room Number/Name
•Activity Type •Company website
•Frequent Flyer Number •Date Funds Received
•Activity Date •Course Director
•Requested Amount •Honorarium Amount
•Phone Number •Objectives
•Secretary’s Name •Disclosure information
•Activity Title •Committee approval date
•Number of Credits •Notes
•Sponsorship Type •Contact Person
•Needs Assessment •CV on File
•Amount Received 1. Activity
•LOA on File 2. Speaker
3. Commercial Support
Fields might include:
Activity Title Expenses
Activity Date Commercial Support
Needs Assessment Speaker ID
Objectives Speaker Name
Location of activity
Speaker Name Speaker Disclosure
Speaker Address Information
Speaker phone(s) Hotel
Contact person Accommodations
Emails Travel Arrangements
SSN or TID Brief bio
2 Ways to Build a Table
Wizard – guides you through
the process, recommends names
for tables and fields
Design View – allows you
to create your own fields and
determine their data types
Field Name Data Type Description
ActivityID AutoNumber The Unique identifier for the activity.
Choose the option that automatically assigns a
TopicTitle Text The title of the Activity Up to 255 characters
TopicDate Date/Time The date of the activity
Credits Number Number of credits for this activity
NeedsAssessment Memo Narration Unlimited characters
SpeakerID Number Identified in the Speaker Table
Step Four: Enter Information
Speaker Name Travel Arrangements
Address (Another second Hotel
address field might be needed if
there is a different address for
mailing) Hotel Reservation #
Phone (cell, office, Disclosure information
SSN or TID number
Entering data into a table is just like
entering it into an Excel spreadsheet
Step Five: Setting up a Query
A query enables you to…
extract pieces of information from one or
more tables to develop a report or form
set parameters for your report
e.g., entering a beginning and an ending date
e.g., entering a specific department
Select Simple Query or
Choose the table(s) you
would like to use
Choose the fields that will be included in your report by either
double-clicking on the field or click and drag to the section below.
If you want to produce a document that includes information over
a specific time span you would insert the following formula in the
criteria location in the corresponding field cell:
>=[Enter Start Date:] And <=[Enter End Date:]
Name your Query e.g. Disclosure Query
name your table with the word Table
Name your query with the word Query
When you write your field names keep all the
letters together – don’t split up the words.
Step Six: Building a Report
A Number of ways to build a report/form
From a table
From a query
Using the Report Wizard
Using the Design View
Using the Auto Report, Tabular style
Using the Auto Report, Column style
Using the Chart Wizard
Building a Report using the
Wizard and a Table or Query
The top part of this form indicates the tables
that you have selected to use.
The bottom part of the form shows the fields
you have selected.
Let’s Practice – Disclosure Form
*Commercial Support Table
Query – Disclosure
Report – Disclosure Form
Disclosure – Activity Table
Activity Title (Topic)
Disclosure – Speaker Table
Activity Table Speaker Table
Activity Title (Topic) First Name
Date Middle Initial
Time Last Name
>=[Enter Start Date:] And <=[Enter End Date:]
Disclosure Report (blank form)
Mail Merge with Microsoft® Word
Create form in Access
Handouts for this breakout session
available on ACME website.
Contact your IT depart to see if they
support MS ACCESS.
Review paper forms to identify tables,
fields and queries.
You Can Do It!
Creating a Database for your CME Program