Select Page
  

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 / DatabaseCreate a blank database and name it LASTNAMEINITIAL_2AC.accdb. tblItemsImport
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. tblSalesImport
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 tablesBe
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 dataUse 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 PriceSort 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 orderYour 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 DepartmentDisplay only the following fields (any order that works): Departmentand the sum of the Units SoldIn 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 SoldIn 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, DepartmentIn 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 wildcardsDisplay only the following fields (any order that works): Item Code, Item Name, Department, Selling Price and CostIn 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 TablesPart 3Lookup Field /Relationships between tablesPart 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 FormCreate 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. access2_data_s19.xlsx access_2_instructions_s19.pdf Unformatted Attachment Preview Bike ID H9 R23 M25 H47 C14 H28 L31 R41 C16 M24 H27 C34 C35 C37 M45 L52 R58 M4 H7 L10 L12 R21 R22 R40 C54 L13 R18 H29 R39 R42 C56 L30 C36 M46 L53 C55 R3 C15 C17 R19 Hybrid Road Mtn. Hybrid Children Hybrid Leisure Road Children Mtn. Hybrid Children Children Children Mtn. Leisure Road Mtn. Hybrid Leisure Leisure Road Road Road Children Leisure Road Hybrid Road Road Children Leisure Children Mtn. Leisure Children Road Children Children Road Category Tea for Two - CR RoadRunner 1004 Eagle MX Runblend 2001 Runkidder 100 - orig Eagle 4 - USA Model Starlight - women Classic 110 Coolest 100 Eagle 4 Runblend 2002 Runkidder 100 - lux Red Rider Girls Green Rider Girls Eagle 2 - USA Model Supreme 350 - child Runroad 4000 - std Eagle 1 Runblend 2000 Runcool 3002 Supreme 350 - men Classic 109 Elegant 03 Twist & Shout - OL Runkidder 100 - std Blue Moon Runroad 4000 - orig Tea for Two - OL Runroad 5000 - le Elegant 04 Coolest 100 Girls Runcool 3003 Coolest 100 Boys Eagle 3 - USA Model Blue Moon LE Red Rider Multi 1002 High Road Red Rider Boys Green Rider Boys Runroad 5000 - mx Name Purchase Cost Selling Price Quantity on Hand Reorder Level YTD Bikes Sold $175.02 $290.95 $411.11 $190.95 $50.95 $451.11 $100.47 $227.49 $69.99 $451.11 $195.95 $60.95 $15.00 $95.47 $411.11 $40.00 $420.95 $300.00 $180.95 $85.95 $50.00 $217.49 $281.52 $500.50 $45.95 $75.29 $425.95 $175.02 $390.95 $281.52 $79.99 $95.95 $79.99 $410.01 $85.29 $15.00 $250.95 $15.00 $95.47 $390.95 $245.03 $420.99 $621.54 $275.99 $75.99 $651.54 $140.66 $340.49 $97.98 $651.54 $300.00 $95.99 $25.50 $133.66 $581.54 $60.00 $540.99 $421.54 $255.99 $135.99 $70.00 $310.49 $300.00 $700.70 $68.99 $105.41 $550.99 $245.03 $495.99 $414.13 $117.98 $160.99 $117.98 $574.01 $165.41 $25.50 $350.99 $25.50 $133.66 $505.99 0 0 0 0 1 1 1 1 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 4 4 4 4 4 4 5 5 5 5 5 6 6 6 6 2 5 2 5 5 2 2 2 5 2 5 5 5 5 2 5 5 2 5 5 5 2 2 2 5 2 5 2 5 2 5 5 5 2 2 5 5 5 5 5 15 12 23 12 43 76 21 12 13 44 9 21 34 34 23 34 12 23 4 33 12 25 34 21 12 53 32 24 9 1 11 23 23 33 956 15 12 15 32 8 L32 L33 R38 M44 L50 C57 R60 R1 R2 M6 R20 H48 R59 M5 M26 H49 L51 H8 L11 R43 Leisure Leisure Road Mtn. Leisure Children Road Road Road Mtn. Road Hybrid Road Mtn. Mtn. Hybrid Leisure Hybrid Leisure Road Supreme 350 - women Blue Moon MX Runroad 4000 - lux Eagle 1 - USA Model Runcool 3004 Green Rider Multi Twist & Shout - BL Classic 108 Nightrun Elegant 02 Eagle 3 - USA Model Twist & Shout - CR Eagle 7 Runroad 5000 - se Eagle 2 Eagle Rage Tea for Two - BL Starlight - child Eagle 7 - USA Model Starlight - men Runroad 1003 $50.00 $80.29 $450.95 $431.11 $105.95 $95.47 $500.50 $207.49 $281.52 $410.01 $500.50 $471.11 $350.95 $411.11 $510.01 $175.02 $80.47 $471.11 $100.47 $270.95 $70.00 $135.41 $599.99 $621.54 $180.99 $133.66 $700.70 $290.49 $394.13 $574.01 $700.70 $681.54 $450.99 $581.54 $721.54 $245.03 $110.66 $681.54 $140.66 $390.99 6 6 6 6 6 6 6 7 7 7 7 7 7 8 8 8 8 9 9 9 5 2 5 2 5 5 2 2 2 2 2 2 5 2 2 2 2 2 2 5 15 33 11 7 12 32 17 23 13 22 11 3 5 21 22 24 12 12 15 2 Order No C2222 A2356 A9842 B1234 C0467 C0467 B2499 B3022 A0205 B1345 C0467 C1111 C3333 C0456 A2357 A9876 A4356 B2499 B3224 C0423 C1111 A0446 A0433 A1456 C0423 C0423 A1314 A9865 Vendor No BSI-1 BSI-5 BSI-3 BSI-4 BSI-11 BSI-11 BSI-7 BSI-8 BSI-9 BSI-10 BSI-11 BSI-1 BSI-2 BSI-3 BSI-4 BSI-5 BSI-6 BSI-7 BSI-8 BSI-9 BSI-10 BSI-11 BSI-1 BSI-7 BSI-8 BSI-9 BSI-5 BSI-11 Bike ID H9 R23 M25 H47 C14 H28 L31 R41 C16 M24 H27 C34 C35 C37 M45 L52 R58 M4 H7 L10 L12 R21 R22 R40 C54 L13 R18 H29 Ship Date Quantity Estimated Transit Arrival Date 04/09/18 9 7 04/23/18 04/26/18 10 8 05/05/18 04/21/18 10 10 04/30/18 03/03/18 10 6 03/07/18 04/08/18 10 8 04/27/18 05/12/18 11 10 05/23/18 05/08/18 11 10 05/19/18 05/12/18 11 9 05/24/18 04/13/18 12 8 05/20/18 03/10/18 12 4 03/11/18 05/04/18 12 7 05/11/18 05/12/18 13 5 05/17/18 04/09/18 13 8 04/23/18 05/04/18 13 8 05/14/18 05/05/18 14 5 05/11/18 05/08/18 15 5 05/13/18 05/01/18 15 5 05/10/18 05/08/18 15 7 05/18/18 04/21/18 15 7 05/02/18 05/01/18 16 8 05/10/18 05/12/18 18 14 05/22/18 03/10/18 16 6 03/19/18 04/26/18 20 5 05/03/18 04/21/18 20 5 04/26/18 03/03/18 20 6 03/09/18 05/08/18 21 5 05/14/18 05/14/18 22 6 05/18/18 05/12/18 22 6 05/15/18 Access 2 Assignment – ISM3011 - Bicycles Page 1 of 3 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 (Modules area). If you need help, use an (optional) Access text and/or review the tutorials as necessary listed in the Overview area. You can also use Google, of course. If you don’t have Access on your computer, see the FAQ area in our Canvas Modules area about using Citrix / apps.usf.edu. BE SURE you review how to save a file to your computer when using apps.usf.edu – there are specific instructions in the FAQ area. You can also use the computers in the USF library with a flash drive. Part 1 – Getting Started / Database • Review the project – look over the data that is provided as well as all the instructions to get familiar with what is required. • Create a blank database and name it your last name followed by your initials and _2AC. For example: WarnerBL_2AC.accdb . Do not use an Access template or older database project to start this project. Part 2 Import • Inventory – Import the inventory information from the Inventory tab within the Excel file ‘Access2_Data_S19.xlsx’ into an Access table (named tblInventory). o The primary key (unique identifier for each record) should be the Bike ID. • Orders – Import the Order information from the Order tab within the Excel file ‘Access2_Data_S19.xlsx’ into an Access table (named tblOrders). . o The primary key should be a combination of the Order No, Vendor No and Bike ID fields (a multiple field primary key). • All tables o Make sure all data types and field sizes are reasonable (not default values). o Add data descriptions for all fields. o No additional fields/tables should be added to the project at any time (no ID fields unless they are also in the Excel data) Part 3 Lookup Field • Make the Bike ID field in the tblOrders table a lookup field. It will look up the Bike IDs in the Inventory table. • When you set up the lookup field, use the lookup wizard and let the users see both the Bike ID and Name in the drop down menu (do not ‘hide the key column’). Sort by the Bike ID field in ascending order. • When a new record is entered into tblOrders, the Bike ID should reference the tblInventory table and give users a drop down menu showing the Bike ID and Name (full or partial display is ok) & let them click on the one they want. • The Bike ID should be the field that is stored and the label for the lookup column should also be Bike ID. • Test your lookup field by adding 2 new records to the tblOrders table. Use a NEW order number and a valid Bike ID. Make up the rest of the data. Delete these records before you continue. • View your database relationships; be sure both tables are displayed. The tblOrders and tblInventory tables should have been joined for you when you created the lookup field. 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! Your queries should work even if the data in the individual fields is changed. All currency fields should be formatted appropriately ($ sign and 2 decimal places). 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. • Qry01-EndingChar o Create a query that displays all Orders that have an Order No that does not end with a 7 or 8. o Experiment with the use of ‘not’ in the criteria. o Display only the following fields (any order that works): Order No, Bike ID, Name, Vendor No o Sort by Name (ascending). o Hints ▪ First create a query that that displays all orders that DO end in a 7 or 8, then modify it by adding ‘NOT’ ▪ Helpful Tips: 1) Basic Wildcards, 2) Using the NOT Operator 3) And / Or Operators • Qry02-Find o Create a query that lets a user enter a letter (or a few letters) and selects all bikes with names that start with that letter (or group of letters). Access 2 Assignment – ISM3011 - Bicycles o o o o Page 2 of 3 Use the parameter feature and wildcards Display only the following fields (any order that works): Bike ID, Name, Category, Purchase cost, and Selling Price. Sort by purchase cost in descending order. Helpful Tips: 1) Basics Wildcards, 2) Parameters and Wildcards • Qry03-FindAll o Create a query that lets the user enter letters and displays all bikes with those letters anywhere in their name. o Use the parameter feature and wildcards o Display only the following fields (any order that works): Bike ID, Name, Category, Purchase cost, and Selling Price. o Sort by purchase cost in descending order. o Helpful Tips: 1) Basics Wildcards, 2) Parameters and Wildcards • Qry04-FindPrice o Create a query that displays all Bikes with a selling price between 300 and 400 dollars. o Display only the following fields (any order that works): Bike ID, Name, Selling Price. o In the design view, sort by Selling Price (ascending), and then by Name (ascending) o You can use >, <, = operators or the BETWEEN operator o Helpful Tips: 1) And / Or Operators, 2) Numeric Query, 3) Between Operator • Qry05-ShipMarch o Create a query that selects all Orders that had a Ship Date in March, 2018. o Display only the following fields (any order that works): Order No, Bike ID, Name, and Ship Date. o Sort by Ship Date (ascending) and then by Bike ID (ascending). o Helpful Tips: 1) And / Or operators, 2) Numeric Criteria 3) Between Operator • Qry06-TotalSold o Create a query that displays the YTD Bikes Sold for each Category o Display only the following fields (any order that works): Category and the sum of the YTD Bikes Sold o Use the Grouping feature in Access to group by Category o Use the formatting feature in the query design view to display YTD Bikes Sold values with commas and no decimal places. o In the design view, sort by the YTD Bikes Sold (descending order). o Helpful Tips: Grouping o Qry07-Wildcards: o Your manager found a ripped document and can only see part of the printed information. o Create a query that displays all Orders with an Order No. containing a 4 as the 3rd character o Display only the following fields (any order that works): Order No, Vendor No, Bike ID. o In the design view, sort by Bike ID in ascending order o Use at least 2 different types of wildcards – see 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. o Helpful Tips: Advanced Wildcards • • Note: Check your results/sorts carefully. 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. NOTE: Be sure that Access is closed before you upload your file to Canvas. Access 2 Assignment – ISM3011 - Bicycles Page 3 of 3 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 project, 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 internal 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 these 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. Also 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 until the last days, I may not be able to do either. ... Purchase answer to see full attachment

Don't use plagiarized sources. Get Your Custom Essay on
Getting Started Database Item Code Field
Just from $10/Page
Order Essay

Order your essay today and save 10% with the discount code ESSAYHSELP