1) Start Access and Open Your JMS Tech Wizards DB

CS 105 Integration Assignment

1) **Start Access and open your JMS Tech Wizards DB.

a) Create a single Query (multiple criteria) that will find every record that satisfies BOTH of the following criteria:

i) the billed amount is less than 200 or greater than 400 (that is, it is NOT from 200-400).

ii) the Technician number is in the following group (23, 25, 32 ). So you need to find all of them.

b) Sort the results in ascending order by client Name. Your results must include: Client Name, Client Number, Street, City, State, Postal Code and Billed from the Client Table and First Name, Last Name and Technician Number from the Technician Table. You will also need Coupon Value, a calculated field in the query, whose value is 0.02 * the Billed 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 as usual. And close Access.

2) Open Word. 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). Next, where I have field names like <<Name>>, you must insert a real field name from your query. Italicize all fields, as shown. Remember that you cannot just type the names. 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. ANY graph from ANY spreadsheet is OK. You may NOT insert a GIF or JPG image file.

3) **Now you can insert the required fields for the letter. Select “Tools /Letters and Mailings / Mail Merge Wizard” and follow these steps as noted at the bottom of the helper menu that appears at the right of the screen. The step numbers here correspond to the step numbers in the helper menu.

i) For “Select Document type”: select “letters”

ii) For “Select Starting Document”: select “Use current document” (since the memo already exists, from step 2 above)

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

iv) For “Write your letter”: click inside your document where you wish a field to be placed, then click on “more items” in the helper menu and select the desired field. Repeat until all desired fields are inserted.

v) To get the currency symbol and trailing decimals in the Billed and Coupon Value numbers, do this:

(1) Press Alt-F9 (both keys together – F9 is at the top of the keyboard). This will make the formatting codes appear.

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

(3) Do the same as (2) for the Coupon Value field, but use this 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) Preview the letters using the regular “print preview” button on the tool menu at the top. Note that you are NOW looking at your boilerplate. Print this ONE page, so we can see your fieldnames. Now do a “merge preview” to be sure your letters look OK.

vii) Select "complete the merge" in the helper menu and print all the form letters. Label them as step 3.

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