Nhúng công thức người dùng trong Access

Ngày đăng: 23:52:38 28-04-2012

Chắc hắn rất nhiều bạn trong số những người làm về công tác kế toán tiền lương đã hơn một lần tự hỏi về việc làm thế nào để nhúng các công thức do người dùng tự định nghĩa vào xử lý số liệu.

Yêu cầu nghe có vẻ trừu tượng, song trên thực tế, điều này thường rất hữu ích, đặc biệt với những người hay phải làm công tác kế toán tiền lương. Có một người bạn đã đặt vấn đề này đối với tôi, anh ấy cũng mất kha khá thời gian để mày mò song kết quả không được như ý.

Quả thật với những người làm việc nhiều với lập trình thì vấn đề không quá khó, nhưng với các bạn mới bắt đầu hoặc không muốn đào sâu về việc này, vấn đề trở nên tương đối khó giải quyết. Để có thể cung cấp cho các bạn quan tâm một cách tiếp cận đơn giản, tôi xin phép được bắt đầu bài viết dựa trên những yêu cầu đầu vào như sau:

• • Quản lý hồ sơ lương nhân viên dựa trên các thông tin: Mã nhân viên, các loại hình phụ cấp, bảo hiểm … dưới dạng số liệu liên quan đến nhân viên đó cùng với hệ số biến động dựa trên sự thay đổi các yếu tố đầu vào.

  • Lập chu trình tính lương dựa trên sự kết hợp các dạng đầu vào từ mã biến động và các công thức này có thể thay đổi được tùy theo thời gian.

Tôi xin phép được giải quyết bài toán này dựa vào phần mềm MS Access.

Có thể các bạn sẽ hỏi tại sao lại là Access? Câu trả lời đơn giản là vì:

• • Access cho phép thiết kế cơ sở dữ liệu và các dạng biểu mẫu báo cáo, mẫu nhập liệu khá nhanh gọn.

  • Access quen thuộc với nhiều người dùng

  • Access cho phép sử dụng hàm người dùng trong các câu lệnh Query

  • Access có công cụ thiết kế Query rất tiện dụng.Chúng ta sẽ thiết kế giải pháp ra sao?

Chúng ta cần có nơi lưu trữ thông tin nhân viên

• • Cần có nơi lưu trữ thông tin liên quan đến công thức tính toán

  • Cần giải pháp để chuyển các công thức người dùng định nghĩa thành biểu thức tính toán để máy tính có thể sử dụng được

Thiết kế cơ cấu sinh và trả về kết quả.Tôi xin phép không giải thích quá nhiều về việc tại sao lại chọn giải pháp như vậy song có thể nói một cách ngắn gọn là:

• • Chúng ta có các yếu tố thay đổi tương đối (hồ sơ nhân viên, các mã biến động và các hệ số tính toán chẳng hạn bảo hiểm xã hội – a , phụ cấp độc hại - b, phụ cấp nghề nghiệp - c và ưu tiên vùng -d) và các yếu tố cố định tương đối đó là phép toán.

  • Máy tính không tự hiểu được các phép toán và ta cần phải biên dịch và điều chỉnh các phép toán đó sang thứ ngôn ngữ mà chúng có thể hiểu được.

Trước tiên chúng ta có 2 bảng tbl_Congthuc để lưu trữ công thức ứng với mã biến động.Tiếp theo, ta cần xây dựng bảng lưu lại hồ sơ nhân viên và các tham số bổ sung phục vụ cho công tác tính toán.

Xem hình bên về ví dụ 2 bảng.

Tiếp theo chúng ta cần xây dựng cơ chế biên dịch biểu thức thành dạng máy tính có thể hiểu được.

Giả sử ta có tình huống công thức:

• • Mức lương = bảo hiểm xã hội – a + phụ cấp độc hại – b + phụ cấp nghề nghiệp - c + ưu tiên vùng –d x 20% được tính toán trong trường hợp mã điều chỉnh là 1.Công thức tương đương là

  • Mức lương = a + b + c + d * 20%

  • Mức lương = bảo hiểm xã hội – a + phụ cấp độc hại x 15% khi mã điều chỉnh là 2.Vậy dữ liệu nhập trong bảng tbl_Congthuc sẽ có dạng như hình bên.

Trong bảng dữ liệu ta chỉ cần lưu lại mã biến động ứng với mỗi hồ sơ nhân viên.

Việc tiếp theo là xây dựng hàm biên dịch các công thức người dùng dưới dạng chuỗi thành dạng biểu thức tính toán được.

Để làm điều này ta cần sử dụng đến một hàm đặc biệt của VB đó là Eval. Hãy xét ví dụ sau:

Đầu vào là 1+2+3+4, với Excel thì đơn giản ta chỉ cần viết =1+2+3+4 trong ô và sẽ có kết quả là 10. Với VB điều này khác hoàn toàn do dữ liệu biểu thức nói trên chỉ là dạng ký tự và máy tính sẽ hiểu là chuỗi 1+2+3+4. Muốn trả về kết quả tính toán ta dùng hàm Eval như sau:

Ketqua=eval(1+2+3+4)

Và ketqua sẽ là 10.

Vậy việc còn lại của ta là gì?

• • Thay thế các giá trị tính toán vào biểu thức

  • Gọi hàm Eval và trả về kết quả.Trong thực tế, để có thể dễ dàng sử dụng các cách kết hợp khác nhau của công thức với những hàm người dùng khác, chúng ta cần một bước đệm.

1. Biên dịch các công thức, hàm thành dạng tính toán được.

2. Thay thế các toán tử trong công thức bằng các giá trị dữ liệu thật

3. Tính toán kết quả hàm người dùng trước

4. Chuyển biểu thức sang hàm Eval, tính toán và trả về kết quả.

Ở đây tôi dùng một hàm trung gian có tên là ProcessFomula với mã chi tiết như sau:

Function ProcessFomula(InForm As String, a As Long, b As Long, c As Long, d As Long) As Long

'Thay thế các đoạn chuỗi trong công thức bằng giá trị thật

InForm = Replace(InForm, "a", a)

InForm = Replace(InForm, "b", b)

InForm = Replace(InForm, "c", c)

InForm = Replace(InForm, "d", d)

'Gọi hàm Eval và tính toán rồi trả về kết quả cuối cùng

ProcessFomula = Eval(InForm)

End Function

Function Test1(a As Long, b As Long) As String

Test1 = (a + b) / 3

End Function

Như vậy hàm ProcessFomula đã giúp chúng ta giải quyết tất cả các bước 1-4 nói trên.

Việc còn lại là đưa những công đoạn này vào câu lệnh truy vấn Query hoặc form tùy theo yêu cầu người dùng.

Ngoài ra, ta còn có thể viết thêm các hàm tính toán khác và có thể chèn luôn vào công thức. (Trong ví dụ của tôi là hàm Test1)

Dạng câu lệnh truy vấn sẽ là

SELECT tbl_Sample.ID, tbl_Congthuc.CONGTHUC, ProcessFomula([Congthuc], tbl_Sample.HSLCU, tbl_Sample.HSLMOI, tbl_Sample.PCCU, tbl_Sample.PCMOI) AS Ketqua, tbl_Sample.HSLCU, tbl_Sample.HSLMOI, tbl_Sample.PCCU, tbl_Sample.PCMOI FROM tbl_Sample INNER JOIN tbl_Congthuc ON tbl_Sample.MABIENDONG = tbl_Congthuc.MABIENDONG;

Và bây giờ bạn đã có thể làm thêm những gì mình cần.

Để có thêm thông tin, xin xem trong file đính kèm hoặc email cho tôi tại địa chỉ ngocdd@sfdp.net

Cảm ơn sự chú ý của các bạn.

Ví dụ có kèm theo bài viết ở dưới đây.