ADO: Kết nối Excel Database cho công việc kế toán (1 người xem)

[ptm0412]

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 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.

 

[ptm0412]

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', [stien], 0)) AS sttruocthue, VATRate/100 As ThueSuat, _ 
sum(IIf(tkno='133', [stien] ,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

 

[sealand]

Mình đã chép tăng số dòng của sheet Data lên 15 lần rồi mở rộng Name Data, vậy mà tốc độ sử lý thay đổi không đáng kể. Các tác vụ sử lý trên File vẫn nhẹ nhàng, nhanh gọn. Hơn nữa, SQL của Ptm0412 khá hoạt, rất tốt cho việc tham khảo tổng hợp dữ liệu.
Chắc là câu chuyện còn dài, vậy mình tham gia Ptm0412 nên dồn đoạn khai báo tạo kết nối vào Sub Mo() , đoạn đóng các kết nối và xoá biến đối tượng vào Sub Dong() . Như vậy, mỗi Sub hay Function cần trích dữ liệu sử lý ta gọi sub Mo và kết thúc gọi sub Dong là xong, mà Sub chức năng khác gọn gàng hơn, dễ tham khảo hơn.
Cám ơn Ptm0412.

 

[cadafi]

Để thuận tiện hơn cho việc triển khai các báo cáo nâng cao, anh ptm0412 có thể thêm vào cấu trúc data của file trên phần liên quan đến quản lý kho (Số lượng, đơn giá, mã hàng Đơn vị tính, mã kho) để có thể in các báo cáo kho (Tổng hợp nhập xuất tồn kho, thẻ kho, sổ chi tiết vật tư, v.v...); bổ sung các thông số lập Cash flow ngay trên DATA được không!?

Về Code:
Trong code của anh có đoạn này:

With Sheets("Data")
  Set myRng = .Range("Data")
  End With

Thực tế đối với bài này, đoạn code này không cần thiết. Tuy nhiên các bạn đừng vì thế mà bỏ nó đi, vì chúng ta sẽ cần đoạn này và các đoạn tương tự như vậy trong những báo cáo khác sắp tới.

Các biến khai báo cần tường minh và các thao tác cần ghi chú cụ thể để những người mới tiếp cận ADO họ có thể hiểu được một cách cụ thể nhất!

 

[ptm0412]

Dear Sealand:

Sẽ làm theo góp ý của bạn, về Mo() và Dong()

Dear Kiệt:

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.

 

[TrungChinhs]

ấy zà món này (cũ người nhưng mới ta) nghe có vẻ hấp dẫn đây nhưng chưa biết tiếp cận nó như thế nào ?

Tôi thấy trong bài viết là ADODC nhưng câu lệnh trong code lại là Set cnEx = New ADODB.Connection ?

ADO… là gì ? ví dụ: ADO… là một kiểu kết nối…. nếu được biết sơ qua về nó thì thật là thú vị, còn không biết thì cũng chẳng sao (sẽ tìm hiểu sau).

Các bước để thực hiện ADO ?
Vd: Khai báo biến; khai báo file nguồn; truy xuất dữ liệu…

Các câu lệnh trong từng phần (câu lệnh nào là bắt buộc và câu lệnh nào người dùng có thể gán vào). Trong code ví dụ nên có phần chú thích (dịch nôm càng kỹ càng tốt).

Rất mong được sự giúp đỡ của các bạn. Thanks!

 

[cadafi]

ADO… là gì ? ví dụ: ADO… là một kiểu kết nối…. nếu được biết sơ qua về nó thì thật là thú vị, còn không biết thì cũng chẳng sao (sẽ tìm hiểu sau).

Các bước để thực hiện ADO ?
Vd: Khai báo biến; khai báo file nguồn; truy xuất dữ liệu…

Các câu lệnh trong từng phần (câu lệnh nào là bắt buộc và câu lệnh nào người dùng có thể gán vào). Trong code ví dụ nên có phần chú thích (dịch nôm càng kỹ càng tốt).

ADO = ActiveX Data Object

Trong bài đầu của anh ptm0412 có link dẫn đến bài này
http://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
http://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: http://www.vovisoft.com)

 

[sealand]

To Trung Chinh: Sao bạn không dọc tài liệu của anh Duyêt nhà ta ấy. Nó đây này và ở đây nữa này (Và còn vô vàn bài của các cao thủ của GPE)

Bạn đừng nghĩ cái gì là cũ, cái gì là mới mà cái nào dùng được là ta dùng phải không? Cái định lý Pitago đã ai bỏ nó đi đâu? Nói thât, cái mà người ta khen mà áp dụng vào trường hợp của chúng ta là hơi mệt. Với điều kiện tay trái như chúng ta thì thật khó tiếp cận và sử dụng. Nó đòi hỏi phải có trình độ lập trình cao mới chủ động được. Đây mới mấy thứ OldMode này mà nghe chừng còn khó quá.
Rất có thể mình hơi bảo thủ, thông cảm nha.

 

[ThuNghi]

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 theo, các bạn hãy thử tạo bảng kê hóa đơn bán ra.

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 = .[e2]
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%', [stien], 0)) AS sttruocthue, VATRate/100 As ThueSuat, sum(IIf(tkco like '333%', [stien] ,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
  .[a4].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:R[-1]C)"
  .Range("J" & endR + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
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!
Bây giờ các bác cùng nhau làm thêm bảng cân đối số phát sinh từ ngày -> ngày.

 

[sealand]

Thu Nghi à,
1/Hôm nay học mót được của Thu Nghi cách đánh số TT khá hay không dùng For.
2/Riêng phần Ketnoi Thu Nghi dùng lệnh Application.Version để kiểm tra Version , từ đó tùy biến cho Off.2003 và Off.2007 (kèm theo đổi đuôi .xls ). Như vậy, tính tương thích sẽ cao hơn.
3/Lần sau Thu Nghi gửi cả ví dụ đi, đến giờ mình ráp code vào file của Ptm0412 vẫn chưa chạy.

 

[ptm0412]

Bây giờ các bác cùng nhau làm thêm bảng cân đối số phát sinh từ ngày -> ngày.

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
- ...

 

[ptm0412]

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', [stien], 0)) AS Thu,  sum(IIf(tkco ='112', [stien] ,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 & _
 "', [stien], 0)) AS Thu,  sum(IIf(tkco ='" & TKQuy & "', [stien] ,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ệ.

 

[ptm0412]

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!

 

[ptm0412]

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 [data]
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.[tkno]=DMTK.[sotk],Olddata.[Stien],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([tkco]=[sotk],[Stien],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 & "', [stien], 0)) AS PSno,
  sum(IIf(tkco ='" & TK & "', [stien] ,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.

 

[ptm0412]

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.

 

[ptm0412]

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 ở http://www.giaiphapexcel.com/forum/showpost.php?p=52973&postcount=10

SQL:

SELECT IIf([tkno]="111",[tkco],[tkno]) AS TKDU, IIf([tkco]="111",[dmtk].[ten], _
[dmtk_1].[ten]) AS TenTK, Sum(IIf([tkno]="111",[stien],0)) AS PSNo, _
Sum(IIf([tkco]="111",[stien],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 [dmtk].[ten],[dmtk_1].[ten], 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([tkno]='" & TK & "',[tkco],[tkno]) AS TKDU,IIf([tkno]='" & TK & _
"',[dmtk_1].[ten],[dmtk].[ten]) AS TenTK, " & _
" Sum(IIf([tkno]='" & TK & "',[stien],0)) AS PSNo, Sum(IIf([tkco]='" & TK & "',[stien],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.

 

[ptm0412]

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([tkno]=[sotk],[Stien],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([tkco]=[sotk],[Stien],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 ((([ckyno]+[ckyco]+[psn]+[psc])>0));

Ghi xuống sheet CDPS, tính số dư cuối kỳ và dòng tổng.

 

[sealand]

Phải nói quá hay đấy. Cứ tiến độ này ta sẽ có được cơ bản 1 chương trình Kế toán.
Cám ơn Ptm0412 nhé.

 

[ptm0412]

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.

2. Câu lệnh điều kiện trong SQL:

HAVING ([ckyno]+[ckyco]+[psn]+[psc])>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ợ.

 

[ptm0412]

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.

 

[ptm0412]

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.

 

[ptm0412]

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 [data]" & 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 [data]" & 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.

 

[ptm0412]

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.

 

[ptm0412]

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ợ.

 

[ptm0412]

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

 

[sealand]

Mình thực sự khâm phục công sức và "Tâm" của Ptm. Mong tiếp tục nhé.Nếu được phép của GPE sau này Ptm tổng hợp lại thành chuyên mục thì thật sự bổ ích cho dân Kế toán cần tạo cho riêng mình 1 chương trình Kế toán tham khảo.
Cám ơn.

P/s: Mình tham gia 1 chút. Riêng bài "1. Bảng kê hóa đơn nợ của khách hàng" thì cộtHD không nên để dạng Boolean vì nó chứa ít thông tin quá. Các phần mềm Kế toán thường hay dùng:
Hóa đơn vào:
V05: Hóa đơn 5%
V10: Hóa đơn 10%
V10B: Hóa đơn 10% có phụ phí (Xăng dầu v.v...)
Hóa đơn ra:
R05: Hóa đơn 5%
R10: Hóa đơn 10%
R10B: Hóa đơn 10% có phụ phí (Xăng dầu v.v...)

 

[hai2hai]

- 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 đó.

- Khi thanh toán, KH thanh toán vượt tổng tất cả các hóa đơn nợ thì sao anh? (số tiền thừa dùng để trả trước hóa đơn lần sau chứ ko trả lại).

- Tương tự, KH trả trước thì sao anh?

 

[ptm0412]

Cám ơn Sealand.

Mình tham gia 1 chút. Riêng bài "1. Bảng kê hóa đơn nợ của khách hàng" thì cộtHD không nên để dạng Boolean

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!

 

[ptm0412]

- Khi thanh toán, KH thanh toán vượt tổng tất cả các hóa đơn nợ thì sao anh? (số tiền thừa dùng để trả trước hóa đơn lần sau chứ ko trả lại).
- Tương tự, KH trả trước thì sao anh?

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.

 

[hai2hai]

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.

Em hiểu rồi, mới đầu em nghĩ là em đưa thêm các tình huống cho sinh động, té ra không phải là mục tiêu đó mà chỉ là mục tiêu học tập các thủ thuật. Thế thì em sẽ ko bàn tới các tình huống đó nữa vậy

Pre-Payment là 1 tình huống có thực, nghiệp vụ có thật trong thực thế mà đa số các phần mềm kế toán nào cũng phải tính tới (hầu hết các PMKT của nước ngoài đều có phần này). Nó "có bài có vở" để xử lý hẳn hoi chứ ko phải chỉ là chạy được và chỉ là vấn đề nhập liệu (ko chỉ phân bổ pre-payment amount cho các hóa đơn thanh toán)

Đúng là trong tình huống thực tế có nhiều thứ phải bàn. Tỷ như việc thanh toán cho từng hóa đơn rồi (trả trước hay trả sau cũng thế), sau đó dân nhà ta lại thích ... sửa/xóa hóa đơn thì mệt lắm. Lúc đó các phiếu thu chi thanh toán sẽ ra sao? Có ăn theo việc sửa/xóa hóa đơn đó ko? (Khổ nhất là sửa xóa chứng từ thì phải care đủ thứ, còn nếu chỉ có nhập xong và miễn sửa thì rất đơn giản)

Thôi, anh cứ tiếp tục các tình huống mà anh đưa ra đi.

 

[ptm0412]

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.

 

[ptm0412]

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ỳ.

 

[ptm0412]

Nhân trong bà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ới ý tưởng in liên tục, bạn đã dùng Pivot table và kết quả như tôi đã chỉ ra ở trên. Theo tôi, bạn hãy dùng ADO lần lượt cho từng mã đối tượng, copy xuống sheet nối tiếp nhau, cách nhau 1 dòng cộng và 1 dòng số dư cuối kỳ. Dù vậy cũng không phải viết câu SQL 100 lần cho 100 đối tượng, mà chỉ dùng 1 câu SQL duy nhất với biến thay đổi, biến này chạy theo danh sách mã đối tượng.

và:

II. Về việc dùng ADO hay PivotTable:

Tôi vẫn không ủng hộ việc dùng PivotTable như là 1 báo cáo hoàn chỉnh.
Lý do là cấu trúc 1 báo cáo khác xa cấu trúc của Pivot table, thêm dòng thêm cột thì rất gượng ép mà vẫn không đúng.

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.

 

[levanduyet]

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 mediafire.

Chúc các bạn vui.

Lê Văn Duyệt

 

[thanhtv2104]

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([tkno]=[sotk],[Stien],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([tkco]=[sotk],[Stien],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 ((([ckyno]+[ckyco]+[psn]+[psc])>0));

Ghi xuống sheet CDPS, tính số dư cuối kỳ và dòng tổng.

PTM cho mình hỏi chút: Tại sao trong file excel của bạn không có sheet nào tên là DMKT, nhưng trong câu truy vấn sql có dùng bảng DMKT nhỉ? Bạn khai báo bảng DMKT này như thế nào để excel hiểu dc đó là một bảng nhỉ?

 

[ptm0412]

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

 

[thanhtv2104]

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

Thanks ptm0412.
ptm0412 cho mình hỏi thêm một câu nữa là: ngôn ngữ SQL dùng trong ADO giống với ngôn ngữ SQL trong hệ quản trị csdl nào? (myslq, sql sever, ....)
- Nếu có thể bạn giúp mình chỉ ra những câu lệnh, cú pháp chuyên dùng cho ADO đc không?

 

[phungvinhquang]

hỏi về cách thay tên sheet bằng tên vùng

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

Rất cảm ơn anh ptm và các bạn. Kiến thức quả là bổ ích. Chỉ sợ là không đủ trình độ để lĩnh hội hết.

Anh ptm làm ơn nói rõ hơn về việc thay tên sheet băng tên vùng, trong trường hợp sheet co nhiều bảng và đã đặt tên bản.
Mình đã thử như sau:
- để tên sheet thì ok
- Đặt tên vùng là DataRng = Data!$A$1:$B$20 (giả sử vùng Data của mình trong phạm vi đó và dòng 1:1 là tiêu đề)
- Thay Data$ = DataRng
Khi chạy thì lỗi "DataRng$" is not a valid name

 

[ptm0412]

- Thay Data$ = DataRng
Khi chạy thì lỗi "DataRng$" is not a valid name

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?

 

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

Rất cảm ơn anh ptm và các bạn. Kiến thức quả là bổ ích. Chỉ sợ là không đủ trình độ để lĩnh hội hết.

Anh ptm làm ơn nói rõ hơn về việc thay tên sheet băng tên vùng, trong trường hợp sheet co nhiều bảng và đã đặt tên bản.
Mình đã thử như sau:
- để tên sheet thì ok
- Đặt tên vùng là DataRng = Data!$A$1:$B$20 (giả sử vùng Data của mình trong phạm vi đó và dòng 1:1 là tiêu đề)
- Thay Data$ = DataRng
Khi chạy thì lỗi "DataRng$" is not a valid name

Đối với vùng dữ liệu được đặt ở dạng là name thì không cần thêm dấu $ vào phía sau cùng. Bạn thử bỏ $ xem coi được không nhé.

 

[phungvinhquang]

Đối với vùng dữ liệu được đặt ở dạng là name thì không cần thêm dấu $ vào phía sau cùng. Bạn thử bỏ $ xem coi được không nhé.

Rất cảm ơn, mình sẽ thử

 

[phuongtrannhat1409]

Để 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 [data]
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.[tkno]=DMTK.[sotk],Olddata.[Stien],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([tkco]=[sotk],[Stien],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 & "', [stien], 0)) AS PSno,
  sum(IIf(tkco ='" & TK & "', [stien] ,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.

Em cóp nhặt được của thầy để làm công việc kế toán của em, cám ơn thầy rất nhiều. Em biết đề tài cũng đã lâu, nhưng kiến thức có hạn nên rất mong thầy giải đáp giúp em vướng mắc chổ sổ cái chi tiết và CDPS sử dụng datatmp và tmp nhưng có một vấn đề ở code datatmp là nếu có một hóa đơn nhiều dòng số tiền giống nhau thì code vào datatmp chỉ lấy dòng đầu tiên, các dòng giống nhau tiếp theo không được lấy. Rất mong thầy giúp em hoàn thiện chổ này ạ. Cám ơn thầy rất nhiều và mong rằng sẽ được cụng ly với thầy ở SNGPE 03/07/2016

 

[Tuanba1992]

file không chạy ra tài khoản chi con được ạ