Music Sales Database

Before you start -- read this whole assignment watch the project overview (assignment area). If you dont have Access on your computer (all Mac users), see the FAQ area in our Canvas Modules area about using apps.usf.edu. BE SURE you review how to save a file to your computer when using apps.usf.edu there are specific instructions (a PDF you can download) in the FAQ area.

Scenario: You are creating a sample database for a small chain of sheet music stores. The database will contain 3 tables; one with sheet music info, one with store info, and one with sales info. You will create the database, tables, a form, queries and a report.

Part 1 Getting Started / Database

• Download the Data Create a folder for your Access project and download the Excel workbook (Access 1 Data U16.xlsx) from the Assignment page. This Excel file contains the data for 2 of the 3 tables (Music and Sales)
• Create a blank database and name it your last name followed by your initials and _1AC.accdb Note: .mdb is the extension Access will add to your database file name if you are using Access2003, for Access 2007 or later, it will be .accdb.

Part 2 Tables

• tblMusic table
  • Import the music information from the Music spreadsheet in the Excel file Access 1 Data U16.xlsx into an Access table (named tblMusic). Note there are several spreadsheets within that one workbook.
  • The primary key should be the CODE.
  • Set the COMPOSER and TITLE so they are required fields.
  • Add data validation to ensure that no new music entered has a SELLING PRICE over $20.00. Display an error message if someone tries to enter an invalid SELLING PRICE. When you save your table, you may get a message saying that you have changed the Data Integrity Rules, thats fine just click ok. Test your data validation by changing a SELLING PRICE (then change it back as needed).
• tblSales table
  • Import the SALES information from the SALES spreadsheet in the Excel file Access 1 Data U16.xlsx into an Access table (named tblSales).
  • Keep the fields in the same order in the table as they are in the spreadsheet.
  • The primary key should be a combination of CODE, STORE ID, and MONTH (a multiple field primary key).
• tblStores table - Create this table (manually / using the design view) containing the information shown below.
  • The table should have the 5 fields shown below (Store ID, Store Name, City, Zip, Phone).
  • One field should be designated as a primary key. Be sure it is an appropriate field, one that wont be duplicated if additional data is entered.
  • In the table design view, designate the city field as a required fields. This means the user must enter data in this field when adding records to the table.
  • Set up an input mask for the phone number (its up to you whether you store the symbols or not).
  • Name your table tblStores
  • Once your table is created, go to the datasheet view and add the data below (type it in). Note: your records may be in a different sorted order than those displayed.
  • Create a new store (make up the information & use your last name as the STORE NAME). Test that the input mask for the phone number works correctly. see image.
• All tables
  • Make sure all data types (including zip code) are appropriate review when fields should be numeric vs. text. Also set reasonable field sizes (not default values). Watch the online Access Tips for more info on this. Note: if you shorten field sizes you may get a warning, some data may be lost This just means that you should be sure that your new field size doesnt truncate any existing data.
  • Add data descriptions for all fields. Note any multiple-field and primary keys in your data descriptions.
  • No additional fields/tables should be added to the project.

Part 3 Relationships

• Create a lookup field to join the Sales and Store tables.
  • Make the Store ID field (in tblSales) a lookup field. This will join your two tables, so dont join them before creating the lookup field.
  • When you set up the lookup field, use the lookup wizard and let the users see both the Store ID and the store name. Sort by either field and do not hide the key column.
  • When a new record is entered into tblSales, the Store ID should reference the tblStores table and give users a drop down menu showing the Store ID and Store Name (full or partial display is ok) & let them click on the one they want.
  • The Store ID should be the field that is stored and the label for the lookup column should also be Store ID.
  • Test your lookup field by adding a new records to the tblSales table. Use a valid (Music) Code & Store ID and make up the rest of the data.
• Using the Database Tools tab, create a second relationship. This time between the Music and Sales tables. The Code field should join the tblMusic and tblSales tables.
• View your database relationships; be sure all tables are displayed. The tblMusic and tblSales tables should be joined and the tblSales and tblStores table should be joined.

Part 4 Form

• Using the Form wizard, create an input form for your Music table (name it frmMusic).
  • Include all fields in your form
  • In the form design view, add a new background color to your form use some shade of yellow/orange.
  • In the form design view, put the title on the top of the form, Music input/edit
  • Test the form and add a new record with your last name as the composer and make-up the rest of the data. Also try navigating, editing, and deleting records. When you are done, be sure to have the records above and at least one other record with your name as the composer.

Part 5 Queries Create the following queries

Queries should work even if new data is added to your tables. Check your results against the overview note that when you added a few extra records (above), your displayed results may include these additional records. **In the queries below, fields can be displayed in any order, as long as all sorts work correctly.

• qry01-Grade
  • Create a query that selects all records less than a grade 5. (Numeric criteria)
  • Dont use a parameter.
  • Display the Music Code, Title, Grade, Event.
  • In the design view, sort by Event first and then by Grade (both ascending).
  • Name this query as listed above
• qry02-City Create a query that will allow the end user to enter a CITY and see all the sales records for that CITY.
  • Use the parameter feature correctly to select a CITY to report on
  • Include fields: STORE ID, MONTH, CODE, UNITS SOLD, and CITY.
  • In the design view, sort by STORE ID (ascending).
  • Name this query as listed above
• qry03-StartS
  • Create a query that selects all records with Titles that start with S.
  • Use a wildcard (Dont use a parameter)
  • Display the title, code, grade and event.
  • In the design view, sort by Grade in ascending order, then Title in ascending order.
  • Name this query as listed above
• qry04-Aria
  • Create a query that selects all records with titles that have the word Aria in them.
  • Use wildcards (Dont use a parameter)
  • Display the title, code, grade and event.
  • In the design view, sort by Title in ascending order.
  • Name this query as listed above
• qry05-Search
  • Create a query that lets the user enter a word or group of characters (parameter feature) and displays all Titles that have the word/characters anywhere in their name. Use the parameter feature and wildcards.
  • Display the title, code, grade and event.
  • In the design view, sort by Title in ascending order.
• qry06-Profit - Create a query that includes two calculated fields (must be done in the query).
  • For all titles, calculate the PROFIT (how much each piece of music is marked up in $)
  • Also calculate the MARKUP percentage for each piece (the % it is marked up based on the COST).
  • Include fields: CODE, TITLE, COST, SELLING PRICE, PROFIT and MARKUP %.
  • Sort in the design view, descending order by MARKUP%.
  • Format the PROFIT in a currency format ($ plus 2 decimal places) and the MARKUP % as a percent sign and 1 decimal place. **Note: you may have to run the query once with the property set as percent before it will display/allow you to change the decimal places property.
  • Name this query as listed above
• qry07-Advanced :
  • For each title, if Grade is greater than 5, display a message **Advanced. Otherwise, display nothing. Create a new field in your query called Notes and use the IIF operator to display the appropriate message.
  • Display only the following fields (any order that works): Code, Title, Composer, Grade and Notes.
  • In the design view, sort by Title (ascending).
  • Name this query as listed above
• qry08-UnitsSold:
  • Create a query that displays all Sales, grouped by Store ID and showing the total Units Sold for each Store ID
  • Display only the following fields (any order that works): Store ID and the sum of the Units Sold
  • In the design view, sort by Store ID (ascending order).
  • Name this query as listed above
• qry9-Top15- Create a query that shows the top 15 selling pieces of music in the tblSales table (based on UNITS SOLD).
  • Use the top values function in Access.
  • Include fields: CODE, TITLE, Composer, STORE ID, MONTH, & UNITS SOLD.
  • Sort by UNITS SOLD (descending) and within that by STORE ID (ascending). Sort in the design view.
  • Your fields can be displayed in any order, as long as any sorting works correctly.
  • Name this query as listed above
• qry10-Wildcard: Your manager found a ripped invoice and can only see that the Store ID has a 2 in the sixth character.
  • Create a query that displays all Store IDs with a 2 as the 6th character (for example ABC-321 or ABC-3211)
  • Display only the following fields (any order that works): Store ID, Store Name, Phone
  • In the design view, sort by Store Name (ascending)
  • Use at least 2 types of wildcards (?, *, #) see tips or class on different types of wildcards
  • Name this query as listed above
• Check all queries for reasonable/accurate results, check that the sorts are working correctly, check the formats of numbers, etc.
• Use only the tables you need for each query and be sure the queries are named correctly

Part 5 Reports Create a Sales report:

• First create a query with the following fields: CITY, STORE ID, STORE NAME, CODE, TITLE, UNITS SOLD. Name the qryViewAll.
• Create a SALES report from the qryViewAll query. Use the wizard if youd like and include the following:
  • Group by CITY and then by STORE ID
  • Within the grouping, sort by UNITS SOLD (descending) and then by CODE (ascending)
  • Include Summary Options by Group (sum of all UNITS SOLD and detail). You should get summary information displayed for each STORE ID and also for each CITY.
  • Name this rptSales.
  • Move everything around until your report is formatted like this sample.
  • Headings should be modified and in the same order as above & UNITS SOLD should be wrapped onto two lines.
  • In the design view, using the design tab, find the tool to draw a line below the heading
  • Make sure the report fits on one page in width (will be several pages in length)
  • Add color fonts to your report headings (any color except for blue or black).
  • Add your name in the Report header in a separate label from the title.
  • Make sure all headings/data are complete (not cut-off).
  • Add the store name and city name to the total areas of the report
  • Look over your report and make sure it looks good and makes sense spend some time/effort on it.
  • Name your report rptSales (title inside report should be MUSIC SALES).