1)Create a blank database file in ACCESS 2016 and save the database file as “Klascom –FTR Data” on your ‘your folder
2) Import the “Klascom” tab in your Excel file “Klascom Initial Data” into the Access database file “Klascom –FTR Data” with all the due diligence you have learned in this course, save the table as “Klascom Master table”, and then: a) Using “Klascom Master table” as a base/copy, create as many tables as are essential to ensure efficient running of the database. Label each table according tothe group of fields it contains. Be sure to use appropriate data type and properties for each field. 一b) Add the following two new fields to the appropriate table(s): i) A field called “member photo” in the appropriate table using the appropriate field type and caption “photo”. Be sure that at least two of the members have photo provided; you may use any photo for the members 一ii) A field called “membership type”. This field should be a lookup type with options “Pre-Merger” and “Post-Merger” and entries limited to list but no multiple entries allowed for any member. Do not manually fill in the values. This would be done automatically using an update query in step (c) below. 一一c) Create and run an update query called “membership type” to populate the membership type field automatically as follows: i) Pre-Merger –for every member who joined before 1/1/2015 一ii) Post-Merger –for every member who joined from 1/1/2015 一一d) Establish relationships between your tables, with “Enforce Referential Integrity”, “Cascade Update Related Fields” and “Cascade Delete Related Fields” all taken care of. While you are establishing relationship between your tables, use MS PowerPoint toolto make a one-page audio-video “screen recording” of the process. 一e) Create a Form (using form sub-form type) which a data entry clerk can use in entering the data for all fields for new customers. 一f) Create a crosstab query (any would do). Call this ‘my crosstab’ 一g) Create a parameter query which can be used to pull records of all the products that a specific member did not pay for or were delivered by 3/20/2016. They would be using the ‘member ID’ as the parameter for pulling the required records. Call this query ‘Unpaid scheduled deliveries’ 一h) Create an Access Report called ‘Summary Report’. The Report must contain at least six fields (from across all your tables), grouping must be used in this report. Your Report must also contain the following summary statistics values (Sum, Avg, Max, Min) with “% of totals for the sums”, totals and grand totals as well as the details of the values from each of those six fields. 一i) Create a “Make-Table” query called “Delivery Schedule” query that would make/generate a table called “Delivery Schedule” and which contains the following fields: ‘member last names, product name, quantity, and delivery date. Run the ‘Make-Table’ so that the required table “Delivery Schedule” is generated. 一j) Create and run an update query (named “HSD 9% price increase”) that would increase the price of every product from HSD by 9%. 一3) Export the “Delivery Schedule” table from ACCESS to WORD. Save the WORD document under the name “Delivery Schedule”. 4) DELIVERABLE:a) Excel: “Klascom –Initial Data’ 一b) Access: “Klascom –FTR Data” with all its objects, i.e. (i) Tables: All the tables including the “delivery schedule’ 一(ii) Report: ‘Summary Report’ 一(iii) Queries: “my crosstab”, “Delivery Schedule”, “unpaid schedule deliveries”, “membership type”, “HSD 15% price increase”. 一一c) Word: “Delivery Schedule”. 一d) PPT: “Screen recording” of process for establishing relationship between tables. One page
