1) Start Access and Open Your ECO DB

CS 105 Integration Assignment

1) **Start Access and open your ECO DB.

a) Create a single Query (multiple criteria) that will find EVERY record that satisfies ANY of the following criteria (that means you do NOT get to pick one of the criteria. You must USE all of them):

i) The Balance is greater than 0 and less than 200

ii) Or the Sales Rep Number is 45.

iii) Or the Sales Rep Number is 44 and the balance is greater than 300

b) Sort the results in ascending order by Sales Rep Number. Your results must include: Customer Name, Customer Number, Street, City, State, Postal Code and Balance from the Customer Table and First Name, Last Name and Sales Rep Number from the Sales Rep Table. You will also need Discount Value, a calculated field in the query, whose value is 0.20 * the Balance value.

c) Save and name this query "Integration XX" where "xx" is your initials. Use the special print instructions to print the Design view, so we can see the query rules you created and all the fields you included. Print in landscape. It might take 2 pages. Run the query and print the resulting table in normal view. Close Access.

2) Open Word for a new blank document. Click on Mailings/Start Mail Merge/Letters. Create the text part of the memo exactly as shown below, including formatting, but without the field names (the border is NOT part of the memo). Just leave room for them. Be sure to enter your own name for "student name". The headings above the Balance & Discount Value at the bottom must be bold. Remember that you cannot just type the names of the fields. In the next step, you will insert the actual field names from your query, as in the image below. Boldface all fields, as shown.

3) With the memo still open, open any Excel file that has a graph. If you don't have one, make one. Insert the graph into the memo where shown in the sample below. You do not have to put the [ ] symbols around it. ANY graph from ANY spreadsheet is OK. You may NOT insert a GIF or JPG image file.

4) **Now you can insert the required fields for the letter.

i) For “Select Document type”: select “letters” (SKIP THIS STEP if this is the first time for doing this lab)

ii) For “Select Starting Document”: select “Use current document” (since the memo already exists, from step 2 above)
(Skip this step too, if this is the 1st time doing this lab)

iii) Click “Select recipients”: choose “Use an existing list” (tells Word that you want to use an existing DB) and browse to locate your DB and select it, then select the "Integration xx" query you created in step 1 above.

iv) Click inside your document where you wish a field to be placed, then click on “insert merge field" and select the desired field. Repeat until all desired fields are inserted.

v) To get the currency symbol and trailing decimals in the Balance and Discount numbers, do these 3 steps:

(1) Move your cursor to the Balance field. Press Alt-F9 (Press & hold the Alt key, then press & hold F9 with Alt. The F9 key is at the top of the keyboard). This will make the formatting codes appear.

(2) Move your cursor to the end of the Balance field (just left of the right-curly-brace) and type:
\# $##0.00
(that’s a backward slash in front and 3 #’s total). Be sure to put EXACTLY one space before the $.

(3) Do the same as (2) for the Discount field, but use the following format code instead:
\# $#0.00
Be sure to put EXACTLY one space before the $ and only ONE # sign after the $. NO OTHER SPACES!!!

(4) Press Alt-F9 again (the codes will disappear.)

vi) Note that you are NOW looking at your boilerplate. Print this ONE page, exactly as it is, (do not do a merge) so we can see your fieldnames, using the regular print command.

5) Preview the letters using the “preview results” button on the ribbon. If you see a mistake, click on the "Preview Results" button again and you will get back to your original boilerplate.

6) Select "Finish & merge/Print Documents/All" on the ribbon and print all the form letters. Label them as step 6.

Hand in: your query results and QBE grid from step 1, printed boilerplate with graph (step 5) and all the memos generated by the merge operation (step 6). There should be 1 memo for each line in your query results table.

[ Insert your graph here ]