1 / 20

CSE 103

CSE 103. 103 students: Do not log in yet. Computers are SLOW today! Review days 13 and 15 in the textbook. Others: Please save your work and log out. Class begins at 10:20. Mail Merge. What is a main document? What is a data source? What are merge fields?

freira
Download Presentation

CSE 103

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSE 103 • 103 students:Do not log in yet. Computers are SLOW today! Review days 13 and 15 in the textbook. • Others:Please save your work and log out. Class begins at 10:20. http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  3. Mail Merge Main Document Data Source Processing Merged Document http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  4. Mail Merge Exercise • Copy the filesCustomer 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 getto 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 other’s work http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  5. Mail Merge Steps 1. Open the main document, select Mail Merge from the Tools menu, then Createyour 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. http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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.docin the Day 04 course AFS space http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  7. 1st Music Catalog • Use the file BeatlesAlbums.doc(copiedfrom the Day 04 AFS space) as the data source for your catalog • Use Word’s 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.docso 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 won’t actually do the 2nd merge that would be needed) http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  8. 2nd Music Catalog • Use the musicdemo01.mdb file from Day 1 • Consult Word’s 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 http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  10. Updated catalog • Now produce a catalog ordered by album title rather than chronologically • What changes are necessary to effect this change? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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.06*Price FROM tbl_Items http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  13. Aliases with Calculated Fields:SQL AS • SQL Keyword: AS • Example:SELECT Description, 1.06*Price AS TotalCostFROM tbl_Items • Note: • Usually can’t reuse field aliases. MUSTreuse table aliases (including the SELECT line, even though the table alias is in the FROM line!) http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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 student’s BT is unique • BTs are given with special software http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  16. BT Authentication • During regular BTs, the Proctor must authenticate you at this screen. • You must have a picture ID to be authorized http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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. http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  18. Practice • Read How to Use Handin link on today’s 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 http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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 12BT 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 today’s Web page http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  20. Bridge Task 1.0 • Covers all the material from Days 1-4 • including today’s 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 http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

More Related