I was wondering if someone had plenty of experience with Microsoft Access

Page 1 of 4 Access 1 Assignment - ISM3011 Ask before/after/during class or come into office/online hours if you have questions on any of this. Refer to the syllabus on Academic Dishonesty and group/individual work and allowable help for all projects – also remember it’s your responsibility to protect your work. Before you start -- read this whole assignment watch the project overview (assignment area ). If you don’t 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 project assignment area and FAQ page . If you have issues with apps.usf.edu/Citrix – please contact USF IT at it.usf.edu/help. You can also use the USF library computers with a flash drive to save your work. 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 wi ll create the database, tables , a form, queries and a report. There is an overview that will walk you through this assignment. Part 1 – Getting Started / Databas e  Download the Data – Create a folder for your Access project and download the Excel workbook (Access 1 Data S17 .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  tbl Music table o Import the music information from the ‘Music’ spread sheet in the Excel file ‘ Access 1 Data S17 .xlsx’ into an Access table (named ‘ tblMusic’ ). Note there are several spreadsheets within that one workbook. o The primary key should be the CODE . o Set the COMPOSER and TITLE so they are required fields. o 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 m essage saying that you have changed the Data Integrity Rules, that’s fine –just click ok. Test your data validation by changing a SELLING PRICE (then change it back as needed).  tblSales table o Import the SALES information from the ‘ SALES’ spread sheet in the Excel file ‘ Access 1 Data S17 .xlsx ’ into an Access table (named ‘ tblSales ). o Keep the fields in the same order in the table as they are in the spreadsheet. o The primary key should be a combination of CODE , STORE ID , and MONTH (a multiple field primary ke y).  tblStores table - Create this table (manually / using the design view) containing the information shown below. o The table should have the 5 fields shown below (Store ID, Store Name, City, Zip, Phone) . o One field should be designated as a primary key. Be sure it is an appropriate field, one that won’t be duplicated if additional data is entered. o 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. o Set up an input mask for the phone number (it’s up to you whether you store the symbols or not). o Name your table tblStores o 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. Page 2 of 4 o 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.  All tables o Make s ure all data types (including zip code ) are appropriate – review when fields should be numeric vs. text. Also set reasonable field sizes (not default values) . W atch 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 doesn’t truncate any existing data. o Add data descriptions for all fields. Note any multiple -field and primary keys in your data descriptions. o No additional fields/tables should be added to the project . Part 3 – Relationships  Create a lookup field to join the Sales and Store tables. o Make the Store ID field (in tblSales) a lookup field. This will join your two tables, so don’t join them befor e creating the lookup field. o 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’ . o When a new record is entered into tblSales, the Store I D should reference the tbl Store s 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. o The Store ID should be the field that is stored and the label for the lookup column should also be Store ID . o 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 sho uld be joined and the tblSales and tblStore s table should be joined . Part 4 – Form  Using the Form wizard, create an input form for your Music table (name it frmMusic). o Include all fields in your form o In the form design view, add a new background color to your form – use some shade of yellow/orange. o In the form design view, put the title on the top of the form, ‘Music input/edit’ o Test the form and add a new record with your last name as the composer an d make -up the rest of the data. Also try navigating, editing, and deleting records. When you are done, be sure to have the records from the Music table 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 o Create a query that selects all records less than a grade 5. (Numeric criteria) o Don’t use a parameter. o Display the Music Code, Title, Grade, Event . o In the design view, sort by Event first and then by Grade (both ascending). o Name this query as listed above  qry0 2-City – Create a query that will allow the end user to enter a CITY and see all the sales records for that CITY. o Use the parameter feature correctly to select a CITY to report on o Include fields: STORE ID, MONTH, CODE, UNITS SOLD, and CITY. o In the design view, s ort by STORE ID (ascending) . o Name this query as listed above  qry0 3-StartS o Create a query that selects all records with Titles th at start with S. o Use a wildcard (Don’t use a parameter) o Display the title, code, grade and event. o In the design view, sort by Grade in ascending order, then Title in ascending order. o Name this query as listed above  qry0 4-Aria Page 3 of 4 o Create a query that selects all records with titles that have the word Aria in them. o Use wildcards (Don’t use a parameter) o Display the title, code, grade and event. o In the design view, sort by Title in ascending order. o Name this query as listed above  qry0 5-Search o 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. o Display the title, code, grade and e vent. o In the design view, sort by Title in ascending order. o Name this query as listed above.  qry0 6-Profit - Create a query that includes two calculated fields (must be done in the query). o For all titles, c alculate the PROFIT (how much each piece of music is marked up in $) o Also c alculate the MARKUP percentage for each piece (the % it is marked up based on the COST). o Include fields: CODE, TITLE, COST, SELLING PRICE, PROFIT and MARKUP %. o Sort in the design view, descending order by MARKUP%. o 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 plac es property. o Name this query as listed above  qry0 7-Advanced : o 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. o Display only the following fields (any order that works): Code, Title, Composer, Grade and Notes . o In the design view, sort by Title (ascending). o Name this query as listed above  qry0 8-UnitsSold : o Create a query that displays all Sa les, grouped by Store ID and showing the total Units Sold for each Store ID o Display only the following fields (any order that works): Store ID and the sum of the Units Sold o In the design view, sort by Store ID (ascending order). o 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). o Use the top values function in Access. o Include fields: CODE, TITLE, Composer, STORE ID, MONTH, & UNITS SOLD. o Sort by UNITS SOLD (descending) and within that by STORE ID (ascending). Sort in the design view. o Your fields can be displayed in any order, as long as any sorting works correctly. o Name this query as listed above  qry1 0-Wildcard : Your manager found a ripped invoice and can only see that the Store ID has a 2 in the sixth character. o Create a query that displays all Store IDs with a 2 as the 6 th character (for example ABC -321 or ABC -3211) o Display only the following fields (any order that works): Store ID, Store Name, Phon e o In the design view, sort by Store Name (ascending) o Use at least 2 types of wildcards (?, *, #) – see tips or class on different types of wildcards o 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 c reate 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 you’d like and in clude the following: o Group by CITY and then by STORE ID o Within the grouping, sort by UNITS SOLD (descending) and then by CODE (ascending) o 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. o Name this rptSales. Page 4 of 4 o Move everything around until y our report is formatted like th is sample. o Headings should be modified and in the same order as above & UNITS SOLD should be wrapped onto two lines. o In the design view, using the design tab, find the tool to draw a line below the heading o Make sure the report fits on o ne page in width (will be several pages in length) o Add color fonts to your report headings (any color except for blue or black) . o Add your name in the Report header in a separate label from the title. o Make sure all headings/data are complete (not cut -off). o Add the store name and city name to the total areas of the report o Look over your report and make sure it looks good and makes sense – spend some time/effort on it. o Name your report rptSales (title inside report should be MUSIC SALES ). Project Submission Instructions / Notes:  Office/online hours get busy as deadlines approach. If you procrastinate and wait until the last days to work on your projec t, you may not be able to get all the help you want.  The only way we can fairly grade the projects is if we check for each requirement. Please go through the instructions before you submit & be sure you have done each one correctly so you don’t miss out on points. Compare your solution to the project overview.  Submitting: o Remember to leave all of the inter nal file properties intact for your project, if they are modified or deleted, you project won’t be accepted (see syllabus for more on this). o Read and follow the instructions in the Assignments section of Canvas on uploading and checking your upload. If you follow the se instructions you can ensure that your project is uploaded correctly (and is the correct project) . Be sure that Access and Excel are closed before you try to upload your project files. o If your project doesn’t upload correctly before the due date, it will be considered late and be assessed the late penalty – even it was finished on time. This is the only way we can ensure that students check their Canvas submissions.  Technology problems relating to your home computer (Windows based or Mac), internet connection or slow Canvas access are not valid excuses for late/missing work, unless Canvas is down for 6+ hours on the due date. Computers at USF computer labs and the library are available; leave enough time to access them as needed. Als o give yourself enough time that if a TA can’t answer a question, you’ll have time to contact me & I can either help you or make an allowance in your grade. If you wait un til the last days, I may not be able to do either.