Homework answers / question archive / Access Class Assignment 10 1

Access Class Assignment 10 1

Management

Share With

Access Class Assignment 10 1. Open the Access Class Assignment 10.zip folder, then copy the Access CA10.accdb file to a location outside of the zip folder. Rename this database file as your LastName_FirstName_Access_CA10.accdb. 2. Use the Design View to create the Student table from the information provided on the second page. Then manually enter your name and age. 3. Review the data in the Tables section of your handout and create the necessary relationships to link the data together. Make sure to enforce referential integrity in your relationships. Do not link the Student table. 4. Create a query to select all customers. The query should display only the CustomerID, LastName, Address, City, State, ZipCode, and Phone fields. Save the query with the name Customer Query. 5. Create a query to select all customers and vehicles they have purchased. The query should display only the FirstName, LastName, Phone, VIN, Make, Model, PurchaseDate, and PurchasePrice fields and sort by the LastName in ascending order. Save the query with the name Customer/VIN Query. 6. Create a query to select all vehicles that include a sun roof or seat warmers. The query should display all the fields in the Vehicle table and the Description field in the Optionstable. Save the query with the name Vehicle with Sunroof/Seat Warmers Query. 7. Create a query to select all vehicles that cost more than $17000 or purchased before December 5, 1998 or where the owner lives in the 29212 zip code. The query should display all the fields in the Vehicle table. Save the query with the name Vehicle by Cost/Date Purchased/Zip Code Query. 8. Create a report using the Customer table (do not base the report on a query). Use the Report Wizard. Select all fields. Accept default values for all remaining options (Click Finish). Save report with the name Customer Report. 9. Create a report using the Report Wizard based on the two-table query you created in Step 5. Select all fields except VIN. Choose to view the data by Vehicle. Select a grouping level on Make. Click Finish. Save report with the name Vehicle Report. 10. Save and close your database file and upload to Blackboard. ASSIGNMENT IS DUE BY THURSDAY, APRIL 21ST . Tables Section Customer CustomerID LastName FirstName Address City State ZipCode Phone 00001 Carter Billy 20 Main St. Columbia SC 29208 (803) 562-5263 00002 Jones Susie 45 Bull St. Columbia SC 29212 (803) 326-8956 00003 Smith Jeff 56 A Meeting St. West Columbia SC 29206 (803) 569-9856 00004 Harding Karen 58 Clemson Rd. Columbia SC 29212 (803) 567-8541 Vehicle VIN Make Model Color Cost Sold? PurchasePrice PurchaseDate SoldTo 11111 Honda Accord White 16500 Yes 22000 3/1/1999 00002 11122 Honda Accord Blue 17600 No 11133 Honda Prelude Green 14500 Yes 23500 1/1/1999 00003 22222 Toyota Camry Cream 26000 Yes 35000 12/5/1998 00001 33333 Toyota Corolla Beige 12000 Yes 16750 11/15/1998 00003 Contains VIN OptionCode 11111 B0001 11111 B0004 11133 B0001 11133 B0002 22222 B0002 22222 B0004 Options OptionCode Description B0001 Sun Roof B0002 Seat Warmers B0003 CD Player B0004 Dancing Hula Girl Table Name: Student Field Name Data Type Field Size Description Age Short Text 2 Primary Key StudentName Short Text 30 Table Name: Customer Field Name Data Type Field Size Description CustomerID Short Text 5 Primary Key LastName Short Text 20 FirstName Short Text 20 Address Short Text 30 City Short Text 20 State Short Text 2 ZipCode Short Text 10 Phone Short Text 15 Table Name: Vehicle Field Name Data Type Field Size Description VIN Short Text 10 Primary Key Make Short Text 15 Model Short Text 15 Color Short Text 10 Cost Currency Sold? Yes/No PurchasePrice Currency PurchaseDate Date/Time SoldTo Short Text 5 Table Name: Contains Field Name Data Type Field Size Description VIN Short Text 10 *Primary Key OptionCode Short Text 5 *Primary Key *Note: This is a concatenated key Table Name: Options Field Name Data Type Field Size Description OptionCode Short Text 5 Primary Key Description Short Text 30