The objective is to create aMicrosoft Access file as mentioned in the attachment. The instructionsare attached below and pasted below too.
Part 1 – Getting Started / Database
- Create a blank database and name it LASTNAMEINITIAL_2AC.accdb.
- Importthe Item information from the Items tab within the Excel fileAccess2_Data_F18.xlsx into an Access table.Name the table tblItems.
- The primary key should be the Item Code.
- Usedata validation to ensure no one can enter a Selling Price greater than$100 or less than 0.Display an error message if either situationoccurs.Test this by trying to enter an invalid Selling Price into table.
- Usedata validation to make Item Name a required field.This means the ItemName cannot be left blank when a new item is entered into the table.
- Importthe sales info from the Sales tab within the Excel fileAccess2_Data_F18.xlsxinto an Access table.Name the table tblSales.
- The primary key should be a combination of Month, Store ID, and Item Code (a multiple field primary key).
- All tables
- Besure the field sizes are appropriate (don’t use the default sizes), thedata types are appropriate, and there is a data description for eachfield.
- No additional fields/tables should be added to the project.
- Makethe Item Code field (in tblSales) a lookup field.This will join yourtwo tables, so don’t join them before creating the lookup field.
- Whenyou set up the lookup field, start in the tblSales table and use thelookup wizard and let the users see both the Item Code and Item Name(sort by Item Code) in the drop down menu (do not ‘hide the keycolumn’).
- When a new record is entered into tblSales, the ItemCode should reference the tblItems table and give users a drop down menushowing the Item Code and Item Name (full or partial display is ok)& let them click on the one they want.
- The Item Code should be the field that is stored and the label for the lookup column should also be Item Code.
- Testyour lookup field by adding 2 new records to the tblSales table.Use avalid Item Code & Store ID and make up the rest of the data.
- Viewyour database relationships; be sure both tables are displayed.ThetblSales and tblItems table should have been joined for you when youcreated the lookup field.
- qry1-DepartSearch– Create a query that accepts a user input for a department name and displays relevant data
- Use the parameter feature – 1 prompts only for the department name.
- Display only the following fields (any order that works): Department, Store ID, Month, Item Name, Units Sold
- In the design view, sort by Month (ascending) and by Units Sold (descending)
- qry2-PriceSearch – Create a query that accepts a user input for both a minimum Selling Price and a maximum Selling Price and displays all Items within that range (including maximum and minimum prices).
- Use the parameter feature – 2 prompts only.
- Display only the following fields (any order that works): Item Name, Item Code, Selling Price
- In the design view, sort by Selling Price (ascending)
- You can use >, <, = operators or the BETWEEN operator
- qry3-HairSelect– Create a query that selects Items based on Department and Selling Price.
- Displays all Items in the Hair Care department or have a Selling Price less than $10.00.Don’t use a parameter.
- Display only the following fields (any order that works): Item Name, Item Code, Department, Selling Price
- Sort first by Selling Price in descending order and then by Department (ascending order).
- qry4-Top10– Create a query that shows the top 10 Items based on Profit per unit(calculated field)
- Use the top values function in Access.
- Include fields (any order that works): Item Name, Item Code, Selling Price, Cost, Profit (create this calculated field)
- Sort in the design view by Item Code and Profit, both in descending order
- Your fields can be displayed in any order, as long as the sorting works correctly and the results are correct.
- In the design view, format Profit as currency with 2 decimal places.
- qry5-TotalSales – Create a query that displays the total units sold for each Department
- Display only the following fields (any order that works): Departmentand the sum of the Units Sold
- In the design view, sort by the Units Sold (descending order).
- qry6-UnitsCheck– Create a query that lists each record in the tblSales table and notesall those with units sold quantity less than or equal to 50 units .
- Display only the following fields (any order that works): Store ID, Month, Department, Item Name, Units Sold
- Inthe design view, sort by Department (ascending order), then by ItemName (ascending) and within that by Units Sold (descending order).
- Createa new field called UnitsCheck.For each record, if the Units Sold isless than or equal to 50, this field should display a message “**SmallSale”.If the Units Sold is greater than 50, don’t display anything.Usethe IIF operator to display the appropriate message.
- qry7-Wildcards:Your manager found a ripped report and can only see part of the printed information.
- Create a query that displays all Item Names with an Item Code containing a 0 (zero) as the 4th character and the Department Code has an ‘i’ as the 3rd character.
- Display only the following fields (any order that works): Item Code, Item Name, Department
- In the design view, sort by Item Codein ascending order
- Useat least 2 different types of wildcards – see the Access 1 project,tips or class on different types of wildcards.Note: You may have to addthe required quotes yourself instead of letting Access add them for you.
- qry8-SearchTerm:Create a query that lets you enter a search term and find all item names that contain that term.
- Use a parameter and wildcards
- Display only the following fields (any order that works): Item Code, Item Name, Department, Selling Price and Cost
- In the design view, sort by Item Name in ascending order
- Check all queries for reasonable/accurate results.
- Onlyuse the tables you need in each query.If you aren’t displaying/usingany fields from a particular table, don’t include it in aquery.Including unnecessary tables can lead to unexpected results(missing records, duplicated records displayed).
Part 2 Tables
Part 3Lookup Field /Relationships between tables
Part 4 – Create the following queries:
Youare going to create a few queries.Remember – queries take some time toplay with and figure out how they work.You may not get the right resultsfor each one the first time you try it – keep at it!
Note:Your fields can be displayed in any order, as long as the sorting workscorrectly and the results are correct.Name the queries as listed below.
Part 5 – Navigation Form
- Create a report for each query.Give them a name similar to the query, just replace the ‘qry’ with ‘rpt’.
- **When you run the reports, the sorting may be different than when you run the query.For our purposes, this is ok.
- Create a navigation form that runs all of the reports.
- Adjust the report Title Names (within the report) to be more descriptive.
- Feel free to add different background colors.
- Modify your navigation tabs to make them a different shape and color than the default tabs.
- Ifyour first tab is to a report with prompts (parameters), that willprompt you whenever you open the database / navigation form.If thisisn’t what you want, you can put a non-prompting report first or evencreate a form that is just a Item Name and put that first.See theoverview for more on this.
- The navigation form should open automatically when your database opens.
Project Submission Instructions / Notes:
- Theonly way we can fairly grade the projects is if we check for eachrequirement.Please go through the instructions before you submit &be sure you have done each one correctly, so you don’t miss out onpoints.Compare your solution to the project overview.
- Rememberto leave all of the internal file properties intact for the project, ifthey are modified or deleted, the project won’t be accepted.