The objective is to create a Microsoft Access file as mentioned in the attachment. The instructions are attached below and pasted below too.
Part 1 â€“ Getting Started / Database
Create a blank database and name it LASTNAMEINITIAL_2AC.accdb.
Import the Item information from the Items tab within the Excel file Access2_Data_F18.xlsx into an Access table.Name the table tblItems.
The primary key should be the Item Code.
Use data validation to ensure no one can enter a Selling Price greater than $100 or less than 0.Display an error message if either situation occurs.Test this by trying to enter an invalid Selling Price into table.
Use data validation to make Item Name a required field.This means the Item Name cannot be left blank when a new item is entered into the table.
Import the sales info from the Sales tab within the Excel file Access2_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).
Be sure the field sizes are appropriate (donâ€t use the default sizes), the data types are appropriate, and there is a data description for each field.
No additional fields/tables should be added to the project.
Make the Item Code field (in tblSales) a lookup field.This will join your two tables, so donâ€t join them before creating the lookup field.
When you set up the lookup field, start in the tblSales table and use the lookup 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 key columnâ€).
When a new record is entered into tblSales, the Item Code should reference the tblItems table and give users a drop down menu showing 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.
Test your lookup field by adding 2 new records to the tblSales table.Use a valid Item Code & Store ID and make up the rest of the data.
View your database relationships; be sure both tables are displayed.The tblSales and tblItems table should have been joined for you when you created 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 notes all 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
In the design view, sort by Department (ascending order), then by Item Name (ascending) and within that by Units Sold (descending order).
Create a new field called UnitsCheck.For each record, if the Units Sold is less than or equal to 50, this field should display a message â€œ**Small Saleâ€.If the Units Sold is greater than 50, donâ€t display anything.Use the 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
Use at least 2 different types of wildcards â€“ see the Access 1 project, tips or class on different types of wildcards.Note: You may have to add the 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.
Only use the tables you need in each query.If you arenâ€t displaying/using any fields from a particular table, donâ€t include it in a query.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: You are going to create a few queries.Remember â€“ queries take some time to play with and figure out how they work.You may not get the right results for 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 works correctly 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.
If your first tab is to a report with prompts (parameters), that will prompt you whenever you open the database / navigation form.If this isnâ€t what you want, you can put a non-prompting report first or even create a form that is just a Item Name and put that first.See the overview for more on this.
The navigation form should open automatically when your database opens.
Project Submission Instructions / Notes:
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.
Remember to leave all of the internal file properties intact for the project, if they are modified or deleted, the project wonâ€t be accepted.
Are you looking for a similar assignment? Order now for an Amazing Discount! Use Discount Code “Newclient” for a 15% Discount!
create a microsoft access file as required 1 .