Homework answers / question archive / Page | 1 MIS 311 ACCESS CASE OBJECTIVES The purpose of this assignment is to use some of the database skills about Microsoft Access you learned in class

Page | 1 MIS 311 ACCESS CASE OBJECTIVES The purpose of this assignment is to use some of the database skills about Microsoft Access you learned in class

MS Access

Share With

Page | 1 MIS 311 ACCESS CASE OBJECTIVES The purpose of this assignment is to use some of the database skills about Microsoft Access you learned in class. In particular, you will use the following database skills: ? Table Design ? Relationships ? Reports ? Queries ? Aggregate Functions ? Calculated Fields WHAT YOU NEED You need the following application and file to complete this assignment: ? Microsoft Access 2010 or newer version ? Party.accdb and Party_Data.xlsx (available for download as AccessCaseFiles.zip on LMS) OVERVIEW You have a company which organizes parties for your customers. You would like to implement a database system to keep track of parties you organized. Your company organizes various parties such as conferences, banquets, weddings, birthdays, receptions, and cocktails in 20 different locations. A customer can select multiple parties that your company offers. A customer will be charged for each party your company organizes based on the PricePerPerson and PartySize. You will build a database that tracks customers, parties you organized, party types you offer, and party locations. You are provided with a partially completed database with the Party Locations and Party Types tables. You will build and populate other required tables, create several relationships, design a form, design a report, and create several queries. Complete the following steps: Page | 2 INSTRUCTIONS 1. Download the Access file (Party.accdb) and the Excel data file (Party_Data.xlsx) from the LMS, and save them in a folder you create on your computer. 2. Rename the Party.accdb as Party_StudentLastNames.accdb by adding your last names. “Customers” table: 3. Create the Customers table, and set field properties as shown in the following table. Save your changes, and then close the Customers table. Field Name Data Type Description Field Size Comments CustomerID Number A unique identifying number assigned to each customer. Serves as primary key. Long Integer Required LastName Text The customer’s last name 50 Required FirstName Text The customer’s first name 30 Required AccountDate Date/Time The customer’s registration date Short Date Required Address Text The customer’s street address 75 Required City Text The customer’s city 30 Required State Text The customer’s state 2 Required Zip Text The customer’s zip code 10 Required Phone Text The customer’s phone number. 15 Required 4. Import the Customers data stored in Party_Data.xlsx to populate the Customers table. “Parties” table: 5. Create the Parties table, and set field properties as shown in the following table. Save your changes, and then close the Parties table. Field Name Data Type Description Field Size Comments PartyID Number A unique identifying number assigned to each party. Serves as primary key. Long Integer Required CustomerID Number The customer identification number which must match a customer ID from the Customers table. Serves as a foreign key. Long Integer Required PartyDate Date/Time Party launch date Short Date Required PartySize Number Party size in the form of the number of people attended Integer Required PartyTypeID Number The party type identification number which must match a party type ID from the Party Type table. Serves as a foreign key. Long Integer Required LocationID Number The location identification number which must match a location number from the Party Location table. Serves as a foreign key. Long Integer Required 6. Import the Parties data stored in Party_Data.xlsx to populate the Parties table. Page | 3 7. Create the three relationships that are necessary. First, a relationship between the Customers and Parties tables is required. Since each table contains the CustomerID field, you use the CustomerID field to create the relationship. Second, a relationship between the Parties and Party Type tables is required. Since each table contains the PartyTypeID field, you use the PartyTypeID field to create the relationship. Third, a relationship between the Parties and Party Locations tables is required. Since each table contains the LocationID field, you use the LocationID field to create the relationship. Select the Enforce Referential Integrity option and the Cascade Update Related Field option for each relationship. Save the changes to the Relationships window and close it. “Customer Information” form: 8. Using the following figure as a guide, create a form based on the Customers table. When a new customer registers to your service, customer’s information will be entered using this form. Create the “Customer Parties Report” button to open the “Customer Parties” report. Rename the form as Customer Information. (Hint: Create “Customer Parties Report” button after creating “Customer Parties” report in the step 9.) Page | 4 “Customer Parties” report: 9. Using the following figure as a guide, use the Report Wizard to create a report based on the Customers, Parties, Party Types, and Party Locations tables. Display the First Name and Last Name from the Customer table, the Party ID and Party Date from the Parties table, the Party Type from the Party Type table, and the City and District from the Party Location table. Below each customer, calculate the Total Number of Parties. Rename the report as Customer Parties. Page | 5 Information Specifications: Build queries to answer the following questions: 1. Which customers did not have any parties? Display the CustomerID, FirstName, and LastName. Name the query as Customers without Parties. 2. Which customers are living in New York? Display the CustomerID, FirstName, and LastName. Do not display the customer city. Sort the information in ascending order based on the customer’s last name. Name the query as New York Customers. 3. Which customers have six or more parties? Display the CustomerID, FirstName, LastName, and the total number of parties that a customer has. Name the query as Customers with Multiple Parties. 4. What is the total party cost charged to each customer? Display the CustomerID, FirstName, LastName, and the Total Party Cost charged for that customer. Name the query as Total Party Cost by Each Customer. (Hint: Each party’s cost can be calculated by multiplying the number of people attended in a party with the price per person.) 5. What is the total number of parties and the average of party size categorized by each party type? Display the PartyType, the Number of Parties, and the Average of Party Size for each party type. Name the query as Party Statistics by Each Party Type. 6. What is the total number of attendants invited by each customer? Display the CustomerID, FirstName, LastName, and the Total Number of Attendants that a customer invited. Name the query as Party Statistics by Each Customer.