Transcript BS1904w9

Computer Application for Business (9)

Last Week: Databases part 2

This week:
Thank you to the 146 people
who submitted assignment
» The Relational Model
files via Learning Network
» Practical:
– Completing Garages tables and Queries
– Final assignment workshop
– Not everyone did Seminars database with multiple tables
»
»
»
»
Practical: Using Microsoft Access as a relational database
More uses of Databases
Mail Merge introduction
Practical: Putting Word and your database together
BS1904 Week 9
1
The Assignment

I picked up your files automatically, but
» A few people forgot to put their account in the file name
» Only one of the _BS1904_cover.doc files got saved

It takes 30-45 mins to mark this assignment
» There are 160 to do  about 100 hours
» I’ll not finish before the Christmas vacation

Current plan
» Mark all on-time assignments that are complete with a
cover sheet for me to feed back on
» Sort out those remaining, where I have to go back to the
LN to download the files manually
» Chase students for missing files (so don’t delete yours)
BS1904 Week 9
2
Business Value of Computing


Business is driven by people
The computer rarely creates anything new
»
»
»
»

It can allow you to re-use work already done
Help you manage and store work for reference and reuse
Present information clearly
Simplify decision-taking by providing data and calculation
Repetitive tasks can be automated
» Handling Orders
» Creating bills
» Spotting trends

And information made more timely
» e.g. Web site showing stock position from database
BS1904 Week 9
3
Relational Database Practical


Knight’s example 7 (from p.220) involves linked tables
Problem: organizing seminars for conference delegates
» Delegates’ details are held in a table (import from web)
» Seminar details are held in another table (type it in)
» Each delegate can attend one seminar
(but we hope each seminar will attract many delegates!)
Primary key
Seminars
SeminarRef
SeminarName
From
To
Fee
Delegates
1
M
BS1904 Week 9
DelegateRef
Title
Initials
Surname
Company
Address1...
Phone #
SeminarRef
4
Creating the Tables

Design the Seminars table according to instructions
» Be sure to specify SeminarRef as primary key
» Prime the table with the values given
but make it refer to 2008 so you can omit the year

Build the Delegates table
» Use “Get External Data” to Import the data from
delegates.xls on the web site for week 8
» This already has delegate name split up as advised
(not as in Knight’s text) – needed for the mail-merge later
» Access lets you use delegateRef as a primary key

Modify table design to match the hand-out
» Ensure SeminarRef format is compatible between tables
BS1904 Week 9
5
Doing single-table Queries
Use the Query Wizard to:

List the delegates who are attending seminar S03,
showing full name and Company,
and sorting them by phone number

List the titles of the seminars that start on July 26th
in order of seminar reference
BS1904 Week 9
6
Joining the Tables


Use the Relationships tool to link the tables using
SeminarRef
What happens when you “enforce referential integrity”?
» The system checks that every seminar referred to in the
delegates table matches an entry in the seminar table
» So you can’t do this before you fill in the seminar details
» Or if you mismatch “O” and “0”!

Produce queries using fields from both tables
» The Query Wizard will help you do this
» Who is attending a seminar starting on July 26?
Show name and address, and sort by Company
BS1904 Week 9
7
Creating Automatic Mailings


Most companies want to communicate with customers
Best value is to target the communications:
» Offer iPod accessories to those who’ve bought iPods
» Don’t offer ride-on lawnmowers to flat-dwellers
» Go for repeat business where appropriate

Databases let you create lists of target customers
» Then you can use the fields inside to write to them

The letters you generate will be personal...
» ...and will be properly addressed
» Nobody likes things starting “Dear Sir or Madam”

We need to use more than one package to do this
BS1904 Week 9
8
Integrating Packages


For one-off tasks, like writing a letter,
a single package is sufficient
For decision-support, you often need to combine:
» data,
» computation, and
» display




The data may be in a spread-sheet or database
Spreadsheets are good for calculation and generating
graphics
Word-processors are good for laying out reports
Presentation graphics are better in front of an audience
BS1904 Week 9
9
Mail Merge

A common process that you can automate
» For example, sending a form-letter to all your customers
» Integrates word-processing with data retrieval

Works by creating a “Master document”
» Contains all the words and layout common to all letters
» and “place holder” fields for plugging in variable data

Microsoft Word will take data from various sources,
usually in the form of a table:
» Most often from a database or spreadsheet
» Sometimes from a table in a word processor document
» We’ll use a database to extend our experience of Access
BS1904 Week 9
10
Master Document




Word provides extensive help for building this
Best if you already have the source of variable data
Start with a document based on an appropriate template
Use Tools/Letters and Mailing to get Mail Merge wizard
» Step 3 is where you select the data source
» Often this will be a Query combining multiple tables,
like the “people attending seminars” one you created
» Warning: Word XP always looks in “My Data Sources”
– Doesn’t look in the last place you got data from
– You can’t alter this in Tools/Options/File Locations
– If you have several merges to do with the same data,
it may be best to move your database there
BS1904 Week 9
11
Step 4: Write the document


Type the common text
Use “More items” to plug in external data
» Avoid “Address block” unless all addresses are in USA
» More items lets you select individual fields to insert
» You can’t flip between inserting fields and editing the
document, so you may want to insert (say) the whole
address, then add the spacing and new-line marks later

Try the button on the Mail Merge toolbar to preview
the merged result
» You can move through the data checking each letter

Once you’ve reviewed the results, you could print,
but don’t bother doing so in this exercise
BS1904 Week 9
12
Mail Merge Database Practical


Start with the conference database you created
Generate a letter to each delegate confirming the
seminar chosen
» (see BS1904w9.doc for problem description)



If you didn’t import my data, you may need to extend the
database first, restructuring names to avoid nasties like
“Dear Marghanita Laski” (she’d bin your letter unread)
Create a query that contains fields from both tables,
joined by the SeminarRef value
Then create a suitable master document
» Test it out for layout and validity,
» can merge into a file to check that all letters get created
BS1904 Week 9
13