Step 1: Setting Up Your MS Access Database
1. Create a New Database
o Open Microsoft Access.
o Select "Blank Database."
o Name your database (e.g., “BakerySalesDB”) and click “Create.”
2. Creating Tables
You’ll need at least three tables: Customer, Product (or Inventory), and Sales (or
Orders).
A. Customer Table
o Go to the "Create" tab and select "Table Design."
o Add the following columns with these example data types:
CustomerID - AutoNumber (Primary Key)
CustomerName - Short Text
PhoneNumber - Short Text
Email - Short Text
Order - Lookup Wizard (linking to Sales/Orders table to reference the
orders placed by customers)
o Set the "Required" property for the Order field to Yes in the Design View.
B. Inventory Table
o Create a new table with these columns:
ProductID - AutoNumber (Primary Key)
ProductName - Short Text
Price - Currency
StockQuantity - Number
Category - Short Text
C. Sales Table
o Create a new table with these columns:
OrderID - AutoNumber (Primary Key)
CustomerID - Number (set as Foreign Key)
ProductID - Number (set as Foreign Key)
OrderDate - Date/Time
Quantity - Number
TotalPrice - Currency
Relationships:
o Customer Table’s Order field should reference the Sales table’s OrderID.
o Product Table’s ProductID should link to the Sales table’s ProductID.
3. Populating Tables with Data
o Fill the Inventory Table with at least 10 products.
o Add at least 10 customers in the Customer Table.
o Assign orders to each customer in the Sales Table by linking them to products in
the Inventory Table.
Step 2: Relationships and Referential Integrity
1. Setting Relationships Between Tables
o Go to the "Database Tools" tab and select "Relationships."
o Add the Customer, Inventory, and Sales tables.
o Link CustomerID in the Customer Table to CustomerID in the Sales Table.
o Link ProductID in the Inventory Table to ProductID in the Sales Table.
o Ensure Referential Integrity is enabled by checking the box in the relationship
dialog.
2. Creating a Supplier Table and Relationship
o Create a Supplier Table with columns like:
SupplierID - AutoNumber (Primary Key)
SupplierName - Short Text
ContactNumber - Short Text
Address - Short Text
o Add a SupplierID column in the Inventory Table and link it to the Supplier
Table's SupplierID.
o Set referential integrity in this relationship as well.
Step 3: Queries and Forms
1. Query the Customer Table by Orders
o Go to the "Create" tab and select "Query Design."
o Add the Customer Table.
o Include all fields and sort the Order column in Descending Order.
o Run the query and capture a screenshot.
2. Creating a Tabular Form for Inventory
o Select the Inventory Table.
o Go to the "Create" tab and choose "Form Wizard."
o Select Tabular Layout and proceed.
o Once created, go to Design View and change the data label font to Arial Black
and font size to 24.
o Save the form and capture a screenshot.
Step 4: Report of Suppliers
1. Creating a Suppliers Report
o Go to the "Create" tab and select "Report Wizard."
o Choose the Suppliers Table (excluding SupplierID).
o Set the sort order to ascending by ItemName.
o Once created, capture a screenshot of the report.
Step 5: Export Your Database and Submit
1. Save your Database:
o Ensure all tables, forms, queries, and relationships are saved.
o Export the database file (e.g., as [Link]).
2. Upload to ColCampus
o Go to ColCampus, navigate to the assignment upload section, and attach the MS
Access database file.
Additional Questions (Section 2)
2.1 Steps in Database Design Process
The seven steps typically include:
1. Requirement Analysis: Gathering the specific needs of the users.
2. Conceptual Design: Defining the entities and relationships in the system.
3. Logical Design: Structuring tables and attributes without considering physical aspects.
4. Physical Design: Deciding data types, constraints, and indexes.
5. Normalization: Organizing data to reduce redundancy.
6. Implementation: Creating the database and tables in Access.
7. Testing and Evaluation: Running queries and tests to ensure data integrity.
2.2 Types of Relationships in MS Access
One-to-One: Each record in one table relates to only one record in another (rarely used).
One-to-Many: One record in a table can be associated with multiple records in another
table.
Many-to-Many: Many records in one table relate to many records in another, requiring a
junction table (e.g., Sales).
This approach will give you a fully functional sales management system in MS Access for your
bakery, organized and efficient for tracking orders and inventory! Let me know if you need
further help with any specific steps.