| Part A: Data Files needed: Vendor.accdb, Supplies.xlsx and Field Properties.xlsx | ||
| Question | Task | Marks |
| 1. | Open the Vendor database in Integrating Project folder. | |
| 2. | Use the Import Spreadsheet Wizard to the Product table. The data you need to import is contained in the Supplies.xlsx in the project folder. Open the Product table in Datasheet view and resize columns to their best fit. Save and close the Product table. | 5 |
| 3a | Set up the field properties of Supplier table. See Field Properties.xlsx. | 5 |
| 3b | Define a one-to-many relationship between the primary Supplier table and the related Product table. Resize the table field lists so that all field names are visible. Select the referential integrity option and the cascade updates option for the relationship. Save the changes to the Relationships window and close it, compact and repair the Vendor database, and then close the database. | 5 |
| 4. | Reopen the Vendor database. Open the Supplier table in Datasheet view. Locate Magdalen Cheung’s name and change to your own name. | 4 |
| 5. | Create a query based on the Supplier table. Include the following fields in the query, in the order shown: Company, Category, ContactFirst ContactLast, Phone, and InitialContact. Sort the query in ascending order based on the Category field values. Save the query as ContactList, and then run the query. | 5 |
| 6. | Format the ContactList query: Change the font size of the ContactList query to 12 points. Resize and bestfit all columns to display all column headings.Change the alternate row color to the Theme Color named Green, Accent 6, Lighter 60%. Save and close the query. | 6 |
| 7. | Create a query based on the Supplier and Product tables. Select the Company, Category, and State fields from the Supplier table, and the ProductName, Price, Units, and Weight fields from the Product table. Sort the query results in descending order based on price. Select only those records with a State field value of CT, but do not display the State field values in the query results. Save the query as CTSuppliers, run the query, and then close it. | 10 |
| 8. | Create a query that lists all products that cost more than $200 and are sterile. Display the following fields from the Product table in the query results: ProductID, ProductName, Price, Units, and TempControl. Save the query as HighPriceAndSterile, run the query, and then close it. | 10 |
| 9. | Create a query that lists only those products that cost $1000 or more, along with a 5% discount amount based on the price of the product. Include the Company field from the Supplier table and the following fields from the Product table in the query: ProductID, ProductName, and Price. Save the query as HighPriceWithDiscount. Display the discount in a calculated field named DiscountAmt that determines a 5% discount based on the Price field values. Set the Caption property DiscountAmt for the calculated field. Display the query results in descending order by Price. Save and run the query. | 10 |
| 10. | Create a query that calculates the lowest, highest, and average prices for all products using the field names LowestPrice, HighestPrice, and AveragePrice, respectively. Set the Caption property for each field to include a space between the two words in the field name. Run the query, resize all columns in the datasheet to their best fit, save the query as PriceStatistics, and then close it. | 10 |
| 11. | In the Navigation Pane, copy the PriceStatistics query, and then rename the copied query as PriceStatisticsBySupplier. | 5 |
| 12. | Modify the PriceStatisticsBySupplier query so that the records are grouped by the Company field in the Supplier table. The Company field should appear first in the query datasheet. Save and run the query, and then close it. | 5 |
| Part B: Create Mail Labels and Perform Mail Merge | ||
| 13 | Create a Report of mailing labels by using Label Wizard and using the Supplier table. Include the following fields: ContactFirst, ContactLast, Company, Address, City, State, Zip. Use Avery J8163 or similar to this measurement: 99 x 38.1 mm as your destination label. Accept all defaults. Make sure that the Name Block, Address Block and the Greeting Line is properly set up. No need to sort your labels. Save the Report as Labels Suppliers. | 10 |
| 14 | Export the Supplier table to a Word Merge as the recipient list and use the Catalogue Request Letter.docx as the Main Document in a Mail Merge. Create your company logo and insert it at the top left corner of the letter. Insert the current date by inserting the date code. Date format should be: April 12, 2021. Under the signature line, insert your name as manager. Use [Edit individual letters] feature to view all merged letters. Save all merged letters as “Catalogue Merged Letters”. | 10 |
| Files Submission: (1) Vendor.accdb, (2) Catalogue Merged Letters.docx |
Get expert help for COMP126 Integrating Project and many more. 24X7 help, plag free solution. Order online now!
