Compiled by Inst Anteneh.
SOl
Lucy college Harar campus
Department of HNS
MS Access Lab Exercise: Simple Inventory Management
Database Goal:
To manage Products and the Vendors who supply them, establishing a One-to-Many
Relationship (one vendor supplies many products).
Step 1: Create the Tables (The Foundation)
First, you need to create two tables in Design View.
Table 1: Vendors (The "One" Side)
1. Open Microsoft Access and create a new Blank Database.
2. Click the Create tab, then select Table Design.
3. Enter the following fields:
Field Name Data Type Key/Property Description
VendorID AutoNumber Primary Key Unique identifier for the vendor.
VendorName Short Text Field Size: 50 Name of the supplier company.
ContactPhone Short Text Field Size: 15 Phone number of the contact person.
4. Save the table as Vendors.
5. Switch to Datasheet View and enter at least three records (e.g., V001: Tech Supply,
V002: Office Goods, V003: Parts Depot).
Table 2: Products (The "Many" Side)
1. Click the Create tab, then select Table Design.
2. Enter the following fields:
Field Name Data Type Key/Property Description
ProductID AutoNumber Primary Key Unique identifier for the product.
ProductName Short Text Field Size: 100 Name of the product (e.g., Keyboard, Monitor).
UnitPrice Currency Required: Yes Cost per unit.
Field Size:
StockQuantity Number Current number of items in stock.
Integer
Must match the Data Type of the Primary Key
VendorID Number (Foreign Key)
in the Vendors table.
3. Save the table as Products.
4. Switch to Datasheet View and enter at least five records, making sure the VendorID
values correspond to the records you entered in the Vendors table (e.g., assign V001 to
three products and V002 to two products).
Step 2: Create the Relationship (Connecting the Data)
1. Close both the Vendors and Products tables.
2. Go to the Database Tools tab and click Relationships.
3. In the Show Table dialog box, add both the Vendors and Products tables.
Compiled by Inst [Link]
4. Create the Link: Click and drag the VendorID field (the Primary Key) from the Vendors
table and drop it onto the VendorID field (the Foreign Key) in the Products table.
5. In the Edit Relationships box that appears, check the box for "Enforce Referential
Integrity".
6. Click Create. You should see a line with a '1' on the Vendors side and an infinity sign '∞'
on the Products side (One-to-Many).
7. Save and close the Relationships window.
Step 3: Create a Form (Data Entry Interface)
Forms are used to easily view and enter data one record at a time.
1. Select the Products table in the Navigation Pane.
2. Click the Create tab.
3. In the Forms group, click the Form Wizard.
4. Select all fields from the Products table and move them to the "Selected Fields" column.
5. Click Next. Choose the Columnar layout.
6. Click Next. Title the form Product Entry Form.
7. Click Finish. The Form will open, allowing you to easily browse and enter new products.
Step 4: Create a Query (Extracting Specific Data)
Queries are used to ask questions about your data.
1. Click the Create tab, then select Query Design.
2. In the Show Table box, add both the Vendors and Products tables, then close the box.
The relationship link should appear automatically.
3. Add Fields: Double-click the following fields to add them to the design grid:
o ProductName (from Products table)
o UnitPrice (from Products table)
o VendorName (from Vendors table)
4. Set the Criteria (The Question): In the Criteria row for the UnitPrice field, type the
expression: >100. (This asks for products costing over 100).
5. Click the Design tab and select Run to view the results.
6. Save the query as Expensive Products Query.
Step 5: Create a Report (Output and Printing)
Reports are used to present data in a printable, formatted summary.
1. Select the Expensive Products Query in the Navigation Pane (you want the report to
use the filtered data).
2. Click the Create tab, then select Report Wizard.
3. Select all fields from the Query and move them to the "Selected Fields" column.
4. Click Next. Grouping: Add VendorName as a grouping level.
5. Click Next. Sorting: Sort the data by ProductName in ascending order.
6. Click Next. Choose Block or Stepped layout and Portrait orientation.
7. Click Next. Title the report Vendor Product Report.
8. Click Finish. The report will display the list of products, grouped by the vendor who
supplies them, only showing products that match the query criteria (UnitPrice > 100).