Getting Started Database Item Code Field

/in /by

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.

  • tblItems
    • 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.
  • tblSales
    • 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).
  • All tables
    • 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.
  • Submitting:
    • 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.
 
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.