access 1 case 1

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:

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 nameconvention, and call this initial file <first name-last name>_Access1_ERP.
3. 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)

1. Question: open Windows Explorer application, look in your folder: Do you see the filename you created? ____ (Yes/No)
2. Question: Do you see a second file with same name? ____Yes/No
3. 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)

1. Use top “Create” menu, select “Table” and enter in field names and data tables as below.
2. 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…

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)

1. Select for “When you select a row… you can store a value from that row…” as ‘ven-no’.
2. 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:

1. Close any open tables.
2. Go to top menu “DATABASE TOOLS” Tab and select ‘Relationships’.
3. Add all four tables to the screen by selecting them and hitting Enter.
4. 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.)
5. One relationship exists since you already setup a lookup between Vendor and Purchaseorder 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).

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:

1. When done, ‘Save As’ and give the entire database an updated name-version such as“<– <first name-last name>_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).
2. Close Access database (and note that lock file disappears.)
3. With Access application Closed, copy the ACCESS database file and save as a newversion called – <first name-last name>_Access1_ERP_v2.accdb before you startyour Queries below.

11. Only use this v2 file to do queries below, and keep original file <first name-last

name>_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

1. 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
2. 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.
3. 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 – <first name-last name>_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