http://cse103 u:/msu/course/cse/103 day 04, slide 1 cse 103 103 students: do not log in yet....

http://www.cse.msu.edu/~cse103http://www.cse.msu.edu/~cse103U:/msu/course/cse/103U:/msu/course/cse/103 Day 04, Slide Day 04, Slide 11

CSE 103CSE 103

103 students:103 students:Do not log in yet. Computers are SLOW Do not log in yet. Computers are SLOW today! Review days 13 and 15 in the today! Review days 13 and 15 in the textbook.textbook.

Others:Others:Please save your work and log out. Class Please save your work and log out. Class begins at 10:20.begins at 10:20.


Mail MergeMail MergeWhat is a main document?What is a main document?

What is a data source?What is a data source?

What are merge fields?What are merge fields?

What is the output of the mail merge What is the output of the mail merge operation?operation?


Mail MergeMail MergeMain Document Data Source

Processing

Merged Document


Mail Merge ExerciseMail Merge ExerciseCopy the filesCopy the files Customer Gift.doc Customer Gift.doc andand CustList.doc CustList.doc from the course Day 04 AFS from the course Day 04 AFS space to your personal AFS space space to your personal AFS space Follow most of the directions in the text starting Follow most of the directions in the text starting on page on page 13-4:13-4:– When you getWhen you get to the bottom, do NOT create a new to the bottom, do NOT create a new

data source. Choose to OPEN the CustList.doc filedata source. Choose to OPEN the CustList.doc file– Then skip to 13-10 (stop at bottom of 13-12)Then skip to 13-10 (stop at bottom of 13-12)

Partners should check each other’s work Partners should check each other’s work


Mail Merge StepsMail Merge Steps1. Open the 1. Open the mainmain document, select document, select Mail MergeMail Merge

from the from the ToolsTools menu, then menu, then CreateCreate your main your main document, and document, and Form LettersForm Letters..

2. Set up the data source.2. Set up the data source.– Choose Choose Open Data SourceOpen Data Source,, rather than Create Data rather than Create Data

Source, to use an existing file.Source, to use an existing file.

3. 3. Add merge fields via Add merge fields via Edit Main DocumentEdit Main Document. .

4. 4. MergeMerge the documents, then save this merged the documents, then save this merged document in a new separate file.document in a new separate file.


CatalogsCatalogsCan be created similarly to form lettersCan be created similarly to form letters

No page breaks in the merged documentNo page breaks in the merged document

Every piece of text in the main document is Every piece of text in the main document is repeated for each record in the data sourcerepeated for each record in the data source

Non-repeated items must be typed into a Non-repeated items must be typed into a separate document, then copied and pasted separate document, then copied and pasted with the catalog AFTER the merge.with the catalog AFTER the merge.– See, for example, See, for example, MusicIntro.docMusicIntro.doc in the Day 04 in the Day 04

course AFS spacecourse AFS space


11stst Music Catalog Music CatalogUse the file Use the file BeatlesAlbums.docBeatlesAlbums.doc (copied(copied from the Day 04 from the Day 04 AFS space) as the data source for your catalogAFS space) as the data source for your catalogUse Word’s HELP on how to make a catalogUse Word’s HELP on how to make a catalogEach catalog entry should includeEach catalog entry should include– Album title, year, number of tracks, and number of discsAlbum title, year, number of tracks, and number of discs

Save main document Save main document MusicCatalogMain.docMusicCatalogMain.docDo the merge!Do the merge!After you have created the catalog, After you have created the catalog, editedit (do not merge) (do not merge) the form letter the form letter MusicIntro.docMusicIntro.doc so that it so that it includesincludes the the catalog, and save it as a new main document.catalog, and save it as a new main document.– Goal: a new main document so we can send form letters, each of 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 2which has a copy of our catalog (but we won’t actually do the 2ndnd merge that would be needed)merge that would be needed)


22ndnd Music Catalog Music CatalogUse the Use the musicdemo01.mdbmusicdemo01.mdb file from Day 1 file from Day 1– Consult Word’s HELP to learn how to use an Access Consult Word’s HELP to learn how to use an Access

db as a data source (construct/save query in Access)db as a data source (construct/save query in Access)

MergeMerge with with MusicCatalogMainMusicCatalogMain from before to from before to produce a catalog that contains all of the They produce a catalog that contains all of the They Might Be Giants albums produced between 1976 Might Be Giants albums produced between 1976 and 2001 listed in chronological order (hint: use and 2001 listed in chronological order (hint: use SQL SQL BETWEENBETWEEN))– Query has: Title, #Discs, #Tracks, YearQuery has: Title, #Discs, #Tracks, Year– TMBG is artist ID 44TMBG is artist ID 44

You should have 7 recordsYou should have 7 records


Debrief Catalog ExerciseDebrief Catalog Exercise

Why can't we just copy the results of the Why can't we just copy the results of the query into Word and do the mail merge query into Word and do the mail merge using the resulting Word document? using the resulting Word document?

What are the advantages and What are the advantages and disadvantages of pasting the data into disadvantages of pasting the data into Word to make the data source document?Word to make the data source document?

How did you restrict the albums to be How did you restrict the albums to be included in the catalog?included in the catalog?


Updated catalogUpdated catalog

Now produce a catalog ordered by album Now produce a catalog ordered by album title rather than chronologicallytitle rather than chronologically

What changes are necessary to effect What changes are necessary to effect this change?this change?


Calculated fieldsCalculated fieldsCalculated fields are easiest to create when Calculated fields are easiest to create when working in SQL view.working in SQL view.– Setup as much as possible in design view, then Setup as much as possible in design view, then

switchswitch

We can use parentheses for ordering and the 4 We can use parentheses for ordering and the 4 basic operators (+ - * /) with field names and basic operators (+ - * /) with field names and constants. For example, to compute the total constants. For example, to compute the total cost of of a particular item with 6% sales tax:cost of of a particular item with 6% sales tax:– SELECTSELECT Description, 1.06*Price Description, 1.06*Price FROM FROM tbl_Itemstbl_Items


Aliases with Calculated FieldsAliases with Calculated Fields

Calculated Fields in Access default to Calculated Fields in Access default to Expr1, Expr2, etc. when displayedExpr1, Expr2, etc. when displayed

What is an SQL What is an SQL aliasalias and how can we and how can we use it with calculated fields?use it with calculated fields?

What SQL keyword is necessary in order What SQL keyword is necessary in order to designate an alias?to designate an alias?


Aliases with Calculated FieldsAliases with Calculated Fields::SQL SQL ASAS

SQL Keyword: ASSQL Keyword: AS– Example:Example:

SELECTSELECT Description, 1.06*Price Description, 1.06*Price AS AS TotalCostTotalCost FROM FROM tbl_Itemstbl_Items

Note: Note: – Usually can’t reuse field aliases. Usually can’t reuse field aliases. MUSTMUST reuse reuse

table aliases (including the SELECT line, even table aliases (including the SELECT line, even though the table alias is in the FROM line!)though the table alias is in the FROM line!)


Calculated Fields ExerciseCalculated Fields ExerciseConstruct queries for MyCompany scenarios:Construct queries for MyCompany scenarios:

Copy the database from the Day04 folder to your personal spaceCopy the database from the Day04 folder to your personal spaceCreate a listing that includes the name of all items and the total price Create a listing that includes the name of all items and the total price with 6% MI state sales tax. (with 6% MI state sales tax. (qry_day04_salesTaxqry_day04_salesTax) ) [25] [Office Chair, [25] [Office Chair, 102.82]102.82]To ship items, you need the girth : the distance around the objectTo ship items, you need the girth : the distance around the object– Compute the girth as twice the (depth + width) for each productCompute the girth as twice the (depth + width) for each product– List the product name and number, as well as the girth (in a column 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. labelled “Girth”), for all products with a girth over 84 inches. ((qry_day04_girthqry_day04_girth) ) [18] [98209, 2 Drawer Cabinet, 136][18] [98209, 2 Drawer Cabinet, 136]

A customer wants to purchase 4 of every item you make in WhiteA customer wants to purchase 4 of every item you make in White– For each qualifying product, display the product number, the current For each qualifying product, display the product number, the current

quantity and the quantity left after selling one such suite.quantity and the quantity left after selling one such suite.– Sort the list so that the remaining quantities appear ordered from least to Sort the list so that the remaining quantities appear ordered from least to

most. (most. (qry_day04_whiteqry_day04_white) ) [4] [Metal Desk, 15, 11][4] [Metal Desk, 15, 11]– Is there enough stock on hand?Is there enough stock on hand?


Online Bridge TasksOnline Bridge Tasks

Same system as CSE 101Same system as CSE 101First BT is in-class on First BT is in-class on Day 5Day 5Your Lead and Assistant will not be in your Your Lead and Assistant will not be in your classroom that dayclassroom that dayAnother Lead will proctor your BTAnother Lead will proctor your BTYou must have a Picture ID to take BTYou must have a Picture ID to take BTEach student’s BT is uniqueEach student’s BT is uniqueBTs are given with special softwareBTs are given with special software


BT AuthenticationBT AuthenticationDuring regular BTs, the Proctor must During regular BTs, the Proctor must authenticate you at this screen.authenticate you at this screen.

You You mustmust have a picture ID have a picture ID to be to be authorizedauthorized


Handin ProgramHandin ProgramBTs require you to use this program to BTs require you to use this program to hand in your BT files electronicallyhand in your BT files electronicallyYou must hand in:You must hand in:– All files specified in the BTAll files specified in the BT– Files from the Files from the correctcorrect folder specified in the BT folder specified in the BT

If you do not use the Handin Program If you do not use the Handin Program correctly or fail to hand in correctly or fail to hand in allall required BT required BT files from the correct folders, you will files from the correct folders, you will automatically fail the BT. automatically fail the BT. No exceptionsNo exceptions..


PracticePractice

Read Read How to Use HandinHow to Use Handin link on today’s link on today’s CSE 103 classwork Web pageCSE 103 classwork Web page

Start Bridge Task SoftwareStart Bridge Task Software and and Practice Practice with Sample BTwith Sample BT are in the Day 4 folder are in the Day 4 folder of the CSE 103 AFS spaceof the CSE 103 AFS space


Bridge Task 1.0Bridge Task 1.0INDIVIDUAL, not group work INDIVIDUAL, not group work Use a computer to solve problemsUse a computer to solve problemsDemonstrate mastery of materialDemonstrate mastery of materialSynthesize concepts to solve problemsSynthesize concepts to solve problemsRetake BT if you don't pass (next week after BT)Retake BT if you don't pass (next week after BT)No penalty for multiple attempts, but there is a No penalty for multiple attempts, but there is a maximum of maximum of 1212 BT opportunitiesBT opportunitiesIf you fall behind scheduled BTs (see calendar)If you fall behind scheduled BTs (see calendar)– You can take make-up BTs to catch upYou can take make-up BTs to catch up– You may run out of time at the end of the semesterYou may run out of time at the end of the semester

See the link See the link Rules for Student Conduct During Rules for Student Conduct During Bridge TasksBridge Tasks on today’s Web page on today’s Web page


Bridge Task 1.0Bridge Task 1.0Covers all the material from Days 1-4Covers all the material from Days 1-4– including today’s in-class work and including today’s in-class work and

homeworkhomework– Be sure homework is all doneBe sure homework is all done

Make sure you have copies of the Make sure you have copies of the databases we have used in class and databases we have used in class and homeworkhomeworkBring any books, notes, etc. you feel Bring any books, notes, etc. you feel might be helpfulmight be helpfulYou will use You will use HandinHandin on the BT on the BT

http://cse103 u:/msu/course/cse/103 day 04, slide 1 cse 103 103 students: do not log in yet....

Documents