Shop Sales Database

Part 1 Getting Started / Database

Create a blank database and name it your last name followed by your initials and _2AC (ex. WarnerBL_2AC) Note: .mdb is the extension Access will add to your database file name if you are using Access2003, for later versions it will be.accdb.

Part 2 Tables

• tblInventory
  • Import the distributor info from the worksheet named Inventory within the Excel file, Access 2 F16.xlsx into an Access table named tblInventory.
  • The primary key should be Item ID.
• tblOrders
  • Import the ORDER information from the worksheet named ORDERS within the Excel file Access 2 F16.xlsx into an Access table (named tblOrders).
  • Keep the fields in the same order in the table as they are in the spreadsheet.
  • Since some orders include more than one Item ID, the primary key (unique identifier for each record) should be a combination of Order ID and Item ID (a multiple field primary key).
  • Add data validation to ensure that EstTransit field cannot contain a negative number. Display an error message if someone tries to enter an EstTransit amount less than zero. When you save your table after adding data validation, you may get a message that Access is checking the validation of the existing data. This is normal. Test your data validation by adding a new record with a negative EstTransit once youve tested it, change it to a positive number and delete the record.
  • Set Distributor Region so that it is a required field. This means that when someone enters a new record, they cant leave the Distributor Region field blank.
• All tables
  • Make sure all data types AND field sizes are reasonable for the data (not default) 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 (cut off) any existing data.
  • Add data descriptions for all fields.
  • No additional fields/tables should be added to the project at any time.

Part 3 Lookup Field /Relationships between tables

• Make the Item ID field (in tblOrders) a lookup field. There is an online video on the Tips page.
• When you set up the lookup field, use the lookup wizard and let the users see both the Item ID and Item Name (sort by Item ID) in the drop down menu (do not hide the key column).
• When a new record is entered into tblOrders, the Item ID should reference the tblInventory table and give users a drop down menu showing the Item ID and Item Name (full or partial display is ok) & let them click on the one they want.
• The Item ID should be the field that is stored and the label for the lookup column should also be Item ID.
• Test your lookup field by adding 2 new records to the tblOrders table. Use a valid Item ID and Distributor Region and make up the rest of the data. You can delete these records after you see that the Lookup field works.
• View your database relationships; be sure both tables are displayed. The tblOrders and tblInventory table should have been joined for you when you created the lookup field.

Part 4 Create the following queries.

Name the queries correctly (see below) and unless otherwise noted the fields can be sorted as youd like and can be displayed in any order. Just check that your results are correct.

• qry01-ItemG - Create a query that uses the appropriate criteria to display all records with items names that start with a G. Use a wildcard and display the item name, item id and category. Sort by category in ascending order, then by item name in ascending order.
• qry02-Dist - Create a query that lets a user enter a Distributor Region and display all orders for that distributor. Use the parameter feature and display the Order ID, Distributor Region, Item ID and Item Name.
• qry03-StartSearch - Create a query that will allow the end user to enter a letter and display all the Item Names that start with that letter. Use a parameter and wildcard and display the item name, item id and category. b
• qry04-Applicance - Create a query that selects all store names (On Sale At field) that contain the word Appliance. Use wildcards and display the item name, item id, category and On Sale At fields. Once this is working, format the datasheet view so that it has a background color and colored gridlines.
• qry05-WordSearch Create a query that will allow someone to enter a letter or word & displays all store names (On Sale At field) that contain that letter or word. Use wildcards and a parameter and display the item name, item id, On Sale At and category fields.
• qry06-SummerDates - Create a query that displays all Orders with an Order Date during either of the following two periods: July 15-25, 2016, August 15-25, 2016. Display the following fields: Order ID, Order Date, Item ID, Item Name, and Distributor Region. Sort by Order Date (ascending). Use the between and and or or operators in your solution.
• qry07-DateSearch - Create a query that accepts input for both a starting OrderDate and then an ending OrderDate and displays all Order records within that date range (including start/end dates). Use parameters and either inequalities (think algebra) or between. Display the following fields: Order ID, Order Date, Item ID and Distributor Region. Sort by Order Date (ascending)
• qry08-ProfitCk Create a query that calculates and displays the profit for each item in Inventory. Create a new field for the Profit and another field called Check Item. In the Check Item field, display ** (2 asterisks) if the profit is greater than $50.00 (use the IIF function for this). Display the Item ID, Item Name, Selling Price, Cost, Profit and Check Item fields. **Hint: when you create the Check Item field, dont use the field name profit, instead use the profit equation.
• qry09-Sales - Use Grouping and display the Distributor Region and total Order Quantity for that distributor. Display quantities with no decimal places and add a comma if over 999.
• qry10-Top20- Create a query that shows the top 20 selling items last year. Use the top value feature and include the following fields: Item ID, Item Name and Sold Last Year. Sort by Item ID (ascending) and Sold Last Year (descending)
• qry11-Wildcards: Create a query that displays all Orders with an Order ID containing a 2 as the 2nd character after the hyphen Display fields: Order ID, Distributor and Item ID. Use at least 2 different types of wildcards (*and ?, * and # or, ? and #)
• Check all queries for reasonable/accurate results and be sure your query will work even if additional records were added.
• Only use the tables you need in each query. If you arent displaying/using any fields from a particular table, dont include it in a query. Including unnecessary tables can lead to unexpected results.

Part 5 - Navigation Form

Youll need Access 2010 or later for this part. When you run your reports from the navigation form, any query / report that included parameters will still prompt the user for input.

First, create a report for each query. Include all fields from the query.

• Name the reports so that they correspond to the query name, but start with rpt instead of qry. For example, the report for qryWildcards should be named rptWildcards.
• You do not have to adjust the sorting in the reports.
• Be sure the headings/details line up and no headings/details are cut off.
• You dont have to do any special formatting except to change the title start with rpt (vs qry). However, if you want to format them, feel free
• If a query has a parameter, the report will as well and will prompt you to enter the criteria.
• Check out the tip on quick reports.

Second, create the navigation form.

• Create a navigation form to display tabs for all reports. See the TIP on navigation forms.
• Change the shape and style of the navigation buttons so they are not left as the default.
• Have your navigation form start up when the database opens.