Practical Database Access Questions
1. Employee Database
Create a database to track employee information.
Tables:
- Create a table named "Employees" with the following fields: EmployeeID (AutoNumber), Name (Text),
Department (Text), Salary (Currency), HireDate (Date)
- Create a table named "Departments" with the following fields: DepartmentID (AutoNumber),
DepartmentName (Text)
Data:
Employees:
Name Department Salary HireDate
John Smith Sales 50000 01/01/2020
Jane Doe Marketing 60000 06/01/2019
Bob Johnson IT 70000 03/01/2018
Alice Brown HR 40000 09/01/2020
Departments:
DepartmentName
Sales
Marketing
IT
HR
Forms:
- Create a form named "Employee Entry" to enter data into the Employees table
- Create a form named "Department Entry" to enter data into the Departments table
Queries:
- Create a query to calculate Total salary for each department
- Create a query to find Employees hired before 01/01/2020
- Create a query to find Department with the highest average salary
Reports:
- Create a report to display the total salary for each department
- Create a report to display the employees hired before 01/01/2020
Expressions:
- Use expressions to calculate the total salary and average salary
2. *Student Grades*
Create a database to track student grades.
Tables:
- Create a table named "Students" with the following fields: StudentID (AutoNumber), Name (Text)
- Create a table named "Courses" with the following fields: CourseID (AutoNumber), CourseName (Text)
- Create a table named "Grades" with the following fields: GradeID (AutoNumber), StudentID (Number),
CourseID (Number), Grade (Number)
Data:
Students:
Name
John Smith
Jane Doe
Bob Johnson
Courses:
CourseName
Math
Science
English
Grades:
StudentID CourseID Grade
1 1 80
1 2 70
1 3 90
2 1 90
2 2 80
2 3 70
3 1 70
3 2 60
3 3 80
Forms:
- Create a form named "Student Entry" to enter data into the Students table
- Create a form named "Course Entry" to enter data into the Courses table
- Create a form named "Grade Entry" to enter data into the Grades table
Queries:
- Create a query to calculate Average grade for each student
- Create a query to find Student with the highest overall grade
- Create a query to find Course with the highest average grade
Reports:
- Create a report to display the average grade for each student
- Create a report to display the course with the highest average grade
*Expressions:*
- Use expressions to calculate the average grade
3. Inventory Management
Create a database to track inventory levels.
Tables:
- Create a table named "Products" with the following fields: ProductID (AutoNumber), ProductName
(Text)
- Create a table named "Suppliers" with the following fields: SupplierID (AutoNumber), SupplierName
(Text)
- Create a table named "Inventory" with the following fields: InventoryID (AutoNumber), ProductID
(Number), SupplierID (Number), Quantity (Number), Price (Currency)
Data:
Products:
ProductName
Laptop
Desktop
Printer
Suppliers:
SupplierName
ABC Inc.
XYZ Corp.
DEF Ltd.
Inventory:
ProductID SupplierID Quantity Price
1 1 10 1000
1 2 20 1200
2 1 15 800
2 3 25 900
3 2 5 200
Forms:
- Create a form named "Product Entry" to enter data into the Products table
- Create a form named "Supplier Entry" to enter data into the Suppliers table
- Create a form named "Inventory Entry" to enter data into the Inventory table
Queries:
- Create a query to calculate Total value of each product
- Create a query to find Products with quantity less than 10
- Create a query to find Supplier with the highest total quantity
Reports:
- Create a report to display the total value of each product
- Create a report to display the products with quantity less than 10
Expressions:
- Use expressions to calculate the total value
4. *Order Management*
Create a database to track orders.
Tables:
- Create a table named "Customers" with the following fields: CustomerID (AutoNumber), Name (Text)
- Create a table named "Orders" with the following fields: OrderID (AutoNumber), CustomerID
(Number), OrderDate (Date), Total (Currency)
Data:
Customers:
Name
John Smith
Jane Doe
Bob Johnson
Orders:
CustomerID OrderDate Total
1 01/01/2020 100
1 01/15/2020 200
2 02/01/2020 50
3 03/01/2020 150
*Forms:*
- Create a form named "Customer Entry" to enter data into the Customers table
- Create a form named "Order Entry" to enter data into the Orders table
Queries:
- Create a query to calculate Total orders for each customer
- Create a query to find Customer with the highest total orders
- Create a query to find Order with the highest total
Reports:
- Create a report to display the total orders for each customer
- Create a report to display the order with the highest total
Expressions:
- Use expressions to calculate the total orders
5. *Employee Benefits*
Create a database to track employee benefits.
Tables:
- Create a table named "Employees" with the following fields: EmployeeID (AutoNumber), Name (Text)
- Create a table named "Benefits" with the following fields: BenefitID (AutoNumber), EmployeeID
(Number), BenefitType (Text), BenefitAmount (Currency)
Data:
Employees:
Name
John Smith
Jane Doe
Bob Johnson
Benefits:
EmployeeID BenefitType BenefitAmount
1 Health Insurance 500
1 Retirement Plan 1000
2 Health Insurance 600
3 Retirement Plan 800
Forms:
- Create a form named "Employee Entry" to enter data into the Employees table
- Create a form named "Benefit Entry" to enter data into the Benefits table
Queries:
- Create a query to calculate Total benefits for each employee
- Create a query to find Employee with the highest total benefits
- Create a query to find Benefit type with the highest total amount
Reports:
- Create a report to display the total benefits for each employee
- Create a report to display the benefit type with the highest total amount
Expressions:
- Use expressions to calculate the total benefits