Access Database Practical Exercise390

Objective: To provide a comprehensive guide to solving practical exercises in Access database, covering essential concepts and step-by-step instructions.

1. Create and Populate a Database

Instructions:

Launch Access and create a new database named "Sales Database."
Create a table called "Products" with fields: ProductID (AutoNumber), ProductName (Text), UnitPrice (Currency).
Create another table called "Orders" with fields: OrderID (AutoNumber), CustomerID (Text), OrderDate (Date), ProductID (Text), Quantity (Number).
Populate the "Products" and "Orders" tables with sample data.

2. Create a Query to Display Product Sales

Instructions:

Create a new query in Design View.
Add the "Products" and "Orders" tables to the query grid.
Create a calculated field named "ExtendedPrice" as " * ."
Group the query by "" and calculate the sum of "ExtendedPrice" to get total sales for each product.

3. Create a Form to Manage Data

Instructions:

Create a new form in Design View.
Add the "Products" table as the data source.
Drag and drop the desired fields (ProductID, ProductName, UnitPrice) onto the form design surface.
Configure the form's navigation and data entry options as needed.

4. Create a Report to Summarize Data

Instructions:

Create a new report in Design View.
Add the "Sales Database" query as the data source.
Drag and drop the "ProductID," "ProductName," and "Total Sales" fields onto the report design surface.
Group the report by "ProductID" and format it according to the desired layout.

5. Use Macros to Automate Tasks

Instructions:

Create a new macro in Design View.
Add a "FindRecord" action to locate a specific record in the "Products" table.
Add a "MsgBox" action to display a message when the record is found.
Save the macro and assign it to a button on the "Products" form.

6. Import Data from an External Source

Instructions:

Prepare an Excel file with customer data (Customer ID, Name, Address).
In Access, create a new table called "Customers."
Use the "External Data" tab to import the Excel file into the "Customers" table.
Match the data fields between the two sources and complete the import process.

7. Create Relationships between Tables

Instructions:

Open the "Sales Database."
Edit the relationships between the "Products" and "Orders" tables.
Establish a one-to-many relationship where "ProductID" is the primary key in "Products" and a foreign key in "Orders."
Enforce referential integrity to maintain data consistency.

8. Use SQL to Execute Queries

Instructions:

Open the "Sales Database."
Create a new query in SQL View.
Enter the following SQL statement:
```sql
SELECT ProductName, SUM() AS TotalQuantity
FROM Products
INNER JOIN Orders ON =
GROUP BY ProductName
```

9. Use Parameters in Queries

Instructions:

Create a new query in Design View.
Add the "Orders" table to the query grid.
Create a parameter named "@OrderDate" based on the "OrderDate" field.
Modify the query criteria to filter orders between two dates:
```sql
OrderDate Between [@OrderDate] And DateAdd("d", 1, [@OrderDate])
```

10. Use Crosstab Query to Summarize Data

Instructions:

Create a new query in Design View.
Add the "Sales Database" query as the data source.
Set the "ProductName" field as the row heading, "Month" field as the column heading, and "Total Sales" field as the value.
Use the "Crosstab" function to generate the summarized data.

2024-12-05

Previous：A Comprehensive Guide to Cimatron E Programming

Next：Unleash Your Inner Filmmaker: A Comprehensive Guide to Celebrity Cut Video Tutorials