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
