Nhờ giúp: Bài toán quản lý hạn mức tiêu dùng (2 người xem)

[ndu96081631]

Tôi đang nhận một bài toán về việc quản lý hạn mức và gặp chút khó khăn. Tôi gửi lên nhờ mọi người trợ giúp
1> Mô tả bài toán

- Có 1 danh sách nhiều thành viên (tối đa 500 người)
- Mỗi người được cho trước 1 hạn mức tiêu dùng. Cụ thể là:

a> Mỗi tuần, mỗi người không được dùng quá 30 đ
b> Mỗi tháng, mỗi người không được dùng quá 100 đ
c> Mỗi năm, mỗi người không được dùng quá 1000 đ​

- Sang tuần mới, hạn mức tiêu dùng sẽ tự động reset về 30 đ (không cộng dồn dù tuần trước thừa)
- Sang năm mới, hạn mức tiêu dùng sẽ tự động reset về 100 đ (không cộng dồn dù tháng trước thừa)
- Sang năm mới, hạn mức tiêu dùng sẽ tự động reset về 1000 đ (không cộng dồn dù năm trước thừa)
- Mỗi khi có phát sinh chi tiêu, hạn mức tuần, tháng, năm tự động giảm xuống tương ứng
- Mỗi ngày, mỗi người chỉ phát sinh tiêu dùng 1 lần duy nhất (hoặc không có phát sinh)
Ghi chú: Tuần được định nghĩa bắt đầu từ Thứ Hai, kết thúc vào Chủ Nhật​

2> Yêu cầu

- Khi nhập liệu phát sinh, ghi rõ tên, số ID người dùng và hạn mức tồn theo tuần, tháng, năm
- Báo cáo trong ngày nếu có phát sinh, ghi rõ hạn mức đã dùng
- Có thể chỉnh sửa được nếu nhập liệu hoặc báo cáo sai
Điều này có nghĩa là: Khi nhập liệu, mỗi lần gõ ID thì ta phải biết được ngay lập tức ID đó có hạn mức tồn hiện đang là bao nhiêu (để quyết định con số phát sinh hợp lý tiếp theo) nhưng khi báo cáo thì ta lại cần biết hạn mức đã dùng​

--------------------------------------------------
Hiện tại tôi đăng gặp khó khăn:
- Ngay từ đầu, tôi không biết phải bố trí CSDL khi nhập liệu thế nào cho dễ tính toán nhất
- Việc tính toán sẽ gặp khó khăn khi chỉnh sửa, vì thông số chỉnh sửa sẽ ảnh hưởng đến toàn bộ những thông số khác. Ví dụ ta đang nhập được 500 dòng, giờ quay lại sửa dòng thứ 250 thì các con số tính toán từ dòng 251 trở xuống sẽ bị ảnh hưởng. Vậy phải tính toán lại?
--------------------------------------------------
Bài toán cụ thể của tôi thật ra không phải là quản lý hạn mức tiêu dùng (cũng là hạn mức nhưng không phải về tiêu dùng). Tuy nhiên, do thấy các thành viên GPE đa phần là dân kế toán nên tôi cố ý quy đổi bài toán cho mọi người dễ hiểu
Ở đây tôi cần mọi người trợ giúp về việc BỐ TRÍ CSDL khi nhập liệu (tôi thấy nó vừa đơn giản nhưng lại.. rối quá). Nếu gợi ý được cách tính toán sao cho đơn giản, hiệu quả thì càng tốt. Bởi tôi lo dữ liệu nhiều sẽ làm chậm bảng tính
(Giải pháp công thức hay VBA đều được)

 

[Hai Lúa Miền Tây]

Tôi đang nhận một bài toán về việc quản lý hạn mức và gặp chút khó khăn. Tôi gửi lên nhờ mọi người trợ giúp
1> Mô tả bài toán

- Có 1 danh sách nhiều thành viên (tối đa 500 người)
- Mỗi người được cho trước 1 hạn mức tiêu dùng. Cụ thể là:

a> Mỗi tuần, mỗi người không được dùng quá 30 đ
b> Mỗi tháng, mỗi người không được dùng quá 100 đ
c> Mỗi năm, mỗi người không được dùng quá 1000 đ​

Cho em hỏi theo hạn mức trên thì có 3 hạn mức? Mỗi thành viên đều có hạn mức theo 3 nhóm giống nhau hay là khác nhau Sư Phụ?

 

[ndu96081631]

Cho em hỏi theo hạn mức trên thì có 3 hạn mức? Mỗi thành viên đều có hạn mức theo 3 nhóm giống nhau hay là khác nhau Sư Phụ?

Hạn mức y chang nhau cho mọi thành viên Hai Lúa à
Sau khi nhập liệu thì ở tại một thời điểm bất kỳ, ta cần biết được hạn mức tồn và hạn mức đã dùng của từng thành viên

 

[anhtuanle123]

mình nghĩ dữ liệu ghi nhân việc chi tiêu của các ID cần khoảng 3 cột , ID , Ngày , Số tiền ,
3 thông tin này + với bảng master định mức phía trên mình nghĩ là đủ thông tin để kiểm tra , và xuất report cho kế toán roài !

 

[Hai Lúa Miền Tây]

Hạn mức y chang nhau cho mọi thành viên Hai Lúa à
Sau khi nhập liệu thì ở tại một thời điểm bất kỳ, ta cần biết được hạn mức tồn và hạn mức đã dùng của từng thành viên

Vậy sau này mình có phát sinh thêm hạn mục nào nữa không Sư Phụ? Theo em thì nên thiết kế bảng hạn mục riêng. Bảng đó sẽ join với bảng danh sách thành viên rồi tính toán. Vậy sẽ giảm thiểu nhập liệu và dữ liệu lưu trữ.

 

[huuthang_bd]

Hạn mức y chang nhau cho mọi thành viên Hai Lúa à
Sau khi nhập liệu thì ở tại một thời điểm bất kỳ, ta cần biết được hạn mức tồn và hạn mức đã dùng của từng thành viên

Vậy dữ liệu của anh chỉ cần Ngày - ID - Số tiền thôi.
Nếu hạn mức tồn chỉ dùng để tham khảo khi nhập liệu thì lúc nhập tính ra để tham khảo chứ không nên nhập vào dữ liệu.
Muốn báo cáo như thế nào thì cứ xử bằng VBA thôi.

 

[ndu96081631]

mình nghĩ dữ liệu ghi nhân việc chi tiêu của các ID cần khoảng 3 cột , ID , Ngày , Số tiền ,
3 thông tin này + với bảng master định mức phía trên mình nghĩ là đủ thông tin để kiểm tra , và xuất report cho kế toán roài !

Quan trọng là số tiền đấy! Nó bị ràng buộc bởi hạn mức tồn hiện tại nên chỉ có thể nhập số <= tồn
---------------------------------------------

Vậy sau này mình có phát sinh thêm hạn mục nào nữa không Sư Phụ? Theo em thì nên thiết kế bảng hạn mục riêng. Bảng đó sẽ join với bảng danh sách thành viên rồi tính toán. Vậy sẽ giảm thiểu nhập liệu và dữ liệu lưu trữ.

Chỉ nhiêu đó thôi, không có bất cứ hạng mục nào nữa. Luôn luôn là vậy từ năm này sang năm khác. Có chăng thì trong tương lai có thể thay đổi các con số hạng mức Tuần, Tháng, Năm mà thôi
--------------------------------------------

Vậy dữ liệu của anh chỉ cần Ngày - ID - Số tiền thôi.
Nếu hạn mức tồn chỉ dùng để tham khảo khi nhập liệu thì lúc nhập tính ra để tham khảo chứ không nên nhập vào dữ liệu.
Muốn báo cáo như thế nào thì cứ xử bằng VBA thôi.

Đúng rồi! Hạn mức tồn chỉ để nhìn (để nhập phát sinh không bị > tồn). Còn khi lưu CSDL ta lưu thông số hạn mức đã dùng
Điều này có nghĩa là:
- Cái ta có trong CSDL sẽ là hạn mức đã dùng
- Khi mở form nhập, ta phải tính toán sao đó để có được hạn mức tồn
Nếu dùng công thức thì chắc chắn phải là SUMIFS hoặc SUMPRODUCT gì đó. Tuy nhiên tôi đang nghĩ liệu có thể thêm cột phụ nào đó để giảm bớt quá trình tính toán không

 

[Hai Lúa Miền Tây]

Chỉ nhiêu đó thôi, không có bất cứ hạng mục nào nữa. Luôn luôn là vậy từ năm này sang năm khác. Có chăng thì trong tương lai có thể thay đổi các con số hạng mức Tuần, Tháng, Năm mà thôi

Do vậy em mới đề cập ở trên là lập bảng hạng mục riêng, nếu có thay đổi các con số thì ta chỉ chỉnh 3 lần, thay vì chỉnh tất cả 500 người * 3. Cái này cũng như mình tính kế hoạch vậy thôi Sư Phụ.

 

[ndu96081631]

Do vậy em mới đề cập ở trên là lập bảng hạng mục riêng, nếu có thay đổi các con số thì ta chỉ chỉnh 3 lần, thay vì chỉnh tất cả 500 người * 3. Cái này cũng như mình tính kế hoạch vậy thôi Sư Phụ.

OK! Việc này chấp nhận
Vấn đề mà mình gặp khó khăn là: Tình toán khá nhiều khi nhập liệu (để ra được mức tồn). Ớn nhất là khoản SỬA dữ liệu, nó sẽ làm thay đổi toàn bộ hạng mức đã dùng phía sau dòng vừa sửa. Vậy nên mình đang nghĩ đến vấn đề bố trí CSDL, liệu nó có làm cho việc tính toán dễ dàng hơn không? Mà bố trí CSDL thế nào đây?

 

[anhtuanle123]

chắc vấn đề nằm ở chỗ bác phân phân về cách tính toán !
về mặt kỹ thuật mình nghĩ là nếu nhập trên winform thì kiểm tra chặt chẽ hơn !
còn khi nhập trên lưới của Excel thì mình bắt sự kiện Worksheet_Change mỗi khi nhập số tiền thì kiểm tra để xem số tiền hợp lệ ko !
+ các kiểm tra cần thiết , khi nhập số tiền thì phải nhập ID , ngày trước
+ tính tổng số tiền đã sử dụng của ID này theo năm , tháng ,tuần và so sánh với số hạn mức trong master !
nếu cả 2 điều kiện trên thỏa thì số tiền phát sinh của ID tương ứng sẽ đc chấp nhận , ngược lại xử lý abc gì đó ^^
nhưng cách này phù hợp với tổ chức database hơn , còn nếu mục đích vừa thỏa mãn nhập liệu , kiểm tra , làm báo cáo chung với nhau thì chắc nghĩ cách khác !
mình nghĩ thế !

 

[Hai Lúa Miền Tây]

OK! Việc này chấp nhận
Vấn đề mà mình gặp khó khăn là: Tình toán khá nhiều khi nhập liệu (để ra được mức tồn). Ớn nhất là khoản SỬA dữ liệu, nó sẽ làm thay đổi toàn bộ hạng mức đã dùng phía sau dòng vừa sửa. Vậy nên mình đang nghĩ đến vấn đề bố trí CSDL, liệu nó có làm cho việc tính toán dễ dàng hơn không? Mà bố trí CSDL thế nào đây?

1. Bảng Hạng mục:
MaHangMuc
TenHangMuc
HanMucSuDung (Nếu không dùng bảng 2)

2. Lich sử Hạn mục:
Ngay
MaHangMuc
HanMucSuDung

3. Bảng Danh sách thành viên:
ThanhVienID
Ten

4. Bảng Dữ liệu lưu trữ:
Ngay
MaHangMuc
ThanhVienID
SoTien

Ở trên là 4 bảng, nếu Sư Phụ muốn biết và xem lịch sử thay đổi hạng mục thì phải thêm bảng số 2. Vấn đề còn lại là nhập liệu và tính toán thôi Sư Phụ.

 

[ndu96081631]

1. Bảng Hạng mục:
MaHangMuc
TenHangMuc
HanMucSuDung

2. Lich sử Hạn mục:
Ngay
MaHangMuc
HanMucSuDung

3. Bảng Danh sách thành viên:
ThanhVienID
Ten

4. Bảng Dữ liệu lưu trữ:
Ngay
MaHangMuc
ThanhVienID
SoTien

Ở trên là 4 bảng, nếu Sư Phụ muốn biết và xem lịch sử thay đổi hạng mục thì phải thêm bảng số 2. Vấn đề còn lại là nhập liệu và tính toán thôi Sư Phụ.

Thấy cái bảng bố trí này hình như đầu mình có sáng ra chút rồi đó!
Tuy nhiên mình đang thắc mắc không biết bảng số 2 sẽ dùng vào việc gì

 

[Hai Lúa Miền Tây]

Thấy cái bảng bố trí này hình như đầu mình có sáng ra chút rồi đó!
Tuy nhiên mình đang thắc mắc không biết bảng số 2 sẽ dùng vào việc gì

Bảng số 2 sẽ có cái để kiểm tra lịch sử thay đổi. Đồng thời nếu Sư Phụ muốn ràng buộc vấn đề cấp hạn mức cho từng thời điểm cụ thể thì cần bảng 2, nếu không có thì sau này thay đổi hạn mức sẽ gặp khó khăn trong vấn đề tính toán dữ liệu. Vì thay đổi cái nguồn, mà dữ liệu nguồn đó làm cơ sở tính toán cho các cái trong các báo biểu, sẽ không cho ra báo biểu chính xác khi truy vấn vào dữ liệu trước khi thay đổi.

 

[ndu96081631]

Bảng số 2 sẽ có cái để kiểm tra lịch sử thay đổi. Đồng thời nếu Sư Phụ muốn ràng buộc vấn đề cấp hạn mức cho từng thời điểm cụ thể thì cần bảng 2, nếu không có thì sau này thay đổi hạn mức sẽ gặp khó khăn trong vấn đề tính toán dữ liệu. Vì thay đổi cái nguồn, mà dữ liệu nguồn đó làm cơ sở tính toán cho các cái trong các báo biểu, sẽ không cho ra báo biểu chính xác khi truy vấn vào dữ liệu trước khi thay đổi.

Nếu bỏ bảng 2 đi thì phần còn lại chính là cách bố trí hiện tại của tôi. Nhưng mà tính toán nhiều quá mỗi khi có sửa dữ liệu (sửa chứ không phải thêm) nên tôi đang phân vân đây!

 

[Hai Lúa Miền Tây]

Nếu bỏ bảng 2 đi thì phần còn lại chính là cách bố trí hiện tại của tôi. Nhưng mà tính toán nhiều quá mỗi khi có sửa dữ liệu (sửa chứ không phải thêm) nên tôi đang phân vân đây!

Sửa dữ liệu là sửa dữ liệu nguồn (Hạn mục) hay sửa gì Sư Phụ?

 

[ndu96081631]

Sửa dữ liệu là sửa dữ liệu nguồn (Hạn mục) hay sửa gì Sư Phụ?

Sửa CSDL đã lưu đấy!
Ví dụ 10 ngày trước ta ghi anh A chi tiêu 5đ, hôm đó đã báo cáo rồi luôn. Hôm nay ta phát hiện thì ra có sai sót, vậy ta sẽ load lại dữ liệu của 10 ngày trước lên, sửa chi tiêu anh A lại thành 7đ (chẳng hạn) rồi ghi vào CSDL đồng thời xuất báo cáo khác thay thế cái sai.
Vậy thì chẳng những số liệu 10 ngày trước của anh A bị ảnh hưởng mà cả số liệu của 9 ngày gần đây của anh A cũng phải tính lại toàn bộ

 

[ndu96081631]

Nếu thêm cột phụ ghi giá trị WEEKNUM(ngày phát sinh) vào CSDL lưu thì sao ta? Có thể giảm nhẹ quá trình tính toán không?

 

[Hai Lúa Miền Tây]

Nếu thêm cột phụ ghi giá trị WEEKNUM(ngày phát sinh) vào CSDL lưu thì sao ta? Có thể giảm nhẹ quá trình tính toán không?

Định dạng cái ngày sử dụng hạn mức để làm điều kiện lọc như sau nè Sư Phụ
-Đối với tuần: Format(NgaySuDung, "ww-mm-yyyy")
-Đối với tháng: Format(NgaySuDung, "mm-yyyy")
-Đối với năm: Format(NgaySuDung, "yyyy") Hoặc dùng hàm year

Em làm thử 1 trường hợp là Tuần như sau:

Sub HanMuc_Tuan()
    Dim cn  As Object
    Dim varname1 As String
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0"
    varname1 = ""
    varname1 = varname1 & "SELECT Q_Tuan.LanThayDoiHanMuc, Q_Tuan.MsThanhVien, Q_Tuan.HoTenThanhVien, Q_Tuan.TongTuan, [HanMuc$].Tuan, [TongTuan]-[Tuan] AS ConLai, Q_Tuan.DieuKien " & vbCrLf
    varname1 = varname1 & "FROM [HanMuc$] INNER JOIN ( SELECT [DuLieu$].MsThanhVien, [DS_ThanhVien$].HoTenThanhVien, [DuLieu$].LanThayDoiHanMuc, Sum([DuLieu$].Tuan) AS TongTuan, Format([Ngay],'ww-mm-yyyy') AS DieuKien " & vbCrLf
    varname1 = varname1 & "FROM [DS_ThanhVien$] INNER JOIN ([HanMuc$] INNER JOIN [DuLieu$] ON [HanMuc$].LanThayDoi = [DuLieu$].LanThayDoiHanMuc) ON [DS_ThanhVien$].MsThanhVien = [DuLieu$].MsThanhVien " & vbCrLf
    varname1 = varname1 & "GROUP BY [DuLieu$].MsThanhVien, [DS_ThanhVien$].HoTenThanhVien, [DuLieu$].LanThayDoiHanMuc, Format([Ngay],'ww-mm-yyyy') ) Q_Tuan ON [HanMuc$].LanThayDoi = Q_Tuan.LanThayDoiHanMuc where Q_Tuan.DieuKien ='" & Format(Sheet5.[D2], "ww-mm-yyyy") & "';"
    Sheet5.Range("A4:G10").ClearContents
    Sheet5.[A4].CopyFromRecordset cn.Execute(varname1)
    
End Sub

 

[ndu96081631]

Định dạng cái ngày sử dụng hạn mức để làm điều kiện lọc như sau nè Sư Phụ
-Đối với tuần: Format(NgaySuDung, "ww-mm-yyyy")
-Đối với tháng: Format(NgaySuDung, "mm-yyyy")
-Đối với năm: Format(NgaySuDung, "yyyy") Hoặc dùng hàm year

Em làm thử 1 trường hợp là Tuần như sau:

Sub HanMuc_Tuan()
    Dim cn  As Object
    Dim varname1 As String
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0"
    varname1 = ""
    varname1 = varname1 & "SELECT Q_Tuan.LanThayDoiHanMuc, Q_Tuan.MsThanhVien, Q_Tuan.HoTenThanhVien, Q_Tuan.TongTuan, [HanMuc$].Tuan, [TongTuan]-[Tuan] AS ConLai, Q_Tuan.DieuKien " & vbCrLf
    varname1 = varname1 & "FROM [HanMuc$] INNER JOIN ( SELECT [DuLieu$].MsThanhVien, [DS_ThanhVien$].HoTenThanhVien, [DuLieu$].LanThayDoiHanMuc, Sum([DuLieu$].Tuan) AS TongTuan, Format([Ngay],'ww-mm-yyyy') AS DieuKien " & vbCrLf
    varname1 = varname1 & "FROM [DS_ThanhVien$] INNER JOIN ([HanMuc$] INNER JOIN [DuLieu$] ON [HanMuc$].LanThayDoi = [DuLieu$].LanThayDoiHanMuc) ON [DS_ThanhVien$].MsThanhVien = [DuLieu$].MsThanhVien " & vbCrLf
    varname1 = varname1 & "GROUP BY [DuLieu$].MsThanhVien, [DS_ThanhVien$].HoTenThanhVien, [DuLieu$].LanThayDoiHanMuc, Format([Ngay],'ww-mm-yyyy') ) Q_Tuan ON [HanMuc$].LanThayDoi = Q_Tuan.LanThayDoiHanMuc where Q_Tuan.DieuKien ='" & Format(Sheet5.[D2], "ww-mm-yyyy") & "';"
    Sheet5.Range("A4:G10").ClearContents
    Sheet5.[A4].CopyFromRecordset cn.Execute(varname1)
   
End Sub

Hình như có gì đó sai sai thì phải Hai Lúa ơi!
Có điều kiện ràng buộc thế này:
- Sang tuần mới, hạn mức tiêu dùng sẽ tự động reset về 30 đ (không cộng dồn dù tuần trước thừa)
- Sang năm mới, hạn mức tiêu dùng sẽ tự động reset về 100 đ (không cộng dồn dù tháng trước thừa)
- Sang năm mới, hạn mức tiêu dùng sẽ tự động reset về 1000 đ (không cộng dồn dù năm trước thừa)
Hai Lúa có tính đến điều kiện này không?

 

[Hai Lúa Miền Tây]

Hình như có gì đó sai sai thì phải Hai Lúa ơi!
Có điều kiện ràng buộc thế này:
- Sang tuần mới, hạn mức tiêu dùng sẽ tự động reset về 30 đ (không cộng dồn dù tuần trước thừa)
- Sang năm mới, hạn mức tiêu dùng sẽ tự động reset về 100 đ (không cộng dồn dù tháng trước thừa)
- Sang năm mới, hạn mức tiêu dùng sẽ tự động reset về 1000 đ (không cộng dồn dù năm trước thừa)
Hai Lúa có tính đến điều kiện này không?

Có cộng theo tuần đó Sư Phụ. Nếu tuần mới thì sẽ trở lại điều kiện ban đầu chứ không cộng dồn