http://www.cse.msu.edu/~cse103

1
CSE 103

• 103 studentsDo not log in yet. Computers are
  SLOW today! Review days 13 and 15 in the
  textbook.
• OthersPlease save your work and log out. Class
  begins at 1020.

2
Mail Merge

• What is a main document?
• What is a data source?
• What are merge fields?
• What is the output of the mail merge operation?

3
Mail Merge
Main Document
Data Source
Processing
Merged Document
4
Mail Merge Exercise

• Copy the files Customer Gift.doc and CustList.doc
  from the course Day 04 AFS space to your personal
  AFS space 
• Follow most of the directions in the text
  starting on page 13-4
• When you get to the bottom, do NOT create a new
  data source. Choose to OPEN the CustList.doc file
• Then skip to 13-10 (stop at bottom of 13-12)
• Partners should check each others work

5
Mail Merge Steps

• 1. Open the main document, select Mail Merge from
  the Tools menu, then Create your main document,
  and Form Letters.
• 2. Set up the data source.
• Choose Open Data Source, rather than Create Data
  Source, to use an existing file.
• 3. Add merge fields via Edit Main Document.
• 4. Merge the documents, then save this merged
  document in a new separate file.

6
Catalogs

• Can be created similarly to form letters
• No page breaks in the merged document
• Every piece of text in the main document is
  repeated for each record in the data source
• Non-repeated items must be typed into a separate
  document, then copied and pasted with the catalog
  AFTER the merge.
• See, for example, MusicIntro.doc in the Day 04
  course AFS space

7
1st Music Catalog

• Use the file BeatlesAlbums.doc (copied from the
  Day 04 AFS space) as the data source for your
  catalog
• Use Words HELP on how to make a catalog
• Each catalog entry should include
• Album title, year, number of tracks, and number
  of discs
• Save main document MusicCatalogMain.doc
• Do the merge!
• After you have created the catalog, edit (do not
  merge) the form letter MusicIntro.doc so that it
  includes the catalog, and save it as a new main
  document.
• Goal a new main document so we can send form
  letters, each of which has a copy of our catalog
  (but we wont actually do the 2nd merge that
  would be needed)

8
2nd Music Catalog

• Use the musicdemo01.mdb file from Day 1
• Consult Words HELP to learn how to use an Access
  db as a data source (construct/save query in
  Access)
• Merge with MusicCatalogMain from before to
  produce a catalog that contains all of the They
  Might Be Giants albums produced between 1976 and
  2001 listed in chronological order (hint use SQL
  BETWEEN)
• Query has Title, Discs, Tracks, Year
• TMBG is artist ID 44
• You should have 7 records

9
Debrief Catalog Exercise

• Why can't we just copy the results of the query
  into Word and do the mail merge using the
  resulting Word document? 
• What are the advantages and disadvantages of
  pasting the data into Word to make the data
  source document?
• How did you restrict the albums to be included in
  the catalog?

10
Updated catalog

• Now produce a catalog ordered by album title
  rather than chronologically
• What changes are necessary to effect this change?

11
Calculated fields

• Calculated fields are easiest to create when
  working in SQL view.
• Setup as much as possible in design view, then
  switch
• We can use parentheses for ordering and the 4
  basic operators ( - /) with field names and
  constants. For example, to compute the total cost
  of of a particular item with 6 sales tax
• SELECT Description, 1.06Price FROM tbl_Items

12
Aliases with Calculated Fields

• Calculated Fields in Access default to Expr1,
  Expr2, etc. when displayed
• What is an SQL alias and how can we use it with
  calculated fields?
• What SQL keyword is necessary in order to
  designate an alias?

13
Aliases with Calculated FieldsSQL AS

• SQL Keyword AS
• ExampleSELECT Description, 1.06Price AS
  TotalCost FROM tbl_Items
• Note
• Usually cant reuse field aliases. MUST reuse
  table aliases (including the SELECT line, even
  though the table alias is in the FROM line!)

14
Calculated Fields Exercise

• Construct queries for MyCompany scenarios
• Copy the database from the Day04 folder to your
  personal space
• Create a listing that includes the name of all
  items and the total price with 6 MI state sales
  tax. (qry_day04_salesTax) 25 Office Chair,
  102.82
• To ship items, you need the girth the distance
  around the object
• Compute the girth as twice the (depth width)
  for each product
• List the product name and number, as well as the
  girth (in a column labelled Girth), for all
  products with a girth over 84 inches.
  (qry_day04_girth) 18 98209, 2 Drawer Cabinet,
  136
• A customer wants to purchase 4 of every item you
  make in White
• For each qualifying product, display the product
  number, the current quantity and the quantity
  left after selling one such suite.
• Sort the list so that the remaining quantities
  appear ordered from least to most.
  (qry_day04_white) 4 Metal Desk, 15, 11
• Is there enough stock on hand?

15
Online Bridge Tasks

• Same system as CSE 101
• First BT is in-class on Day 5
• Your Lead and Assistant will not be in your
  classroom that day
• Another Lead will proctor your BT
• You must have a Picture ID to take BT
• Each students BT is unique
• BTs are given with special software

16
BT Authentication

• During regular BTs, the Proctor must authenticate
  you at this screen.
• You must have a picture ID to be authorized

17
Handin Program

• BTs require you to use this program to hand in
  your BT files electronically
• You must hand in
• All files specified in the BT
• Files from the correct folder specified in the BT
• If you do not use the Handin Program correctly or
  fail to hand in all required BT files from the
  correct folders, you will automatically fail the
  BT. No exceptions.

18
Practice

• Read How to Use Handin link on todays CSE 103
  classwork Web page
• Start Bridge Task Software and Practice with
  Sample BT are in the Day 4 folder of the CSE 103
  AFS space

19
Bridge Task 1.0

• INDIVIDUAL, not group work
• Use a computer to solve problems
• Demonstrate mastery of material
• Synthesize concepts to solve problems
• Retake BT if you don't pass (next week after BT)
• No penalty for multiple attempts, but there is a
  maximum of 12 BT opportunities
• If you fall behind scheduled BTs (see calendar)
• You can take make-up BTs to catch up
• You may run out of time at the end of the
  semester
• See the link Rules for Student Conduct During
  Bridge Tasks on todays Web page

20
Bridge Task 1.0

• Covers all the material from Days 1-4
• including todays in-class work and homework
• Be sure homework is all done
• Make sure you have copies of the databases we
  have used in class and homework
• Bring any books, notes, etc. you feel might be
  helpful
• You will use Handin on the BT