BU 250 Kansas State University Access Vendor Data Table Case Program Just go to the attachments I included DO FIRST TO PREPARE for the Assignment Down

BU 250 Kansas State University Access Vendor Data Table Case Program Just go to the attachments I included

DO FIRST TO PREPARE for the Assignment

Download the “Access-1_Case_Inputs.zip” from D2L, save on local hard drive or your USB. Unzip that file: Open Windows Explorer, find the ZIP file, right click on it and select ‘Extract all’ option so all three files are uncompressed and visible in a new folder (of same name as the zip file, so be sure to use correct ‘unzipped’ folder). The zip file should contain three (3) Excel files to use in the Step-1: Design table and Import data below.

Inventory.xlsx Market_Report.xlsx PurchaseOrder.xlsx

Start and Save Blank Access Database file using MS ACCESS 2016 Application:

When you open MS Access application: Select “New Blank Database”
Give it a File name (lower Right) and select a File location. Use good nameconvention, and call this initial file _Access1_ERP.
Create the file on your local hard drive (desktop, or on USB drive in PC). Note: do notsave into your shared drive over the network, as this might give you problems later.

Self-Reflection Class Questions (you do not turn in any written answer)

Question: open Windows Explorer application, look in your folder: Do you see the filename you created? ____ (Yes/No)
Question: Do you see a second file with same name? ____Yes/No
Question: What is the ‘type’ and purpose of the second file and when does it exist?_________________________________________

Step-1: Create Vendor Table: Manually create a new Table, “Vendor” Table.
It is always best to create the Table Design first, then later to type in data (or import data if have an outside file). If you do import of data, always recheck all data types and other table design settings, and setup primary keys.

In main menu tab select “Create”, then select “Table”

Type in the field names, data

types and field size as you defining each field.

Field Size – shown in Field Properties when field name selected.

Field Name

ven-No

ven-Name ven-Address ven-City ven-State

Data Type

Short Text 3 ?? PRIMARY KEY Field * Short Text 20
Short Text 30
Short Text 20

Short Text 20

When designing a table, it must have a primary key (of one field or several fields).
Set the Primary Key for Vendor Table: Identify the table’s primary key by clicking to the immediate left of the Ven-No field name, and select KEY from popup menu. This sets the primary key of vendor table as ‘ven-no’

Important Note: Any other table with a foreign key, will have to have exactly the same Data Type and Field Size as the related Primary Key, so take note of all primary key field’s type and size, and be sure the foreign key is defined exactly the same. Otherwise, relationships and queries will give you trouble later.

3

BU-250 Access-1 (Spring 2020)

.

Step-2. In Data Sheet view: Type in Vendor Data into the Vendor Table – type the following data into your Vendor table (using “Datasheet View”).

Vendor Data

Step-3. Design a Table, then import it’s data (that was already formatted in the same way as the table design)

Use top “Create” menu, select “Table” and enter in field names and data tables as below.
For each field, be sure to also set the ‘Field Size’ as shown below in Field Properties area (Field size is only shown for field name currently selected).Inventory Table Format:

Field Name

Material

SLoc Description Unit
Stock

Data Type Field Size

Short Text

Short Text Short Text Short Text

Number

6 ?? Primary KEY Field 2

50
5 Double

III. Set the primary key field(s), and recheck (& fix) the formats for all fields defined in this table, which should match the above.
To establish the primary key, you must set the correct field(s) as showing a KEY icon for the primary key(s) field(s), as shown below. When using more than one field in a table’s primary key (Compound or concatenated keys), then all fields must be set in Design view AT THE SAME TIME (highlight all, right click between two & select KEY).

4

BU-250 Access-1 (Spring 2020)

IV. To Import Data: use top “External Data” menu, select Excel, and browse to input file for this table: i. e. the related ‘Inventory.xlsx’ file contains data to fill this Inventory table. As you are going to add new data to a defined table, select radio button “Append a copy of the records to the table, as shown below.

Step-4: Repeat the Table Design step and Import data to populate the following table designs. Note: *To set 2 or more fields as one primary key – You start with no primary key field set. Then select box at left side of all field names for the key. Have cursor sitting between the fields (See a line with arrows) then right click select KEY. All should be turned on as KEYS at same time for this to work. Else, unselect all and try again. (You cannot set one field as key and then add a second field to key. It will just switch from one to other and back and forth).

Market Table Format:

Field Name

Material

Area Rnd/Day Qty Price

Data Type Field Size

Short Text 6

Short Text 5

Short Text 30

Number Double Number Double

?? PRIMARY KEY Field * ?? PRIMARY KEY Field * ?? PRIMARY KEY Field *

PurchaseOrder Table Format:

Field Name

Order Material Vendor

Data Type Field Size

Short Text
Short Text
Short Text 3

30 ?? PRIMARY KEY Field * 6 ?? PRIMARY KEY Field *

5

BU-250 Access-1 (Spring 2020)

Status Goods Quantity Unit Cost

Short Text 5 Short Text 50

Number Number

Double Double

Next do the Import Data step for each new table designed above. Be sure to select the correct table name for selected import data table. They must match, as shown below.

Then check to confirm all data types and field size.
Potential Trouble: If these are not set exactly per design above, then your creation of table relationships could fail, and prevent further progress on the Database.

The “External Data” menu and selecting a file with data is a step that saves you typing in data. by using external data stored in Excel .xlsx format, but remember: after the import, always recheck all data types and other table design settings, and setup primary keys.

Step-5. Adding ‘Vendor’ type as a ‘Lookup field’ type in the Purchase “Order” table:

Next step is to add a field lookup between PurchaseOrder table and vendor table. Purpose: This will help avoid errors in data entry into the table (in datasheet mode), as for the Vendor field, this will provide a dropdown menu listing all valid Vendors by name and primary key from Vendor table.

How To: Set the “Vendor” Field in the Purchase Order Table as a “lookup field” to help ensure Purchase order table must only contain vendors that are valid (i.e. already are contained in the Vendor table).

6

BU-250 Access-1 (Spring 2020)

Open the purchase order table in design view. Change the vendor field’s data type to a lookup field by selecting “lookup wizard”.

Follow these lookup wizard steps…

select “I want the lookup field to get the value from another table”
select “Vendor table”
select two fields (by clicking “>” to move to right side); “ven-no, ven-name”

7

BU-250 Access-1 (Spring 2020)

4. select for “You can sort records…” as” ‘ven-name’ and leave as Ascending.

5. unclick “hide the primary key field”

8

BU-250 Access-1 (Spring 2020)

Select for “When you select a row… you can store a value from that row…” as ‘ven-no’.
Leave “What label would you like for your lookup field?” as Vendor, and select the Checkbox for ‘enable Data Integrity’ and select checkbox ‘cascade delete’.

Save this table. Close it. Because future steps will complain/halt if you have objects (tables, queries… open).

You can then use the Database Tools, Relationships menu to check for the correct relationship between the foreign key “Material” in the PurchaseOrder table and the Primary Key of “ven-no” in the vendor table. Is should look like the below Table Relationships:

9

BU-250 Access-1 (Spring 2020)

Step-5. In Menu: “Database Tools”, “Relationships” you will reconfirm correct Primary Keys are set, and build Relationships between the four tables.

Next you will setup two more relationships by clicking on the Foreign Key field (listed below), and drag/drop onto the appropriate Primary Key for each table as follows:

IMPORTANT: When creating relationships: Be sure you click on the foreign key and drag it over the primary key in another table.

General Instructions for Creating Relationships: Use Menu Tab “Database Tools” and command ‘Relationships’ to set the ‘Foreign Key-to-Primary Key’ relations as follows:

Close any open tables.
Go to top menu “DATABASE TOOLS” Tab and select ‘Relationships’.
Add all four tables to the screen by selecting them and hitting Enter.
PRE-CHECK: Reconfirm that the primary keys also match image below. (If they do not,avoid future problems – go back to Table Design view, and earlier step to fix primary keysettings before proceeding.)
One relationship exists since you already setup a lookup between Vendor and Purchaseorder tables, per above image.
Setup the remaining two relationships: general method is to click on the foreign key,drag from it to the Primary Key in the correct related table and unclick. Unclick and you should get popup box. To create a database the checks for correct primary key data being used in other tables as foreign keys, check boxes for Referential Integrity, and cascade updates, and cascade deletes.
After the set above, there should then be a connection ‘relationship’ line between Foreign Key and the appropriate primary key (as shown in relationship image on next page).

Foreign Key [Tablename]![Field]

Table Name

Primary Key(s) field

[PurchaseOrder]![Material]

Inventory

Material

[Market]![Material]

Inventory

Material

10

BU-250 Access-1 (Spring 2020)

Action: First Relationship to setup: Market table’s foreign key of “Material” field as related to Inventory table’s primary key: Material. Note: all tables must be closed, or error will halt setup.

Action: Second relationships setup between Purchase Order table and Inventory table. In the Purchase Order table, Click your left mouse on ‘Materials’ and drag to Inventory table “Material”. Unclick and you should get popup box. Check boxes for Referential Integrity, and cascade updates, and cascade deletes.

Note that Prior Action of Lookup field already created the relationship between Vendor and PurchaseOrder tables when you created the lookup field in the PurchaseOrder table.

11

BU-250 Access-1 (Spring 2020)

When completed, your Primary Keys and relationships should look as follows:

When done, ‘Save As’ and give the entire database an updated name-version such as“_Access1_ERP.and then click upper right “X” inside the main window to Close relationships tab (do not click the outer “X” that closes the ACCESS application).
Close Access database (and note that lock file disappears.)
With Access application Closed, copy the ACCESS database file and save as a newversion called – _Access1_ERP_v2.accdb before you startyour Queries below.

11. Only use this v2 file to do queries below, and keep original file _Access1_ERP.accdb untouched as a backup file.
12. Each time you stop and then restart to work on new queries, it is best to copy last

work and save as new version number, e.g. _v3 as next time you add to queries. Then you will have a backup with prior work, in case of breaking something while working, or in case of file corruption or loss.

Step-6. Create queries to answer the following questions. In CREATE menu tab, use Query Design

To create a query using QBE (Query by Design), click on CREATE, Query Design (not Query Wizard) and hold Shift key to highlight the necessary tables & click ADD
To build the following queries (to answer questions about the data) then drag/drop fields of interest into grid at bottom. Set criteria to filter.
Click on “!” RUN to run the query and see appropriate data from table(s)

•

Query Design HINTS: Only include tables that are needed for the query, no more, no less!

Missing, Unresolved Field: If you get a popup window asking for data to use for some

field [tablename]![fieldname], then that means the query did not have access to the

correct field in the database. You need to include that table and field in the query

design.

• Extra Unneeded Tables: If you get too many repeating rows as output from a query,

then you likely have too many tables in the ‘Show Tables’ in the query design view.

Remove any duplicate and any un-needed tables to show minimal set of tables, only as

needed in the query selections.

• Group By: you many need to set the ‘totals’ row as visible in Query Design View, and

select default of ‘Group By’ to ensure similar entries are collected and displayed as one

output.

12

BU-250 Access-1 (Spring 2020)

Query 1: Show the stock quantity of product between 1000 and 2000, inclusive. Stock, Description and Materials.

Query Design:

Query 2: Using the Market Table – Show all products sold at market at or above $24. Use the fields of Material, Area, Rnd/Day, Quantity (QTY) and Price, but only records with Price being greater than or equal to $24.
Query Design:

Query 3: Display for 2 Materials ZZ-B0#: Last Day Inventory Value by Material & Area:

Only display Materials of ZZ-B02, and ZZ-B03, and only for Round 02’s last day i.e. day 20 (specified in database as “02/20”), determine the total value of the total inventory quantity on that last day based on current price at that Rnd/Day with criteria “02/20”.

Show these fields in your query: Material, Area, Stock, Rnd/Day (with criteria of “02/20”), and the created new field called “Stock Value” containing the expression of inventory times market price,

Use

HINT: You only need two tables (Inventory and Market) shown in this query design. And, be sure your expression includes the full name containing both the table and field names in format: [tablename]![field].

Use the Rnd/Day with criteria “02/20 but Unclick “Show” box as the option to Not display

the Rnd/Day field . Do this as this report is named to only display Day20, in output (i.e.

Unclick the ‘Show’ box so the criteria is in effect but that field does not appear in the

output).

“Builder” option in query design to create the mathematical calculation needed, by

creating expression: Stock Value: [Inventory]![Stock] * [Market]![Price].

13

BU-250 Access-1 (Spring 2020)

Query Design:

Query 4 – All products, Summary of Last-Day Inventory: Total Value by Area

This query is like Query 3 but it a) includes all products for ZZ-B product group and b) it summarizes (sums) all Stock Values for each area (N, S and W) so the output only shows a total stock for each of the 3 areas (No., So., and We.) :

Show fields: Area, the new calculated field called “Stock Value” with the expression created using ‘Build’ option, i.e. Use “Build” option in query to create the expression:

Stock Value: [Inventory]![Stock] * [Market]![Price]

Query Design: HINT: Only use the two ‘show tables’ of Market and Inventory, otherwise you will get total value of all purchase orders, etc. for timeframe.

Again

You need to include Rnd/Day with

criteria of 02/20 – but do not show the Rdn/Day field on output.

14

BU-250 Access-1 (Spring 2020)

Query-5. AVG Last-Day Inventory Value: Average Inventory Stock by material

Determine the average amount of inventory stock per material (product) across all three areas (No., So., and We.). Show fields of Material, and the new calculated field called AverageStockValue, and use Rnd/Day=02/20 – but do not show the Rdn/Day field on output. AverageStockValue should be created by using the familiar expression:

[Inventory]![Stock] * [Market]![Price] And using the ‘total line’ function of Average.

Query Design: HINT: Only use the ‘Show tables’ required for this query, no extras. For the AverageStockValue expression, select the AVG option in Total line, as below.

After running and saving the resulting query will look like below (ACCESS puts the function into the expression):

15

BU-250 Access-1 (Spring 2020)

Query-6 Market Average Price in West Area (for all materials)

Determine the one overall average price of each material (product) across all areas (No., So., and We.) and all times (no need to include Rnd/Day, as all data will be used).

Show fields of Area, Material, and the Price field using the Total line setting of “avg”. Other fields should show the total line as ‘Group By”.

Query Design:

Last Step: Save/Compress in ACCESS, close ACCESS application, and Zip your completed assignment file as prep to be submitted.

When finished, select FILE menu and click on option “Compact & Repair” as this step compresses all the past work done in the database (since databases store every step for possible ‘roll-back to prior state’). This step reduces the file size and corrects/repairs any data inconsistencies. Save the file, and Close your ACCESS file and the ACCESS application. Be sure the LOCK file, .laccdb closes and disappears on its own.

CLOSE YOUR ACCESS FILE before you zip it and submit. Copy the file to a safe backup location. Then change the copy of the file into a ZIP file, on your hard drive. To

do this: right click on closed ACCESS file name for this assignment, and select “send to: Compressed (Zipped) Folder”. The file name will stay the same but the file type will become .ZIP file and can safely be attached to emails.

Submit your finished Access Database – _Access1_ERP_v#.accdb
(file name example: Jane-Doe_Access1_ERP_Case_v3.accdb) to the D2L Dropbox the deadline.

ACCESS Lock file must be gone otherwise your file will be

permanently locked and you cannot re-open the ACCESS file.

16 BU-250 Access-1 (Spring 2020)
DO FIRST TO PREPARE for the Assignment
Download the “Access-1_Case_Inputs.zip” from D2L, save on local hard drive or your
USB. Unzip that file: Open Windows Explorer, find the ZIP file, right click on it and select
‘Extract all’ option so all three files are uncompressed and visible in a new folder (of same
name as the zip file, so be sure to use correct ‘unzipped’ folder). The zip file should contain
three (3) Excel files to use in the Step-1: Design table and Import data below.
Inventory.xlsx
Market_Report.xlsx
PurchaseOrder.xlsx
Start and Save Blank Access Database file using MS ACCESS 2016 Application:
1. When you open MS Access application: Select “New Blank Database”
2. Give it a File name (lower Right) and select a File location. Use good name
convention, and call this initial file _Access1_ERP.
3. Create the file on your local hard drive (desktop, or on USB drive in PC). Note: do not
save into your shared drive over the network, as this might give you problems later.
Self-Reflection Class Questions (you do not turn in any written answer)
4. Question: open Windows Explorer application, look in your folder: Do you see the file
name you created? ____ (Yes/No)
5. Question: Do you see a second file with same name? ____Yes/No
6. Question: What is the ‘type’ and purpose of the second file and when does it exist?
_________________________________________
Step-1: Create Vendor Table: Manually create a new Table, “Vendor” Table.
It is always best to create the Table Design first, then later to type in data (or import data if have
an outside file). If you do import of data, always recheck all data types and other table design
settings, and setup primary keys.
In main menu tab select “Create”, then select “Table”
Type in the field names, data types and field size as you defining each field.
Field Name
ven-No
ven-Name
ven-Address
ven-City
ven-State
Data Type
Short Text
Short Text
Short Text
Short Text
Short Text
Field Size – shown in Field Properties when field name selected.
3
PRIMARY KEY Field *
20
30
20
20
When designing a table, it must have a primary key (of one field or several fields).
Set the Primary Key for Vendor Table: Identify the table’s primary key by clicking to the
immediate left of the Ven-No field name, and select KEY from popup menu. This sets the
primary key of vendor table as ‘ven-no’
Important Note: Any other table with a foreign key, will have to have exactly the same Data
Type and Field Size as the related Primary Key, so take note of all primary key field’s type and
size, and be sure the foreign key is defined exactly the same. Otherwise, relationships and
queries will give you trouble later.
3
BU-250 Access-1 (Spring 2020)
.
Step-2. In Data Sheet view: Type in Vendor Data into the Vendor Table – type the following
data into your Vendor table (using “Datasheet View”).
Vendor Data
Step-3. Design a Table, then import it’s data (that was already formatted in the same way
as the table design)
I.
II.
Use top “Create” menu, select “Table” and enter in field names and data tables as
below.
For each field, be sure to also set the ‘Field Size’ as shown below in Field Properties
area (Field size is only shown for field name currently selected).
Inventory Table Format:
Field Name
Data Type Field Size
Material
Short Text
6
Primary KEY Field
SLoc
Short Text
2
Description
Short Text
50
Unit
Short Text
5
Stock
Number
Double
III.
Set the primary key field(s), and recheck (& fix) the formats for all fields defined in this
table, which should match the above.
To establish the primary key, you must set the correct field(s) as showing a KEY icon for
the primary key(s) field(s), as shown below. When using more than one field in a table’s
primary key (Compound or concatenated keys), then all fields must be set in Design
view AT THE SAME TIME (highlight all, right click between two & select KEY).
4
BU-250 Access-1 (Spring 2020)
IV.
To Import Data: use top “External Data” menu, select Excel, and browse to input file
for this table: i. e. the related ‘Inventory.xlsx’ file contains data to fill this Inventory
table. As you are going to add new data to a defined table, select radio button “Append
a copy of the records to the table, as shown below.
Step-4: Repeat the Table Design step and Import data to populate the following table
designs. Note: *To set 2 or more fields as one primary key – You start with no primary key
field set. Then select box at left side of all field names for the key. Have cursor sitting between
the fields (See a line with arrows) then right click select KEY. All should be turned on as KEYS
at same time for this to work. Else, unselect all and try again. (You cannot set one field as key
and then add a second field to key. It will just switch from one to other and back and forth).
Market Table Format:
Field Name
Material
Area
Rnd/Day
Qty
Price
Data Type Field Size
Short Text
6
Short Text
5
Short Text
30
Number
Double
Number
Double
PRIMARY KEY Field *
PRIMARY KEY Field *
PRIMARY KEY Field *
PurchaseOrder Table Format:
Field Name
Order
Material
Vendor
Data Type Field Size
Short Text
30
Short Text
6
Short Text
3
PRIMARY KEY Field *
PRIMARY KEY Field *
5
BU-250 Access-1 (Spring 2020)
Status
Goods
Quantity
Unit Cost
Short Text
Short Text
Number
Number
5
50
Double
Double
Next do the Import Data step for each new table designed above. Be sure to select the
correct table name for selected import data table. They must match, as shown below.
Then check to confirm all data types and field size.
Potential Trouble: If these are not set exactly per design above, then your creation of table
relationships could fail, and prevent further progress on the Database.
The “External Data” menu and selecting a file with data is a step that saves you typing in data.
by using external data stored in Excel .xlsx format, but remember: after the import, always
recheck all data types and other table design settings, and setup primary keys.
Step-5. Adding ‘Vendor’ type as a ‘Lookup field’ type in the Purchase “Order” table:
Next step is to add a field lookup between PurchaseOrder table and vendor table. Purpose:
This will help avoid errors in data entry into the table (in datasheet mode), as for the Vendor
field, this will provide a dropdown menu listing all valid Vendors by name and primary key from
Vendor table.
How To: Set the “Vendor” Field in the Purchase Order Table as a “lookup field” to help
ensure Purchase order table must only contain vendors that are valid (i.e. already are
contained in the Vendor table).
6
BU-250 Access-1 (Spring 2020)
Open the purchase order table in design view. Change the vendor field’s data type to a
lookup field by selecting “lookup wizard”.
Follow these lookup wizard steps…
1. select “I want the lookup field to get the value from another table”
2. select “Vendor table”
3. select two fields (by clicking “>” to move to right side); “ven-no, ven-name”
7
BU-250 Access-1 (Spring 2020)
4. select for “You can sort records…” as” ‘ven-name’ and leave as Ascending.
5. unclick “hide the primary key field”
8
BU-250 Access-1 (Spring 2020)
6. Select for “When you select a row… you can store a value from that row…” as ‘ven-no’.
7. Leave “What label would you like for your lookup field?” as Vendor, and select the
Checkbox for ‘enable Data Integrity’ and select checkbox ‘cascade delete’.
Save this table. Close it. Because future steps will complain/halt if you have objects (tables,
queries… open).
You can then use the Database Tools, Relationships menu to check for the correct
relationship between the foreign key “Material” in the PurchaseOrder table and the Primary Key
of “ven-no” in the vendor table. Is should look like the below Table Relationships:
9
BU-250 Access-1 (Spring 2020)
Step-5. In Menu: “Database Tools”, “Relationships” you will reconfirm correct Primary
Keys are set, and build Relationships between the four tables.
Next you will setup two more relationships by clicking on the Foreign Key field (listed below),
and drag/drop onto the appropriate Primary Key for each table as follows:
Foreign Key [Tablename]![Field]
[PurchaseOrder]![Material]
[Market]![Material]
Table Name
Inventory
Inventory
Primary Key(s) field
Material
Material
IMPORTANT: When creating relationships: Be sure you click on the foreign key and drag
it over the primary key in another table.
General Instructions for Creating Relationships: Use Menu Tab “Database Tools” and
command ‘Relationships’ to set the ‘Foreign Key-to-Primary Key’ relations as follows:
1.
2.
3.
4.
Close any open tables.
Go to top menu “DATABASE TOOLS” Tab and select ‘Relationships’.
Add all four tables to the screen by selecting them and hitting Enter.
PRE-CHECK: Reconfirm that the primary keys also match image below. (If they do not,
avoid future problems – go back to Table Design view, and earlier step to fix primary key
settings before proceeding.)
5. One relationship exists since you already setup a lookup between Vendor and Purchase
order tables, per above image.
6. Setup the remaining two relationships: general method is to click on the foreign key,
drag from it to the Primary Key in the correct related table and unclick. Unclick and you
should get popup box. To create a database the checks for correct primary key data
being used in other tables as foreign keys, check boxes for Referential Integrity, and
cascade updates, and cascade deletes.
7. After the set above, there should then be a connection ‘relationship’ line between
Foreign Key and the appropriate primary key (as shown in relationship image on next
page).
10
BU-250 Access-1 (Spring 2020)
Action: First Relationship to setup: Market table’s foreign key of “Material” field as related to
Inventory table’s primary key: Material. Note: all tables must be closed, or error will halt
setup.
Action: Second relationships setup between Purchase Order table and Inventory table. In
the Purchase Order table, Click your left mouse on ‘Materials’ and drag to Inventory table
“Material”. Unclick and you should get popup box. Check boxes for Referential Integrity, and
cascade updates, and cascade deletes.
Note that Prior Action of Lookup field already created the relationship between Vendor and
PurchaseOrder tables when you created the lookup field in the PurchaseOrder table.
11
BU-250 Access-1 (Spring 2020)
When completed, your Primary Keys and relationships should look as follows:
8. When done, ‘Save As’ and give the entire database an updated name-version such as
“
Purchase answer to see full
attachment