ADO: Kết nối Excel Database cho công việc kế toán

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