ADO: Kết nối Excel Database cho công việc kế toán
Dùng ADO kết nối dữ liệu trong Excel cho Kế toán.
I. Bảng kê hóa đơn mua vào (khấu trừ)
Tiếp nối chủ đề của Sealand [URL="https://www.giaiphapexcel.com/forum/showthread.php?t=24648"]Kết nối giữa các file Excel bằng ADODC. , hôm nay ta thử xem ADO có thể làm những gì cho công việc kế toán, từ 1 CSDL ban đầu?
Trước tiên ta cần 1 bộ CSDL, tôi xin dùng tạm 1 CSDL tương đối hoàn chỉnh, gồm có:
_ Các bảng mã: Mã TK kế toán, mã Khách hàng, mã vật tư hàng hóa, mã chi phí, mã loại chứng từ
– Các bảng số dư đầu kỳ 0/01/2008: Số dư công nợ, số dư Tài khoản, số dư vật tư hàng hóa
– Bảng dữ liệu phát sinh, bao gồm tất cả phát sinh trong năm.
Với CSDL như trên, tôi đã làm cơ bản tất cả những biểu mẫu sổ sách cơ bản của kế toán, bằng công thức cũng được, bằng Access cũng được.
Bây giờ bằng công cụ ADO, với sự giới thiệu của Sealand, ta đã thấy tốc độ thần kỳ của việc tạo lập các loại báo cáo như thế nào. Hôm nay tôi xin làm trước mẫu bảng kê hóa đơn mua vào từng tháng (Bảng kê thuế GTGT đầu vào) bằng ADO.
Trong file, các bạn chọn tháng trong ô E2, rồi nhấn nút, và chớp mắt 1 cái! Thậm chí bạn không kịp chớp mắt đâu!
Chúng ta sẽ thảo luận và thực hiện tiếp những mẫu biểu khác, dần dần từng cái một.
Tiếp theo, các bạn hãy thử tạo bảng kê hóa đơn bán ra.
www.giaiphapexcel.com/diendan/threads/ado-k%E1%BA%BFt-n%E1%BB%91i-excel-database-cho-c%C3%B4ng-vi%E1%BB%87c-k%E1%BA%BF-to%C3%A1n.25759/
Thiết kế Tổng đãi ngộ (Total Rewards) theo khung SHRM
Khóa học “Thiết kế Tổng phần thưởng (Total Reward) chuẩn khung SHRM” giúp bạn nắm vững toàn bộ hệ thống đãi ngộ theo chuẩn...
Xem khóa học
Lưu ý 1: Đặc điểm của câu lệnh SQL trong MS Office
Trong khi thực hiện từng mẫu biểu báo cáo, tôi sẽ liệt kê những lưu ý cần thiết khi sử dụng ADO.
Lưu ý cho bài 1:
1. Một số trường (field) chỉ dùng làm điều kiện, có thể không cần hiển thị, thì không nằm trong cú pháp SQL phần Select.
2. Tất cả các fields không tính toán, đều phải liệt kê hết trong Group By, kể cả các fields không hiển thị.
3. Mặc định Query kết xuât ra sẽ sort theo field đầu tiên, muốn sort theo field/ nhiều fileds thì liệt kê lần lượt trong phần Order By
4. Khi tính toán trong SQL của Excel, nếu 1 trường group by nào có các records dữ liệu trống, thì các records đó không được Group như ý muốn. Nếu nguyên 1 trường trống, thì cũng như không Group gì cả, và field tính toán không tính toán gì cả.
Thí dụ:
SELECT 1 as STT, hoadon, ngaygoc, Serie, TenKH, Msthue, diengiai, _
sum(IIf(tkno<>'133', , 0)) AS sttruocthue, VATRate/100 As ThueSuat, _
sum(IIf(tkno='133', ,0)) AS thue
FROM [data$]
GROUP BY hoadon, ngaygoc, Serie, TenKH, diengiai, Msthue, VATRate, HD, loaict, LoaiHD
HAVING (HD=True) AND (loaict<>'BH') AND (LoaiHD='KT') and (month(ngaygoc)=8) _
ORDER BY ngaygoc
Sẽ làm theo góp ý của bạn, về Mo() và Dong()
Trong dữ liệu gốc thì Dữ liệu nhập hàng ngày gồm có 2 table, 1 Master (Chứng từ) và 1 Child (Chi tiết), có đầy đủ cả phần Nhập xuất kho. Tuy nhiên để đơn giản tôi đã dùng query tách ra thành 2 hệ, 1 là kế toán hạch toán tài khoản, và 1 là Nhập xuất kho. Như vậy dễ dàng xử lý hơn.
=======================
ADO = ActiveX Data Object
Trong bài đầu của anh ptm0412 có link dẫn đến bài này
https://www.giaiphapexcel.com/forum/showpost.php?p=171289&postcount=60
Anh down file PDF về nghiên cứu thử xem. Kẹt cái là bằng tiếng anh mà em chưa có thời gian dịch. Nhưng em nghĩ đọc loáng thoáng hiểu ý chắc được mà anh ha!
Tham khảo thêm tại đây nhé anh
https://www.giaiphapexcel.com/forum/showthread.php?p=82680
Bên cạnh đó, em xin được mạn phép đính thêm file ADO căn bản (Bằng tiếng Việt) để mọi người đọc và tiếp cận một cách nhanh nhất (nguồn: [URL='https://www.vovisoft.com/'%5Dhttps://www.vovisoft.com)
=======================
Tôi kết hợp ý của anh Sealand và dùng thuật tóan code của Bác Mỹ tạo thử 1 file BanRa. nhớ insert thêm 1 sh đặt là BanRa.
Dùng code sau:
Khai báo các thông số, biến…
Option Explicit
Dim Recex As Object, Cnex As Object
Dim FName As String
Dim ConnectionString As String, mySql As String
Dim i As Long, iMonth As Byte, endR As Long
Sub KetNoi()
FName = ThisWorkbook.Path & "" & ThisWorkbook.Name
Set Cnex = New ADODB.Connection
'Khai bao cau ket noi'
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FName & ";Persist Security Info=False; Extended Properties=Excel 8.0;"
Cnex.Open ConnectionString
Set Recex = New ADODB.Recordset
End Sub
Sub BoKetNoi()
Recex.Close
Set Recex = Nothing
Cnex.Close
Set Cnex = Nothing
End Sub
Và code
Sub BanRa()
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual
End With
KetNoi
'Khai bao thang can trich xuat'
With Sheets("Banra")
iMonth = .
End With
'—————————————-'
'Khai bao mySql'
mySql = "SELECT 1 as STT, hoadon, ngaygoc, Serie, TenKH, Msthue, diengiai," & Chr(10)
mySql = mySql & "sum(IIf(tkco like '511%', , 0)) AS sttruocthue, VATRate/100 As ThueSuat, sum(IIf(tkco like '333%', ,0)) AS thue " & Chr(10)
mySql = mySql & "FROM [data$]" & Chr(10)
mySql = mySql & "GROUP BY hoadon, ngaygoc, Serie, TenKH, diengiai, Msthue, VATRate, HD, loaict, LoaiHD" & Chr(10)
mySql = mySql & "HAVING (HD=True) AND (loaict='BH') AND (LoaiHD='KT') and (month(ngaygoc)=" & iMonth & ") order by ngaygoc"
Recex.Open mySql, Cnex, adOpenKeyset, adLockOptimistic
'Copy vao Sheets("Banra")'
With Sheets("Banra")
.Rows("4:65000").ClearContents
..CopyFromRecordset Recex
endR = .Cells(65000, 2).End(xlUp).Row
'tao soTT'
With .Range(.Cells(4, 1), .Cells(endR, 1))
.FormulaR1C1 = "=ROW()-3"
.Value = .Value
End With
'Dong total'
.Range("H" & endR + 1).FormulaR1C1 = "=SUM(R4C:RC)"
.Range("J" & endR + 1).FormulaR1C1 = "=SUM(R4C:RC)"
End With
BoKetNoi
With Application
.ScreenUpdating = True: .Calculation = xlCalculationAutomatic
End With
MsgBox "OK"
End Sub
Nhờ các bác xem và góp ý. Thấy cách này trích lọc khá nhanh mà chưa nắm hết lý thuyết.
Xin cám ơn!
ThuNghi à, khoan làm Cân đối PS đã, cái đó nghĩ thì đơn giản nhưng không dễ xơi, phải làm sao cho có số dư TK vào ngày đầu, coi như số dư đầu kỳ mới, rồi mới tính ra số dư cuối kỳ vào ngày cuối.
Trước mắt làm những cái đơn giản trước như:
– Sổ quỹ tiền mặt
– Sổ quỹ Ngân hàng
– Sổ cái chi tiết
– Sổ cái tổng hợp
– …
Sổ Quỹ tiền mặt kết hợp Sổ Quỹ ngân hàng
Hai sổ này cùng cấu trúc nên ta dùng chung 1 sheet. Muốn vậy ta phải dùng thêm 1 biến TKQuy để lấy số TK 111 hoặc 112.
Để lấy TK đối ứng, ta xem hễ TKno = TKQuy thì lấy TKco và ngược lại
Để lấy riêng Thu và Chi từ field Stien, ta xem hễ TKno = TKQuy thì là thu, ngược lại là chi.
Để lọc riêng những Phát sinh của TKQuy, dùng điều kiện Or: Tkno = TKQuy Or Tkco = TKQuy (Nếu And thì chả có dòng nào thỏa).
Để lấy phát sinh trong tháng iMonth, thêm điều kiện như lần trước.
Câu lệnh SQL cho TK 112 như sau:
SELECT 1 as STT, Sct, Date, TenKH, noidung, _
IIf(tkno='112', tkco, tkno) As TKDU, _
sum(IIf(tkno ='112', , 0)) AS Thu, sum(IIf(tkco ='112', ,0)) AS Chi
FROM [data$]
GROUP BY SCt,date, TenKH, noidung,tkno,tkco
HAVING ((tkno='112') Or (tkco='112')) and (month(date)=7) Order by date
Câu lệnh dùng biến:
mySql = "SELECT 1 as STT, Sct, Date, TenKH, noidung,"
mySql = mySql & "IIf(tkno='" & TKQuy & "', tkco, tkno) As TKDU,sum(IIf(tkno ='" & TKQuy & _
"', , 0)) AS Thu, sum(IIf(tkco ='" & TKQuy & "', ,0)) AS Chi " & Chr(10)
mySql = mySql & "FROM [data$]" & Chr(10)
mySql = mySql & "GROUP BY SCt,date, TenKH, noidung,tkno,tkco " & Chr(10)
mySql = mySql & "HAVING ((tkno='" & TKQuy & "') Or (tkco='" & TKQuy & "')) and (month(date)=" & iMonth & ") _
order by date"
Ghi chú: TKQuy là Text nên phải bao TKQuy bằng 1 cặp nháy đơn trong câu lệnh.
Chọn tháng ô D2 và chọn loại sổ (Số hiệu TK quỹ) ô E1 rồi nhấn nút, thử chớp mắt 1 cái như thường lệ.
Ghi chú 2: Ghi chú cho bài "Sổ quỹ tiền mặt"
1. Do tôi tách ra module khác với mục đích dễ tìm kiếm, nên các biến phải khai báo lại là Public:
Public Recex As Object, Cnex As Object
Public FName As String
Public ConnectionString As String, mySql As String
Public i As Long, iMonth As Byte, endR As Long
2. Các bạn để ý trong file tôi dùng trường noidung, và vì nội dung khác nhau cho từng bút toán nợ/ có, nên mỗi bút toán sẽ thể hiện lên 1 dòng. nếu 1 phiếu chi, chi cho 3 hóa đơn, sẽ hiện lên 6 dòng, 3 dòng tiền trước thuế và 3 dòng tiền thuế cho từng hóa đơn.
Nếu các bạn muốn cộng hết bút toán cùng nợ, cùng có vào với nhau, thí dụ như phiếu chi cho 3 hóa đơn sẽ cộng 3 dòng chi phí vào nhau, (nếu cùng TK chi phí) và cộng 3 dòng tiền thuế (cùng TK 133) vào nhau, thì thay trường noidung bằng trường diengiai.
Lý do: trường diengiai là của table Master gốc, còn trường noidung là của table Child gốc.
Các bạn thử xem!
Sổ cái chi tiết tài khoản
Để thực hiện Sổ cái chi tiết, Sổ cái tổng hợp và chuẩn bị cho cả Bảng Cân đối phát sinh trong khoảng thời gian bất kỳ (từ Date1 đến Date2), cần phải tính toán số dư đầu kỳ của khoảng thời gian đó (đầu ngày Date1).
Muốn vậy ta phải chạy 1 query tạo ra OldData là phát sinh trong khoảng thời gian trước ngày Date1, sau đó chạy 1 query phức hợp khác nhằm tính tổng phát sinh nợ, tổng phát sinh có của các TK trong khoảng thời gian trước Date1, tính ra số dư đầu ngày Date1.
Các Query này nằm trong module Temp.
Query tạo sổ cái nằm trong module SocaiCT
Câu lệnh SQL của từng phần như sau:
1. lấy dữ liệu trước ngày Date1
SELECT date, tkno, tkco, stien
FROM
Group by date, tkno, tkco, stien
having date < Date1
ORDER BY Date;
2. Lấy số dư đầu kỳ 01/01/2008 từ bảng SDTK (copy và paste cho lẹ, vì chỉ làm có 1 lần thôi).
3. Lấy tổng PS bên nợ của các TK:
SELECT Sum(IIf(olddata.=DMTK.,Olddata.,0)) AS PSNo
FROM DMTK LEFT JOIN OldData ON DMTK.SoTK = OldData.TkNo
GROUP BY DMTK.SoTK
ORDER BY DMTK.SoTK;
3. Lấy tổng phát sinh bên có của các TK:
SELECT Sum(IIf(=,,0)) AS PSCo
FROM DMTK LEFT JOIN OldData ON DMTK.SoTK = OldData.TKCo
GROUP BY DMTK.SoTK
ORDER BY DMTK.SoTK;
4. Dùng công thức tính số dư cuối kỳ của bảng cân đối tạm (bằng code cho nhẹ file)
5. Lấy phát sinh của TK trong khoảng thời gian từ Date1 đến Date2
SELECT 1 as STT, Sct, Date, diengiai,
IIf(tkno='" & TK & "', tkco, tkno) As TKDU,sum(IIf(tkno ='" & TK & "', , 0)) AS PSno,
sum(IIf(tkco ='" & TK & "', ,0)) AS PSco
FROM [data$]
GROUP BY SCt,date, diengiai,tkno,tkco
HAVING ((tkno='" & TK & "') Or (tkco='" & TK & "')) and _
(date>=" & Date1 & ") and (date<=" & Date2 & ")
Order by date;
Chọn tài khoản trong ô E1, gõ ngày bắt đầu trong ô E2, ngày kết thúc trong ô G2, rồi nhấn nút.
Ghi chú 3: Ghi chú cho bài Sổ cái chi tiết:
Trong quá trình lấy tổng PS bên nợ và tổng PS bên có của bảng cân đối tạm (OldCDPS), nếu lấy bằng cách thông thường
From DMTK Inner Join OldData Where …
thì chỉ liệt kê phát sinh của những TK có phát sinh trong kỳ (trước Date1). Việc này có cái sai là:
– có những TK chỉ phát sinh bên nợ, không PS bên có, và ngược lại, dẫn đến số lượng TK có PS nợ khác với số lượng TK có PS có, và thứ tự theo dòng của các TK có phát sinh này không cùng dòng, tính số dư cuối kỳ theo dòng không được.
– Có những TK có số dư mà không có phát sinh, cũng không tính số dư cuối kỳ theo dòng được.
Cho nên tôi đã dùng mối quan hệ outer join kiểu khác:
Include All records from DMTK and only those records from OldData Where the joined fields are equal.
Nghĩa là dùng Left Join như câu lệnh
FROM DMTK LEFT JOIN OldData ON …
Như vậy, nó sẽ liệt kê tất cả các TK dù có phát sinh hay không, tương ứng với tất cả các TK có trong danh mục. Việc này kết hợp với Order by DMTK.SoTK sẽ bảo đảm số dư đầu kỳ, phát sinh nợ, phát sinh có của mỗi TK sẽ nằm trên cùng dòng.
Sổ cái Tổng hợp:
Hai mối liên kết từ 1 table sang table thứ 2, bằng cách add 1 table 2 lần.
Xem thêm hình minh họa ở https://www.giaiphapexcel.com/forum/showpost.php?p=52973&postcount=10
SQL:
SELECT IIf(="111",,) AS TKDU, IIf(="111",., _
.) AS TenTK, Sum(IIf(="111",,0)) AS PSNo, _
Sum(IIf(="111",,0)) AS PSCO
FROM DMTK AS DMTK_1 _
INNER JOIN (DMTK INNER JOIN NewData ON DMTK.SoTK = NewData.Tkno) _
ON DMTK_1.SoTK = NewData.Tkco
GROUP BY .,., NewData.Tkno, NewData.Tkco
HAVING (((NewData.Tkno)="111")) OR (((NewData.Tkco)="111"));
Câu lệnh dùng biến:
mySql = "SELECT 1 As STT, IIf(='" & TK & "',,) AS TKDU,IIf(='" & TK & _
"',.,.) AS TenTK, " & _
" Sum(IIf(='" & TK & "',,0)) AS PSNo, Sum(IIf(='" & TK & "',,0)) AS PSCO " & Chr(10) & _
"FROM DMTK AS DMTK_1 INNER JOIN _
(DMTK INNER JOIN NewData ON DMTK.SoTK = NewData.Tkno) _
ON DMTK_1.SoTK = NewData.Tkco" & Chr(10) & _
" GROUP BY dmtk.ten, dmtk_1.ten, NewData.Tkno, NewData.Tkco" & Chr(10) & _
" HAVING (((NewData.Tkno)='" & TK & "')) OR (((NewData.Tkco)='" & TK & "'));"
Ghi chú 4:
1. Để lấy được tên tài khoản đối ứng, cần phải có mối liên kết từ TKno và TKCo sang table Danh mục. Để có được 2 mối quan hệ như vậy trong MS query ta add 1 table 2 lần. Trong câu lệnh SQl thì là Inner join 2 lần, DMTK và DMTK As DMTK_1
2. Trong sub SeparateData của kỳ trước, ta đã tách dữ liệu từ ngày Date1 đến ngày Date2 bằng 1 SQL riêng, chỉ gồm TKNo, TKCo và Stien. Lấy dữ liệu từ đây nhanh hơn so với lấy dữ liệu từ table Data nguyên vẹn.
Tiếp theo sẽ là Bảng cân đối Phát sinh từ ngày Date1 đến ngày Date2, căn cứ vào số dư cuối kỳ bảng CDPSTemp tách ra từ trước, và bảng NewData. Phương pháp giống như Bảng CDPSTemp.
Bảng Cân đối phát sinh trong khoảng thời gian bất kỳ
1. Chạy code separateData, lấy OldData trước ngày Date1, lấy NewData trong khoảng Date1, Date2.
2. Chạy code CDPSTemp, lấy số dư đầu kỳ (ngày Date1) tức là số dư cuối kỳ của khoảng thời gian từ đầu năm đến trước Date1.
3. Chạy SQL:
SELECT DMTK.SoTK, Sum(IIf(=,,0)) AS PSN
FROM DMTK LEFT JOIN NewData ON DMTK.SoTK = NewData.Tkno
GROUP BY DMTK.SoTK;
lấy tổng phát sinh nợ của các TK, liệt kê mọi TK dù có phát sinh hay không, bằng Left Join. Ghi vào sheet Tmp với name range là PSNo
4. Chạy SQL:
SELECT DMTK.SoTK, Sum(IIf(=,,0)) AS PSC
FROM DMTK LEFT JOIN NewData ON DMTK.SoTK = NewData.Tkco
GROUP BY DMTK.SoTK;
Cũng ghi vào sheet Tmp với name range là PSCo
5. Chạy SQL:
SELECT 1, DMTK.SoTK, DMTK.Ten, CDPSOld.CkyNo AS DKN,
CDPSOld.CkyCo AS DKC, PSNo.PSN, PSCo.PSC
FROM ((DMTK LEFT JOIN PSNo ON DMTK.SoTK = PSNo.SoTK) LEFT JOIN CDPSOld
ON DMTK.SoTK = CDPSOld.SoTK) LEFT JOIN PSCo
ON DMTK.SoTK = PSCo.SoTK
GROUP BY DMTK.SoTK, DMTK.Ten, CDPSOld.CkyNo, CDPSOld.CkyCo, PSNo.PSN, PSCo.PSC
HAVING (((+++)>0));
Ghi xuống sheet CDPS, tính số dư cuối kỳ và dòng tổng.
Ghi chú 5: Ghi chú cho bài cân đối phát sinh:
1. Dùng Left Join để liệt kê tất cả các TK dù có phát sinh hay không, nhằm mục đích liệt kê những TK có số dư, nhưng không phát sinh trong kỳ.
Mối liên kết Left join thể hiện trên hình như sau, với 1 đầu mối liên kết có mũi tên.
2867
2. Câu lệnh điều kiện trong SQL:
HAVING (+++)>0);
Nhằm mục đích chỉ lấy những TK hoặc có số dư đầu kỳ (có thể không phát sinh), hoặc TK có phát sinh (có thể không có số dư đầu kỳ).
Còn những TK không có số dư đầu kỳ và không phát sinh thì không thề hiện.
Sau phần kế toán tổng hợp, bắt đầu qua phần công nợ.
Công nợ chi tiết Khách hàng & Nhà cung cấp
SQL tương tự sổ cái chi tiết.
Chọn Tài khoản công nợ, chọn mã đối tượng (Khách hàng, Nhà cung cấp), chọn khoảng thời gian từ ngày đến ngày, rồi nhấn nút.
Công nợ Tổng hợp Khách hàng & Nhà cung cấp
Code tương tự như Cân đối phát sinh.
Chọn Tài khoản 131 hoặc 331, chọn ngày bắt đầu và ngày kết thúc.
Vui lòng tải file bài này cho cả công nợ chi tiết và tổng hợp, file trên có chỗ sai.
Cải tiến file công nợ 1 chút:
Datatemp2 lọc sẵn theo tài khoản công nợ cho ngắn lại:
1. DataOld:
mySql = "SELECT date, tkno, tkco, stien,diengiai, MSkh, hoadon " & Chr(10) & _
"FROM " & Chr(10) & _
"Where ((date < " & Date1 & ") And ((Tkno='" & Tk & "') Or(Tkco='" & Tk & "')))" & Chr(10) & _
"ORDER BY Date;"
2. DataNew:
mySql = "SELECT date, sct, tkno, tkco, stien, diengiai, MSkh, hoadon " & Chr(10) & _
"FROM " & Chr(10) & _
"Where ((date >= " & Date1 & ") And (date<= " & Date2 & ")) And _
((Tkco='" & Tk & "') Or(Tkno='" & Tk & "'))" & Chr(10) & _
"ORDER BY Date;"
Chắc có nhanh hơn, và có lợi 1 điều là nhẹ file hơn, do copy xuống sheet ít hơn hẳn.
Ngoài ra, do trong code có dùng câu lệnh gán công thức tính số dư 2 cột cuối, rồi gán cứng giá trị lại, nên phải bỏ câu Application.Calculation = xlCalculationManual
Vì Excel chưa kịp tính toán đã gán cứng giá trị rồi.
Các bạn tải lại file công nợ dưới đây.
Sổ Phát sinh Chi tiết Vật tư hàng hóa
Tương tự Công nợ chi tiết
Chọn Tài khoản Vật tư hoặc hàng hóa, chọn mã vật tư, ngày bắt đầu và ngày kết thúc, nhấn nút.
Bảng Cân đối nhập xuất tồn vật tư hàng hóa.
Code tương tự như Tổng hợp công nợ.
II Các báo cáo không chính thống
1. Bảng kê hóa đơn nợ của khách hàng
Điều kiện:
– Cơ sở dữ liệu phải có 1 trường "số hóa đơn"
– Khi thu tiền phải ghi rõ thu tiền hóa đơn số mấy vào trường này
– Khi gõ số hóa đơn phải theo quy ước số hóa đơn gồm mấy con số, thiếu phải điền số 0 vào, thí dụ quy ước 7 số, hóa đơn số 0034567. Mục đích để số hóa đơn bán hàng và số hóa đơn thu tiền giống nhau, Excel trừ đúng vào hóa đơn nợ. Gõ sai lão chết tiệt không chịu trách nhiệm.
– Nếu thu tiền nhiều hóa đơn, phải tách ra nhiều dòng, mỗi dòng 1 hóa đơn
– Nếu thu tiền ít hơn 1 hóa đơn, hoặc thu nhiều nhưng trừ dần từng tờ hóa đơn không hết số tiền của tờ hóa đơn cuối, thì lần sau phải thu bù hóa đơn đó.
– Để khỏi nhầm với hóa đơn đầu vào và đầu ra, trường HD tương ứng phải là FALSE
Cám ơn Sealand.
trường HD chỉ để xác định có hoá đơn đầu vào hoặc đầu ra hay không, nhằm mục đích lên 2 bảng kê hoá đơn báo cáo thuế. Ngoài ra còn 1 trường LoaiHD để phân biệt hoá đơn trực tiếp và hoá đơn khấu trừ, 1 trường VATRate là trường thuế suất. Thiết nghĩ như vậy là đủ cho khá nhiều yêu cầu.
Ghi chú: Theo ý kiến riêng của tôi thì chỉ có 2 loại thuế suất 5% và 10%, không có loại thứ 3. Theo nhận định của tôi thì phần mềm nào tách "10% có phụ phí" là thừa mà vẫn không đủ! Lý do:
– Biết đâu sẽ có "5% có phụ phí" ?
– Mục đích của PM là sẽ tự động nhân thuế suất ra tiền thuế cho 2 loại đầu, và không nhân hoặc nhân có điều kiện cho loại thứ 3. Điều này không cần thiết vì theo thực tế, những hoá đơn mười mươi 5%, hoặc 10%, khi tự động nhân xong cũng phải kiểm tra lại và sửa bằng tay một vài đồng, vài chục, thậm chí cả trăm đồng. Cái này tuỳ thuộc vào người viết hoá đơn làm tròn số kiểu nào! Kể cả hoá đơn tự động tính, tự động in cũng lệch vài đồng là chuyện thường.
– Khi tự động nhân như vậy, nếu sơ ý không viết code làm tròn sẽ có số lẻ đằng sau. Cứ vài tờ như vậy là lệch 1 đồng trên báo cáo là chuyện thường xuyên xảy ra. Kiểu như mua hàng 2 tờ hoá đơn, trả tiền 2 tờ đủ, mà vẫn lên báo cáo công nợ tồn đọng!
Nói chung khi khách hàng trả tiền trước, nghĩa là khi trả tiền chưa có số hoá đơn, thì sau này khi bán hàng xuất hoá đơn sẽ phải quay lại để điền vào. Phần này các phần mềm đều có option trong form nhập liệu:
1. Khi thu tiền khách hàng, chọn option nào đó để cấn trừ hoá đơn, sẽ nhảy ra 1 form liệt kê hoá đơn nợ (nếu có) để đánh dấu trừ nợ từng tờ.
2. Khi bán hàng, chọn option nào đó, sẽ nhảy ra 1 form liệt kê số tiền đã trả trước (nếu có), để điền số hoá đơn vào.
Sau đó PM sẽ tự động làm phần còn lại.
Nói chung đây là vấn đề của nhập liệu, không phải vấn đề của ADO trong topic này. Hơn nữa, mình chỉ có tham vọng làm file Excel ứng dụng cho 1 Doanh nghiệp loại nhỏ, thậm chí không có nhiều kho hàng, không tính giá thành phức tạp. Trong Excel có khi phải sửa trực tiếp vào cells, hoặc đôi khi tính tay ra con số để gõ vào.
Doanh nghiệp lớn phức tạp không nên dùng Excel, mà nên sử dụng phần mềm của người viết chuyên nghiệp hơn, thí dụ VNUNI, không nên dùng của lão chết tiệt. Topic này chỉ để tham khảo cách vận dụng ADO, các thủ thuật Select, Join, TempQuery, Where condition, … để ra rất nhiều những báo cáo khác nhau.
Muốn làm được thì như đã nói, phải thiết kế được Cơ sở dữ liệu với đầy đủ những tables, fields nhằm đáp ứng yêu cầu của các loại báo cáo. Giỏi lập trình đến đâu mà CSDL không đủ thông tin cần thiết thì cũng thua.
Thí dụ muốn liệt kê hoá đơn nợ như bài trên, 1 yêu cầu đơn giản, nhưng đòi hỏi rất nhiều từ thiết kế CSDL, nhập liệu, đến kỹ năng vận dụng thủ thuật SQL.
Xin cám ơn mọi người đã quan tâm theo dõi.
2. Bảng kê hóa đơn nợ nhà cung cấp:
Kết hợp luôn với sheet Bảng kê hóa đơn nợ khách hàng cho tiện.
3. Báo cáo Chi phí
Tập hợp chi phí theo tài khoản và loại chi phí.
Ghi chú:
– Dùng 1 Query tách Data từ ngày Date1 đến ngày Date2, chỉ lấy dòng có field mã chi phí <>"", gán xuống sheet DataTemp2, gán Name là "DataCP"
– Dùng 1 vòng lặp For để lấy 5 queries chi phí cho 5 tài khoản: 627, 632, 635, 641, 642, lấy từ "DataCP", gán xuống sheet Tmp4, đặt name tương ứng.
– Dùng 1 Query chính từ bảng DMCP Left join 5 bảng chi phí của 5 tài khoản.
– Điều kiên lọc: Tổng chi phí cùng loại trong cả 5 tài khoản lớn hơn không.
Toàn bộ công việc này tương đương với việc lập 1 Pivot Table, nhưng nhanh hơn và còn có thể lọc theo khoảng thời gian bất kỳ.
Nhân trong [URL='https://www.giaiphapexcel.com/forum/showthread.php?p=180406#post180406'%5Dbài này, có bạn muốn in công nợ chi tiết của khách hàng (hoặc nhà cung cấp 1 cách liên tục (chắc để tiết kiệm giấy), tôi có viết:
và:
Hôm nay tôi viết code tạo bảng công nợ chi tiết in liên tục, mặc dù biết rằng có thể in hàng loạt công nợ chi tiết của mọi khách hàng (nhà cung cấp) bằng cách thay lần lượt mã đối tượng vào ô tương ứng của sheet và chạy code, thêm dòng lệnh in. Nhưng như vậy nghĩa là mỗi đối tượng sẽ qua 1 trang in mới dù cho không phát sinh hoặc chỉ phát sinh 1 vài dòng dữ liệu. (Tốn giấy)
ADO chạy rất nhanh, nhưng code bị hơi chậm do chèn dòng tổng, dòng trắng phân cách, dư nợ cuối kỳ cho từng đối tượng. Khoảng vài cái chớp mắt. Nhưng thử nghĩ ADO chạy chừng 30 lần cho 30 đối tượng mà chỉ 3 – 5 giây thì cũng đáng.
Chào các bạn,
Mới cập nhật
Tin chắc các bạn cần bài tổng hợp nhằm tham khảo. Mạn phép Bác ptm0412 tôi tổng hợp thành tập tin help.
Các bạn có thể tải tập tin tại [URL="https://www.mediafire.com/?zitkwjmtu2y"%5Dmediafire.
Chúc các bạn vui.
Sao bạn không gõ tiếng Việt có dấu? Bạn sửa lại, nếu không tôi sẽ xoá bài.
ADO lấy dữ liệu từ các bảng (table), không phải chỉ lấy từ tên sheet.
Nếu 1 sheet chỉ chứa 1 bảng, ta có thể dùng tên sheet làm tên table, và ADO hiểu. Nếu thêm $ thì càng tốt thí dụ [Data$]
Nếu 1 sheet chứa nhiều table như sheet DM (3 bảng danh mục), ta sẽ đặt name cho từng vùng, name đó trở thành tên table
============================
Thay Data$ bằng DataRng thì phải báo lỗi (nếu có) là DataRng is not a valid name chứ?
Bạn đưa file lỗi đó lên xem?