Microsoft Access cung cấp các tính năng mạnh mẽ để tự động hóa các phép tính phức tạp trên các bảng được liên kết bằng cách giảm việc nhập dữ liệu thủ công, giảm thiểu lỗi và giữ cho cơ sở dữ liệu của bạn nhất quán trong thời gian thực. Các trường được tính toán có thể làm cho cơ sở dữ liệu tự động hơn; thay vì yêu cầu người dùng nhập tổng số, chiết khấu, ngày đến hạn hoặc giá trị lợi nhuận theo cách thủ công, Access có thể tính toán chúng từ các trường hiện có.
Trong hướng dẫn này, chúng tôi sẽ trình bày cách thêm các trường được tính toán vào bảng Access để tính toán liên kết chéo tự động. Hãy xây dựng các trường được tính toán tự động lấy và tính toán các giá trị trên các bảng có liên quan.
Hãy nhớ rằng trong cơ sở dữ liệu nâng cao, các trường được tính toán không được sử dụng ngẫu nhiên. Quy tắc quan trọng nhất là:
- Sử dụng các trường được tính toán cho các giá trị phụ thuộc vào các trường trong cùng một bản ghi
- Sử dụng truy vấn để tính toán phụ thuộc vào các bảng được liên kết hoặc nhiều bản ghi
Bước 1:Thiết lập các bảng liên quan mẫu
Trường được tính toán trải dài trên các bảng phụ thuộc vào mối quan hệ vững chắc. Bắt đầu ở đây trước khi viết một biểu thức.
Tạo mối quan hệ:
- Đi tới Công cụ cơ sở dữ liệu tab>> chọn Mối quan hệ
- Thêm bảng.
- Kéo ID khách hàng từ bảng Khách hàng tới ID khách hàng trong bảng Đơn hàng
- Kéo ProductID từ bảng Sản phẩm tới ProductID trong bảng OrderDetails
- Kéo ID đơn hàng từ bảng Đơn hàng tới OrderID trong bảng OrderDetails
- Bật Thực thi tính toàn vẹn tham chiếu
- Nhấp vào OK
Thiết lập này cho phép tính toán liên kết chéo (ví dụ:tổng phụ thứ tự là tổng của các dòng). Mối liên kết này làm cho việc tra cứu giữa các bảng trở nên đáng tin cậy. Nếu không có nó, trường được tính toán có thể âm thầm trả về giá trị rỗng khi bản ghi được tham chiếu bị xóa hoặc không khớp.
Bước 2:Thêm trường tính toán đơn giản vào bảng
- Mở Chi tiết đơn hàng bảng trong Chế độ xem thiết kế
- Ở hàng trống đầu tiên:
- Tên trường:LineTotal
- Loại dữ liệu:Đã tính
- Access mở Trình tạo biểu thức
- Nhập công thức của bạn:
- Hoặc xây dựng nó một cách trực quan:Mở rộng OrderDetails bảng>> nhấp đúp vào Số lượng và Đơn giá , sau đó thêm * nhà điều hành
- Đặt Loại kết quả (Tiền tệ, Số, Văn bản, v.v.) để khớp với kết quả mà biểu thức trả về
- Mở rộng Thuộc tính trường>> chọn Tiền tệ
- Lưu cái bàn
Tổng cộng có chiết khấu:
[Quantity] * [UnitPrice] * (1 - [DiscountRate])
Giờ đây, Access sẽ tự động tính toán LineTotal cho mọi bản ghi — không cần VBA, không cần cập nhật thủ công. Mỗi khi bạn thêm hoặc chỉnh sửa Số lượng hoặc Đơn giá trong Chế độ xem biểu dữ liệu, LineTotal sẽ tự động cập nhật.
Bước 3:Xử lý các phép tính liên kết chéo — Sử dụng các hàm tổng hợp miền trong truy vấn
Không được phép tham chiếu bảng chéo trực tiếp trong các trường được tính toán theo bảng. Bạn cần sử dụng truy vấn hoặc VBA. Hàm tổng hợp miền là cơ chế tích hợp sẵn của Access để kéo các giá trị được tính toán từ một bảng hoặc truy vấn khác vào một biểu thức. Những cái hữu ích nhất là:
Chức năng Mục đích DLookup()Trả về một giá trị từ một bảng khácDSum()Tính tổng các giá trị từ một bảng khác khớp với một điều kiệnDCount()Đếm các bản ghi khớp trong một bảng khácDAvg()Tính giá trị trung bình từ một bảng khácDMax() / DMin()Trả về giá trị tối đa hoặc tối thiểu từ một bảng khácTạo truy vấn:
- Đi tới Tạo tab>> chọn Truy vấn SQL
- Từ Thêm bảng ngăn, thêm bảng Đơn hàng và Khách hàng
- Thêm các trường:Tên khách hàng, ID đơn hàng
- Trong cột Trường trống, hãy tạo trường được tính toán cho Tổng
- Chèn biểu thức sau:
Total: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderID])
- DSum() tính tổng LineTotal cho OrderID phù hợp (một hàm tổng hợp miền hoạt động trên các bảng)
- Lưu dưới dạng qryOrderSummary
- Nhấp vào Chạy
Truy vấn tính toán lại sau mỗi lần chạy. Sử dụng truy vấn này làm Nguồn bản ghi cho biểu mẫu, báo cáo hoặc làm cơ sở cho các tính toán tiếp theo.
Định dạng bằng đơn vị tiền tệ:
Total: CCur(DSum("[LineTotal]","OrderDetails","[OrderID]=" & [Orders].[OrderID]))
Bước 4:Xây dựng truy vấn được tính toán trên nhiều bảng làm nguồn tính toán
Đối với các trường hợp phức tạp hơn — chẳng hạn như tính tổng chiết khấu liên quan đến cấp độ của khách hàng từ một bảng và giá sản phẩm từ một bảng khác — bạn nên tạo một truy vấn cơ sở kết hợp tất cả các bảng có liên quan, sau đó tham chiếu truy vấn đó trong các trường hoặc biểu mẫu được tính toán của bạn.
Các bước:
- Đi tới Tạo tab>> chọn Truy vấn SQL
- Thêm Đơn hàng , Sản phẩm và Chi tiết đơn hàng vào truy vấn
- Thêm các trường:OrderID, ProductName
- Thêm cột được tính toán vào ô Trường trống:
Profit: [DiscountedTotal] - [CostPrice]
- Lưu truy vấn dưới dạng qryOrderProfit
- Nhấp vào Chạy
Bây giờ bạn có báo cáo Lợi nhuận cho tất cả các đơn hàng có Tên sản phẩm.
Giờ đây, mọi biểu mẫu, báo cáo hoặc trường được tính toán xuôi dòng đều có thể sử dụng DLookup() hoặc truy vấn con đối với qryOrderProfit để nhận được giá trị được tính toán đầy đủ — tất cả đều được liên kết chéo và tự động.
Bước 5:Tự động cập nhật bằng macro dữ liệu
Khi bạn cần lưu trữ một kết quả được tính toán (không chỉ hiển thị) — ví dụ:ghi tổng số được tính toán trở lại bảng Đơn hàng mỗi khi bản ghi OrderDetails thay đổi — hãy sử dụng Macro Dữ liệu được gắn vào bảng con.
Thêm trường tổng vào bảng đơn hàng:
- Đầu tiên, hãy mở Đơn hàng bảng trong Chế độ xem thiết kế
- Tên trường:Tổng cộng
- Loại dữ liệu:Tiền tệ
Đây phải là trường Tiền tệ thông thường, không phải trường được tính toán.
Thiết lập:
- Mở Chi tiết đơn hàng trong Chế độ xem thiết kế
- Đi tới Công cụ bảng tab>> chọn Bảng tab>> chọn Sau khi chèn / Sau khi cập nhật
- Trong trình chỉnh sửa macro, hãy sử dụng SetField và Bản ghi tra cứu hành động:
- Chọn Bản ghi tra cứu
Lookup Up A Record In: Orders Where Condition: [Orders].[OrderID] = [OrderDetails].[OrderID]
- Chọn Chỉnh sửa bản ghi>> chọn SetField
Name: [Orders].[Total]
Value: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderID])
- Nhấp vào Lưu
Tính năng này tự động kích hoạt mỗi lần chèn hoặc cập nhật vào OrderDetails, đẩy tổng số được tính toán lại trở lại bảng Đơn hàng gốc — hoàn toàn tự động, không cần VBA.
Lặp lại sau khi cập nhật:
Sau khi chèn macro chỉ cập nhật tổng khi một hàng chi tiết mới được thêm vào. Nếu người dùng thay đổi Số lượng, Đơn giá hoặc Tỷ lệ chiết khấu thì tổng số cũng phải cập nhật.
Thêm macro tương tự:
Look Up A Record In Orders
Where Condition: [Orders].[OrderID]=[OrderDetails].[OrderID]
EditRecord
SetField
Name: [Orders].[Total]
Value: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderDetails].[OrderID])
- Lưu nó
Giờ đây, bất cứ khi nào một bản ghi được chèn hoặc cập nhật trong OrderDetails, Access sẽ tự động tính toán lại tổng số đơn hàng và lưu nó vào bản ghi khớp trong bảng Đơn hàng.
Bước 6:Hiển thị và sử dụng phép tính
- Trong dạng xem biểu dữ liệu: Các trường được tính toán xuất hiện và cập nhật trực tiếp.
- Trong biểu mẫu/báo cáo: Căn cứ vào biểu mẫu hoặc báo cáo về truy vấn (qryOrderSummary) để có kết quả liên kết chéo đầy đủ. Thêm các hộp văn bản không liên kết có biểu thức.
- Lọc/Sắp xếp: Bạn có thể sử dụng các trường được tính toán trong tiêu chí hoặc sắp xếp truy vấn.
Mẹo: Để chỉnh sửa trường được tính toán sau này:
- Trong Chế độ xem biểu dữ liệu:Chọn cột>> chọn Trường tab>> sửa đổi Biểu thức
- Hoặc quay lại Chế độ xem thiết kế>> chọn Thuộc tính>> chọn Biểu thức .
Các phương pháp hay nhất và cân nhắc về hiệu suất
- Ưu tiên truy vấn hơn các trường được tính toán trong bảng cho bất kỳ điều gì liên quan đến nhiều bảng, tổng hợp hoặc các thay đổi trong tương lai. Các truy vấn dễ di chuyển hơn (ví dụ:tới SQL Server) và linh hoạt.
- Tránh lưu trữ các phép tính trừ khi hiệu suất yêu cầu điều đó (ví dụ:các tập dữ liệu rất lớn có tổng số tiền nhanh chóng chậm). Thay vào đó hãy tính toán lại trong các truy vấn.
- Chuẩn hóa: Chỉ lưu trữ đầu vào thô. Tính toán kết quả đầu ra một cách linh hoạt.
- Thử nghiệm: Sau khi thay đổi, hãy luôn xác minh bằng dữ liệu mẫu, đặc biệt là sau khi thêm mối quan hệ.
- Hiệu suất: Quá nhiều trường được tính toán hoặc lệnh gọi DSum() phức tạp trên các bảng lớn có thể làm mọi thứ chậm lại. Lập chỉ mục các khóa ngoại.
- Tóm tắt các hạn chế:
- Không có trường nào từ các bảng khác trực tiếp trong biểu thức được tính toán trong bảng.
- Các hàm bị hạn chế trong các trường được tính toán theo bảng (sử dụng truy vấn để có quyền truy cập VBA đầy đủ).
- Kết quả ở dạng chỉ đọc.
- Mẹo mở rộng quy mô: Đối với các nhu cầu rất nâng cao, hãy cân nhắc việc di chuyển logic sang chế độ xem SQL hoặc chương trình phụ trợ như SQL Server, nơi các cột được tính toán cung cấp nhiều năng lượng hơn.
Các cạm bẫy thường gặp và cách khắc phục sự cố
- #Lỗi hoặc #Tên?: Kiểm tra xem tên trường có nằm trong ngoặc [], kiểu dữ liệu khớp và mối quan hệ có hoạt động hay không.
- Tài liệu tham khảo thông tư: Không tham chiếu trường được tính toán trong biểu thức riêng của nó hoặc tạo vòng lặp.
- Loại dữ liệu không khớp: Đặt Loại kết quả một cách rõ ràng (ví dụ:Tiền tệ cho trường tiền).
- Lỗi trên nhiều bảng: Di chuyển logic sang một truy vấn có các phép nối hoặc sử dụng DSum().
- Nếu sử dụng bảng được liên kết (ví dụ:với SharePoint hoặc cơ sở dữ liệu khác), các trường được tính toán có thể có giới hạn hàng hoặc vấn đề làm mới.
Kết luận
Bằng cách làm theo các bước ở trên, bạn có thể thêm các trường được tính toán vào bảng Access để tính toán liên kết chéo tự động. Các trường được tính toán rất hữu ích cho việc tự động tính toán cấp hàng trong Microsoft Access — chúng hoạt động tốt với các giá trị như tổng dòng, chiết khấu, ngày đến hạn và lợi nhuận trên mỗi dòng đơn hàng. Đối với các phép tính liên kết chéo nâng cao, truy vấn là công cụ phù hợp:chúng tuân theo mối quan hệ giữa các bảng và tính tổng đơn đặt hàng, tổng doanh số bán hàng của khách hàng, số dư hàng tồn kho và tóm tắt lợi nhuận. Việc nắm vững các trường tính toán và tính toán liên kết chéo trong Access sẽ biến kho lưu trữ dữ liệu tĩnh thành một hệ thống sống động, tự duy trì.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!