Managing Inventory and Sales at Marshall’s Music Store

 

Project Description:

Marshall’s Music Store, located in the trendy area of town, carries many different genres of music. The store has a loyal customer base, which is why they are one of the few successful music stores still in existence today. You have been asked to use queries to manipulate data from both the music store and transaction databases. This way, the owner can ensure that there is enough inventory in stock. He has given you a scaled-down copy of the database to use. Once you create the queries, he will import them into the original database.

 

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Access. Open the downloaded Access file named a04ws08_grader_h1.accdb.Save the file with the name a04ws08MusicStore_LastFirst, replacing LastFirst with your name. Enable content if necessary. Download the a04ws08Transactions.accdb database.

0

2

Create an outer join query based on tblAlbum and tblPurchaseDetails that displays the ArtistID and AlbumName, in that order, then add a new field named SalesVolume that calculates the total quantity of all albums sold. Be sure the query lists all albums regardless of whether any have been sold. Save the query as qryAlbumSalesVolume and then close the query.

Hint: To create the query, on the CREATE tab, in the Queries group, click Query Design. Add the tables, and in Design view, double-click the join line for the two tables and select option 2. To create the new field, enter SalesVolume: Quantity. To display the total quantity, on the DESIGN tab, in the Show/Hide group, click Totals and change the Total row for SalesVolume to Sum.

6

3

Create a query based on tblEmployee and tblPurchase using the Find Unmatched Query Wizard that lists the EmployeeID, FirstName, and LastName, in that order, of any employee who has not yet made a sale. Save the query as qryEmployeeNoSales and then close the query.

Hint: To create the query, on the CREATE tab, in the Queries group, click Query Wizard. Click Find Unmatched Query Wizard.

6

4

Create a query based on tblCustomer and tblPurchase using the Find Unmatched Query Wizard that lists the CustomerID, FirstName, LastName, StreetAddress, City, State, and ZipCode, in that order. Save the query as qryCustomerNoSales. Change the criteria to display only those customers not living in New Mexico. Save and run the query, then close it.

Hint: Set the criteria for the State field to Not Like "NM".

6

5

Open the a04ws08Transactions.accdb database. Enable the content, if necessary. Create an append query that appends the data from the tblPurchase table in the a04ws08Transactions database to the tblPurchase table in the a04ws08MusicStore database. Run the query, and then close it without saving it.

hint: To create the query, on the CREATE tab, in the Queries group, click Query Design. Add the fields and then on the QUERY TOOLS DESIGN tab, in the Query Type group, click Append.

4

6

Create an append query that appends the data from the tblPurchaseDetails table in the a04ws08Transactionsdatabase to the tblPurchaseDetails table in the a04ws08MusicStore database. Run the query, and then close it without saving it. Close the a04ws08Transactions database.

Hint: To create the query, on the CREATE tab, in the Queries group, click Query Design. Add the fields and then on the QUERY TOOLS DESIGN tab, in the Query Type group, click Append.

5

7

Because the manager just ordered three copies of each album, create an update query to update the number of albums in stock in the tblAlbum table. Save the query as qryUpdateInventory and then close the query.

Hint: To create the query, on the CREATE tab, in the Queries group, click Query Design. Add the fields and then on the QUERY TOOLS DESIGN tab, in the Query Type group, click Update. The Update to row should contain [NumberInStock]+3.

5

8

Since Amanda Johnson no longer works at the music store, create a delete query that deletes all transactions entered by employee number 78528 from the tblPurchase table. Save the query as qryDeleteEmployee and then close the query.

Hint: To update the relationship, on the DATABASE TOOLS tab, in the Relationships group, click Relationships. Right-click the relationship line and click Edit Relationship. To create the query, on the CREATE tab, in the Queries group, click Query Design. Add the fields and then on the QUERY TOOLS DESIGN tab, in the Query Type group, click Delete.

5

9

Since Keith Marquardt moved and is no longer a customer, create a delete query that deletes Customer ID 9 from the tblCustomer table and all related tables. Update any relationships to the tblCustomer table as needed. Save the query as qryDeleteCustomer and then close the query.

5

10

Create an update query to update the selling price of albums in the tblAlbum table. For all albums that currently sell for less than $10, update the selling price to $10.95. Save the query as qryUpdatePrice and then close the query.

8

11

Close all database objects. Close the database and then exit Access. Submit the database as directed.

0

 

Total Points

50

                                                                                                                                                     

Attachments:

• 1513940506-A_WS08_YOV2_H1_Instructions.docx
• 1513940529-a04ws08_grader_h1.accdb
• 1513940530-a04ws08Transactions.accdb