CS 105 Oxford University Museum Gift Shop CS Database Project There should be two parts, one is database from Access, one is word file (also related with e

CS 105 Oxford University Museum Gift Shop CS Database Project There should be two parts, one is database from Access, one is word file (also related with excel work). Use the database I gave you and follow EVERY STEP of instruction to finish the job, and submit all files to me at the end CS 105 Integration Assignment
1) **Start Access and open your Museum Gift Shop DB.
a) Create a single Query (which will have 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. Your query must USE all of them. It is an “or” query):
i) The Wholesale Cost is either 12.35 or 27.99 and the Vendor Code is XX.
ii) Or the Item Number is less than 6185 and the On Hand is less than or equal to 12
b) Sort the results in ascending order by Vendor Code. Your results must include: Item Number, Item Description, Vendor
Code, Vendor Name, from their respective tables. You will also need Discount Value, a calculated field in the query,
whose value is 0.20 * the Wholesale Cost value.
c) Save and name this query “Integration XX”. Run the query and make sure you got correct results. Close Access. Submit
your Database to Blackboard.
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 Wholesale Cost & 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.
a) For “Select Document type”: select “letters” (SKIP THIS STEP if this is the first time for doing this lab)
b) 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)
c) 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.
d) Click inside your document where you wish a field to be placed, then click on “insert merge field” and select the desired
field. Also: insert the Vendor Code field to the right of Vendor Name. Repeat until all desired fields are inserted.
e) To get the currency symbol and trailing decimals in the Wholesale Cost and Discount numbers, do these 3 steps:
i) Move your cursor to the Wholesale Cost 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.
ii) Move your cursor to the end of the Wholesale Cost 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 $.
iii) 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!!!
iv) Press Alt-F9 again (the codes will disappear so you can see what your boilerplate looks like.)
f) Note that you are NOW looking at your complete boilerplate. Press Alt-F9 AGAIN and print this ONE page, exactly as it is,
using the regular print command (do not do a merge and do NOT use the quick print button – use the File command and
select the Print subcommand and specify “only the current page”), so we can see your fieldnames.
5) Be sure the fieldnames Balance Due and Discount line up with the data below them (hint: use tabs, not spaces).
6) Preview (but do NOT print) 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.
7) Select “Finish & merge/Print Documents/All” on the ribbon and print all the form letters. Label them as step 7.
Hand in: your query results and QBE grid from step 1, the printed boilerplate (step 4f) which looks like the example below, with any
graph and all the memos generated by the merge operation (step 7). There should be 1 memo for each line in your query results table.
See the figure below for a sample of your output letters.
Document1
© 4/21/2020, all rights reserved, D. J. Foreman
CS 105 Integration Assignment
To:
From:
Ref:
On Hand:
Subject:
«Vendor_Name» «Vendor_Code»
«Telephone_Number»
put your name here
item «Item_Number»
«On_Hand»
Billing
Note these special symbols appear only
when you insert actual fieldnames.
In order to increase the sales in The Museum Gift Shop, we are requesting the following
discount on the referenced item.
Wholesale cost
«Wholesale_Cost»
Discount
«Discount»
[
Document1
Insert your graph here
]
© 4/21/2020, all rights reserved, D. J. Foreman
CS 105 Integration Assignment
1) **Start Access and open your Museum Gift Shop DB.
a) Create a single Query (which will have 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. Your query must USE all of them. It is an “or” query):
i) The Wholesale Cost is either 12.35 or 27.99 and the Vendor Code is XX.
ii) Or the Item Number is less than 6185 and the On Hand is less than or equal to 12
b) Sort the results in ascending order by Vendor Code. Your results must include: Item Number, Item Description, Vendor
Code, Vendor Name, from their respective tables. You will also need Discount Value, a calculated field in the query,
whose value is 0.20 * the Wholesale Cost value.
c) Save and name this query “Integration XX”. Run the query and make sure you got correct results. Close Access. Submit
your Database to Blackboard.
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 Wholesale Cost & 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.
a) For “Select Document type”: select “letters” (SKIP THIS STEP if this is the first time for doing this lab)
b) 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)
c) 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.
d) Click inside your document where you wish a field to be placed, then click on “insert merge field” and select the desired
field. Also: insert the Vendor Code field to the right of Vendor Name. Repeat until all desired fields are inserted.
e) To get the currency symbol and trailing decimals in the Wholesale Cost and Discount numbers, do these 3 steps:
i) Move your cursor to the Wholesale Cost 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.
ii) Move your cursor to the end of the Wholesale Cost 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 $.
iii) 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!!!
iv) Press Alt-F9 again (the codes will disappear so you can see what your boilerplate looks like.)
f) Note that you are NOW looking at your complete boilerplate. Press Alt-F9 AGAIN and print this ONE page, exactly as it is,
using the regular print command (do not do a merge and do NOT use the quick print button – use the File command and
select the Print subcommand and specify “only the current page”), so we can see your fieldnames.
5) Be sure the fieldnames Balance Due and Discount line up with the data below them (hint: use tabs, not spaces).
6) Preview (but do NOT print) 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.
7) Select “Finish & merge/Print Documents/All” on the ribbon and print all the form letters. Label them as step 7.
Hand in: your query results and QBE grid from step 1, the printed boilerplate (step 4f) which looks like the example below, with any
graph and all the memos generated by the merge operation (step 7). There should be 1 memo for each line in your query results table.
See the figure below for a sample of your output letters.
Document1
© 4/21/2020, all rights reserved, D. J. Foreman
CS 105 Integration Assignment
To:
From:
Ref:
On Hand:
Subject:
«Vendor_Name» «Vendor_Code»
«Telephone_Number»
put your name here
item «Item_Number»
«On_Hand»
Billing
Note these special symbols appear only
when you insert actual fieldnames.
In order to increase the sales in The Museum Gift Shop, we are requesting the following
discount on the referenced item.
Wholesale cost
«Wholesale_Cost»
Discount
«Discount»
[
Document1
Insert your graph here
]
© 4/21/2020, all rights reserved, D. J. Foreman

Purchase answer to see full
attachment

Submit a Comment