(*) Để tạo công thức với hàm BS_SQL có thể sử dụng chức năng "SQL Builder" trên thanh công cụ hoặc trong menu.

Mở "C:\A-Tools\DATA_DEMO\Examble.xls" để làm các ví dụ dưới đây.

        

BS_SQL: Hàm truy vấn dữ liệu, kết quả trả về là một mảng giá trị dạng bảng (table)

                  
 

Cấu trúc hàm: BS_SQL(SQL [,OPTIONS])

                        
 

Trong đó:

                            

+

SQL  :   Là một chuỗi khai báo câu lệnh truy vấn (SQL). SQL là một ngôn ngữ truy vấn CSDL như trong Access, Foxpro, dBASE, SQLServer, Oracle…

    
   

http://vi.wikipedia.org/wiki/Select_(SQL)

                        
 

SELECT select_list FROM table_source [ WHERE search_condition ]
 [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]

     
       
                               
 

Các từ có màu xanh đậm là từ khoá, khi viết câu lệnh SQL phải đảm bảo thứ tự trước sau của các từ khoá.

              
 

Những khai báo nằm trong ngoặc vuông ([ ]) có thể dùng hoặc không  - tuỳ chọn.

                  
                               
 

select_list: Danh sách các cột trong bảng dữ liệu "table_source", cũng có thể là danh sách các giá trị

              
 

--->

Để đổi tên cột, đăt AS [Tên cột] ngay sau cột muốn đổi

                      
 

--->

Nếu lấy tất cả các cột trong "table_source" dùng kư tự *

                      
 

table_source: là một bảng hay danh sách bảng dữ liệu. Nếu là danh sách th́ các bảng cách nhau bởi dấu phảy (,).

            
   

Ví dụ 1

Chọn tất cả các cột từ sổ NKC

                        
     

Công thức=BS_SQL("SELECT * FROM NKC")

                    
                               
   

Ví dụ 2

Chọn 10 ḍng đầu của tất cả các cột từ sổ NKC

                    
     

Công thức=BS_SQL("SELECT TOP 10 * FROM NKC")

                  
                               
   

Ví dụ 3

Chọn danh sách chứng từ duy nhất từ sổ NKC

                    
     

Công thức=BS_SQL("SELECT DISTINCT SO_CT FROM NKC")

                
                               
 

FROM table_source

                          
 

       table_source:

Bảng dữ liệu nguồn chứa các thông tin cần trích lọc, nó có thể là một Name trỏ tới một vùng dữ liệu.

        
     

Nếu thông tin cần t́m hay để tính toán có từ nhiều sổ th́ table_source là nguồn dữ liệu được kết hợp từ nhiều table,

     
     

Các phương thức kết hợp:

                        
     

INNER JOIN

Ví dụ

SELECT…FROM nkc INNER JOIN dmkh ON nkc.ma_kh = dmkh.ma_kh

      
     

(*)

Theo phương thức INNER JOIN, các table quan hệ có thể được liệt kê trong table_source ngăn cách bởi dấu phảy ( , )   table1, table2,..., khi đó

       

search_condition phải chỉ ra biểu thức quan hệ giữa các khoá của các table (table1.keyA = table2.keyA)

  
       

Ví dụ

SELECT…FROM nkc, dmkh WHERE (nkc.ma_kh = dmkh.ma_kh) AND...

      
                               
     

LEFT JOIN

Ví dụ

SELECT…FROM nkc LEFT JOIN dmkh ON nkc.ma_kh = dmkh.ma_kh

      
     

RIGHT JOIN

Ví dụ

SELECT…FROM nkc RIGHT JOIN dmkh ON nkc.ma_kh = dmkh.ma_kh

      
     

FULL JOIN

Ví dụ

SELECT…FROM nkc FULL JOIN dmkh ON nkc.ma_kh = dmkh.ma_kh

      
                               
   

--->

Khi lập một bảng mà các cột dữ liệu được lấy từ các table khác nhau th́ phải chỉ thị tên table đứng trước.

        
     

Tổng quát là [TÊN BẢNG].[TÊN CỘT]

                      
                               
   

Ví dụ

Lập sổ gồm các cột: Tên khách hàng, Số tiền. Dữ liệu được lấy từ 2 sổ NKC (THANH_TIEN) và DMKH (Ten).

      
     

Công thức=BS_SQL("SELECT dmkh.ten, nkc.thanh_tien FROM nkc INNER JOIN dmkh ON nkc.ma_kh = dmkh.ma_kh")

  
     

Công thức=BS_SQL("SELECT dmkh.ten, nkc.thanh_tien FROM nkc, dmkh WHERE nkc.ma_kh = dmkh.ma_kh")

    
                               
 

(*) Quan hệ CSDL

Có thể lập một sổ mà các cột được nhận từ nhiều sổ khác nhau, các sổ này được liên kết với nhau thông qua một trường (Field) có quan hệ.

  
     

Bạn hăy nh́n vào sổ NKC và các sổ danh mục (DM%). Cột MA trong các sổ danh mục chính là khoá liên kết với sổ NKC, sổ KHO cũng vậy.

  
     

Các quan hệ như sau:

 

 

 

 

 

 

 

 

 

      
     

 

NKC.MA_KH = DMKH.MA_KH

        

 

      
     

 

NKC.MA_NB = DMNB.MA_NB

        

 

      
     

 

NKC.MA_NV = DMNV.MA_NV

        

 

      
     

 

NKC.NOTK = DMTK.MA

          

 

      
     

 

NKC.COTK = DMTK.MA

          

 

      
     

 

KHO.MA_VLSPHH = DMVLSPHH.MA_VLSPHH

      

 

      
     

 

NKC.SO_CT = KHO.SO_CT

          

 

      
     

 

…

 

 

 

 

 

 

 

 

      
     

Một trường ở tableA quan hệ với một trường ở tableB là chúng cùng chứa chung một loại thông tin.

        
     

Để hiểu rơ hơn về LEFT JOIN, RIGHT JOIN, FULL JOIN t́m đọc các tài liệu hướng dẫn ngôn ngữ T-SQL.

      
   

(*)

http://en.wikipedia.org/wiki/Join_(SQL)

                  
     

http://msdn2.microsoft.com/en-us/library/aa258869(SQL.80).aspx

        
                               
 

WHERE search_condition

                        
 

       search_condition: là điều kiện để truy vấn dữ liệu. Giá trị được so sánh trong điều kiện, nếu là văn bản th́ phải đặt trong dấu nháy đơn ( ' )  , không như Excel.

  
     

Các toán tử so sánh là:  > , >= , <  , <= , <> , = , LIKE , IN, BETWEEN

              
     

Khi so sánh tập kư tự bất kỳ ta dùng kư tự % , kư tự này dùng với toán tử là LIKE

              
     

Cách thức dùng LIKE tôi sẽ hướng dẫn cách sử dụng trong phần nâng cao.

              
     

Toán tử kết hợp logic: AND , OR , NOT

                     
                               
   

Ví dụ 4

Chọn tất cả các cột từ sổ NKC ở đó mă khách hàng (MA_KH) là KH001

              
     

Công thức=BS_SQL("SELECT * FROM NKC WHERE MA_KH='KH001' ")

              
                               
   

Ví dụ 5

Chọn các cột SO_CT, NGAY_CT, DIEN_GIAI, THANH_TIEN  từ sổ NKC ở đó kư tự đầu của NOTK là 6

        
     

Công thức=BS_SQL("SELECT SO_CT, NGAY_CT, DIEN_GIAI, NOTK, COTK, THANH_TIEN FROM NKC WHERE NOTK LIKE '6%' ")

  
                               
   

Ví dụ 6

Chọn các cột SO_CT, NGAY_CT, DIEN_GIAI, THANH_TIEN  từ sổ NKC ở đó kư tự đầu của cột NOTK là 6 và COTK có kư tự đầu là 3

  
     

Công thức=BS_SQL("SELECT SO_CT, NGAY_CT, DIEN_GIAI, NOTK, COTK, THANH_TIEN FROM NKC WHERE NOTK LIKE '6%'  AND NOTK LIKE '3%' ")
                               
     

Trong search_condition , bạn có thể sử dụng IN (tập danh sách giá trị) để truy vấn

              
     

Sau IN ta có thể là một bảng dữ liệu hay kết của một câu truy vấn SQL

                
                               
   

Ví dụ 7

Chọn tất cả các cột từ sổ KHO ở đó mă hàng (MA_VLSPHH) có trong danh sách mă có loại (DMVLSPHH.LOAI) là VL và của các phiếu nhập

  
     

Công thức=BS_SQL("SELECT * FROM KHO WHERE MA_VLSPHH IN (SELECT MA_VLSPHH FROM DMVLSPHH WHERE LOAI = 'VL') AND LOAI_PHIEU = 'N' ")
                               
 

Kết nối các table

                          
   

Ví dụ 8

Lập một sổ mà gồm có: Mă KH, Tên KH, NOTK, COTK, THANH_TIEN

                
     

Ta thấy Mă KH, NOTK, COTK, THANH_TIEN có trong sổ NKC c̣n Tên KH chỉ có trong DMKH.

          
     

V́ vậy, ta phải liên kết chúng lại và đặt quan hệ giữa NKC và DMKH sau mệnh đề WHERE. Quan hệ là NKC.MA_KH = DMKH.MA_KH

  
                               
     

Công thức=BS_SQL("SELECT NKC.MA_KH, DMKH.TEN, NKC.NOTK, NKC.COTK, NKC.THANH_TIEN FROM NKC, DMKH WHERE NKC.MA_KH=DMKH.MA_KH")
                               
   

Ví dụ 9

Vẫn theo ví dụ 8, thêm điều kiện là NOTK có kư tự đầu là 131

                  
     

Công thức=BS_SQL("SELECT NKC.MA_KH, DMKH.TEN, NKC.NOTK, NKC.COTK, NKC.THANH_TIEN FROM NKC, DMKH WHERE NKC.MA_KH=DMKH.MA_KH AND (NKC.NOTK LIKE '131%') ")
                               
   

Ví dụ 10

Lập một sổ thông tin về phiếu nhập kho (LOẠIPHIEU='N' ) gồm có: Mă hàng, Tên hàng, loại phiếu, Số lượng

        
     

Công thức=BS_SQL("SELECT KHO.MA_VLSPHH, DMVLSPHH.TEN, KHO.LOAI_PHIEU, KHO.SLG FROM KHO, DMVLSPHH WHERE KHO.MA_VLSPHH=DMVLSPHH.MA_VLSPHH AND (KHO.LOAI_PHIEU LIKE 'N') ")
                               
 

GROUP BY group_by_expression: Được dùng khi bạn thông kê theo nhóm đối tượng

                  
   

group_by_expression: Là danh sách các cột được nhóm, các cột được ngăn cách nhau bởi dấu phảy (,)

            
                               
 

--->

Các hàm dùng để thống kê gồm SUM , COUNT , AVG , MIN , MAX

                  
 

--->

Ví dụ: SUM(THANH_TIEN)

                        
                               
   

Ví dụ 11

Lập sổ gồm các cột: MA_KH, Tổng tiền mà NOTK là 131 (tổng hợp số tiền cho từng khách hàng mà phát sinh Nợ TK =131)

    
     

Công thức=BS_SQL("SELECT MA_KH, SUM(THANH_TIEN) AS [Tổng tiền]  FROM NKC WHERE NOTK LIKE '131%' GROUP BY MA_KH")
                               
   

Ví dụ 12

Lập sổ gồm các cột: MA_VLSPHH, Tổng số lượng SUM(SLG) tên cột là [Tổng SLG] mà LOAI_PHIEU là nhập (N)

        
     

Công thức=BS_SQL("SELECT MA_VLSPHH, SUM(SLG) AS [Tổng SLG]  FROM KHO WHERE LOAI_PHIEU LIKE 'N' GROUP BY MA_VLSPHH")
                               
   

Ví dụ 13

Yêu cầu như Ví dụ 10, nhưng cột SLG được tính tổng và có tên là [Tổng]

                
     

Công thức=BS_SQL("SELECT KHO.MA_VLSPHH, DMVLSPHH.TEN, KHO.LOAI_PHIEU, SUM(KHO.SLG) AS [Tổng] FROM KHO, DMVLSPHH WHERE KHO.MA_VLSPHH=DMVLSPHH.MA_VLSPHH AND (KHO.LOAI_PHIEU LIKE 'N') GROUP BY KHO.MA_VLSPHH, DMVLSPHH.TEN, KHO.LOAI_PHIEU")
                               
   

Ví dụ

Lập danh sách gồm mă khách hàng với số tiền phát sinh lớn nhất, điều kiện 3 kư tự đầu của COTK là 511, sắp xếp theo số tiền giảm dần.

  
     

Công thức=BS_SQL("SELECT MA_KH, Max(THANH_TIEN) FROM NKC WHERE COTK LIKE '511%' GROUP BY MA_KH ORDER BY Max(THANH_TIEN) DESC")
                               
   

Ví dụ

Xem thông tin của 3 khách hàng trong DMKH, mà có phát sinh số tiền lớn nhất (trong sổ NKC, COTK LIKE '511%').

      
     

(Mă khách hàng có trong danh sách 3 người có phát sinh số tiền lớn nhất trong sổ NKC)

          
     

Công thức=BS_SQL("SELECT * FROM DMKH WHERE MA_KH IN
(SELECT TOP 3 MA_KH FROM NKC WHERE COTK LIKE '511%' GROUP BY MA_KH ORDER BY Max(THANH_TIEN) DESC)")

  
                               
   

(*)--->

Lưu ư: Các cột sau mệnh đề GROUP BY là tất cả các cột được liệt kê sau mệnh đề SELECT (select_list)
trừ những cột được thống kê bằng các hàm thống kê như: SUM,COUNT,….

   
                               
 

HAVING search_condition

                        
 

       search_condition: là điều kiện để truy vấn dữ liệu giống như dùng với từ khoá WHERE nhưng câu điều kiện có sử dụng các hàm thống kê SUM, COUNT, MIN,MAX, AVG, …
     

Sử dụng khai bao HAVING thường đi kế sau với khai báo GROUP BY

                
   

Ví dụ:

Lập danh sách các mă khách hàng mà có tổng số tiền > 30000000 và 3 kư tự đầu của mă TK là 131 từ sổ NKC

        
     

Công thức=BS_SQL("SELECT MA_KH, SUM(THANH_TIEN) FROM NKC WHERE NOTK LIKE '131%'
                                  GROUP BY MA_KH
                                  HAVING SUM(THANH_TIEN)>30000000")

    
                               
   

Ví dụ:

Lập danh sách các mă hàng có số lần nhập > 4

                    
     

Công thức=BS_SQL("SELECT MA_VLSPHH, count(ma_vlsphh)
FROM KHO
WHERE LOAI_PHIEU = 'N'
GROUP BY MA_VLSPHH
HAVING COUNT(MA_VLSPHH)>4")

    
                               
   

Ví dụ:

Xem thông tin của các hàng hoá trong DMVLSPHH, mà mă hàng có trong danh sách những hàng hoá có số lần nhập (trong sổ KHO) > 4

  
     

Công thức=BS_SQL("SELECT * FROM DMVLSPHH WHERE MA_VLSPHH IN
(SELECT MA_VLSPHH FROM KHO WHERE LOAI_PHIEU = 'N' GROUP BY MA_VLSPHH HAVING COUNT(MA_VLSPHH)>4)")

    
                               
 

ORDER BY order_expression [ ASC | DESC ]

                        
   

Mệnh đề này cho phép sắp xếp theo các cột tăng dần (ASC) hoặc giảm dần (DESC), ngầm đ́nh là tăng dần.

          
   

order_expression: Là danh sách cột được sắp xếp, cột nào đứng trước th́ được xếp trước, các cột được cách nhau bởi dấu phảy (,).

      
     

Có thể không cần chỉ ra tên cột, thay vào đó ta chỉ vị trí cột trong select_list.

              
                               
   

Ví dụ 14

Chọn tất cả các cột từ sổ NKC sắp xếp theo cột NGAY_CT và SO_CT

              
     

Công thức=BS_SQL("SELECT * FROM NKC ORDER BY NGAY_CT, SO_CT ")

              
                               
   

Ví dụ 15

Chọn tất cả các cột từ sổ NKC sắp xếp giảm dần theo cột THANH_TIEN

              
     

Công thức=BS_SQL("SELECT * FROM NKC ORDER BY THANH_TIEN DESC ")

              
                               
                               

+

OPTIONS: Là tham số tuỳ chọn (có thể không có), tham số này là một chuỗi, cho phép khai báo thêm các tham số cho hàm,

       
   

các tham số được ngăn cách nhau bởi dấu chấm phảy (;)

                    
   

Các tham số trong OPTIONS gồm có như sau:

                        
   

DBKEY

Cú pháp DBKEY = dbkeyValue

                        
     

DBKEY : Là tham số khai báo kết nối với CSDL bên ngoài thông qua giá trị dbkeyValue

              
     

Nếu không khai báo tham số này, ngầm định hàm BS_SQL lấy dữ liệu từ workbook đang làm việc.

          
   

(*)--->

Để tạo dbkeyValue bạn vào menu "A-Tools->Cơ sở dữ liệu (CSDL) ->Thiết lập CSDL DBKEY"

            
                             
   

HR

Cú pháp HR=YES|NO

                        
     

Nếu là YES (ngầm định), kết quả bảng dữ liệu có ḍng tiêu đề, NO th́ không có.

              
     

Nếu không khai báo tham số này, ngầm định hàm BS_SQL đặt HR=YES

                
                               
   

NAME

Cú pháp NAME = Tên vùng

                        
     

Cho phép đặt tên vùng kết quả theo tên được khai báo. B́nh thường trong Excel ta phải làm: chọn (bôi đen) vung, nhấn CTRL+F3 và tạo tên.

  
                               
 

v4.0.000

INSERT

Cú pháp INSERT=YES|NO

                        
     

Nếu là YES, bảng kết quả được chèn vào ô hiện thời (các dữ liệu đứng sau sẽ bị đẩy xuống), NO th́ bảng kết quả được ghi đè lên vùng dữ liệu.

  
     

Nếu không khai báo tham số này, ngầm định hàm BS_SQL đặt INSERT=NO

              
     

Khi tạo báo cáo bắt buộc phải đặt INSERT=YES . Mỗi sheet chỉ nên có một báo cáo!

              
                               
   

Ví dụ 16

Chọn tất cả các cột từ sổ NKC. Cơ sở dữ liệu lấy từ file Access,

                 
     

file có tên Examble.mdb đă được kết nối và có dbkeyValue là MDB

                
                               
     

Công thức=BS_SQL("SELECT * FROM NKC" , "DBKEY=MDB")

                  
                               
   

Ví dụ 17

Chọn tất cả các cột từ sổ NKC. Tên vùng kết quả là DATA, không có ḍng tiêu đề

             
     

Công thức=BS_SQL("SELECT * FROM NKC" , "HR=NO;NAME=DATA")

                
                               
   

VÍ dụ 18

Tương tự như Ví dụ 17, nhưng dữ liệu được lấy bên Access, có khoá kết nối là MDB

              
     

Công thức=BS_SQL("SELECT * FROM NKC" , "HR=NO;NAME=DATA; DBKEY = MDB")

            
 

VBA

Có thể khai báo (lập tŕnh VBA) để chạy các sự kiện trong tham số OPTIONS như sau:

              
     

OnBeforeUpdate = DoBeforeUpdate - Cho phép chạy một macro trước khi cập nhật dữ liệu vào bảng tính

        
     

Trong môi trường lập tŕnh (VBE), viết thủ tục "DoBeforeUpdate" trong một module theo cấu trúc sau

          
     

Sub DoBeforeUpdate(ByVal OldDataTable As Range, ByVal NewDataTable As Range, ByVal DataArray)
       'Có thể đổi tên thủ tục
End Sub

    
                               
     

OnAfterUpdate = DoAfterUpdate - Cho phép chạy một macro sau khi dữ liệu được cập nhật

            
     

Trong môi trường lập tŕnh (VBE), viết thủ tục "DoAfterUpdate" trong một module theo cấu trúc sau

          
     

Sub DoAfterUpdate(ByVal DataTable As Range)
       'Có thể đổi tên thủ tục
End Sub

    
     

OnDblClick = DoDblClick - Cho phép chạy một macro khi nhấp đúp chuột vào vùng dữ liệu (có hàm BS_SQL)

        
     

Trong môi trường lập tŕnh (VBE), viết thủ tục "DoDblClick" trong một module theo cấu trúc sau

            
     

Sub DoDblClick(ByVal DataTable As Range, ByVal Row As Integer, ByVal Column As Integer)
       'Có thể đổi tên thủ tục
End Sub

    
     

OnSelectionChange = DoSelectionChange - Cho phép chạy một macro khi con trỏ ô di chuyển trong vùng dữ liệu (có hàm BS_SQL)

  
     

Trong môi trường lập tŕnh (VBE), viết thủ tục "DoSelectionChange" trong một module theo cấu trúc sau

          
     

Sub DoSelectionChange(ByVal DataTable As Range, ByVal Row As Integer, ByVal Column As Integer)
       'Có thể đổi tên thủ tục
End Sub

    
     

OnGetValue = GetValue - Cho phép chạy một macro (hàm) để thay đổi giá trị trong quá tŕnh nhận dữ liệu từ kết quả truy vấn SQL.

  
     

Trong môi trường lập tŕnh (VBE), viết hàm "GetValue" trong một module theo cấu trúc sau

            
     

Function GetValue(ByVal DataArray, ByVal Row As Integer, ByVal Column As Integer, ByVal Value As Variant)
       'Có thể đổi tên hàm
       'GetValue = NewValue
End Function

    
                               
     

DataTable: Là vùng dữ liệu trên sheet, ở đó chứa giá trị của hàm BS_SQL, ḍng (Row) và cột (Column) nhận giá trị từ 1

      
     

DataArray: Là mảng (2 chiều R:C) , ở đó chứa giá trị của hàm BS_SQL trong bộ nhớ, ḍng (Row) và cột (Column) của mảng nhận giá trị từ 0

  
     

Row, Column: Là ḍng, cột của DataTable hoặc DataArray

                  
     

OldDataTable: Là vùng dữ liệu cũ trên sheet (được tạo từ công thức cũ)

                
     

NewDataTable: Là vùng dữ liệu mới trên sheet (được tạo từ công thức mới)

              
                               
     

Xem các ví dụ trong file "Report 3  So ke toan A-Tools VBA.xls"

                
                               
                               

Kế toán:

Ứng dụng hàm ValuesToColumns (VTC)

                        
                               
   

VÍ dụ 19

Lập bảng tổng hợp đối ứng TKNO và TKCO

                      
     

Công thức=BS_SQL("SELECT NOTK, ValuesToColumns(COTK,SUM,THANH_TIEN) FROM NKC GROUP BY NOTK")

    
                               
   

VÍ dụ 20

Lập bảng tập hợp chi phí theo các tài khoản

                      
     

Công thức=BS_SQL("SELECT NOTK, ValuesToColumns(COTK,SUM,THANH_TIEN) FROM NKC WHERE NOTK LIKE '6%' OR NOTK LIKE '8%' GROUP BY NOTK")
                               
 

New

 

ValuesToColumns(ColumnName,Func,ColumnCalc,[FormatDate])

 

 

 

 

 

 

 

 
   

 

 

ColumnName: Tên cột mà các giá trị của nó làm tiêu thức, điều kiện để tính

 

 

 

 
   

 

 

Func: Tên hàm dùng để tính (SUM, COUNT, MIN, MAX, AVG)

 

 

 

 

 
   

 

 

ColumnCalc:Tên cột được tính giá trị theo hàm khai báo ở Func,

 

 

 

 

 
   

 

 

 cột này phải là kiểu số.

 

 

 

 

 

 

 

 

 
   

 

 

FormatDate: Là định dạng ngày. Có thể bỏ qua tham số này,

 

 

 

 

 
   

 

 

nếu kiểu của ColumnName không phải là kiểu Date, hoặc là kiểu Date nhưng dữ liệu đang ở dạng mm/dd/yy.

 
   

 

 

Nếu ColumnName là kiểu Date mà dữ liệu nhập không phải ở dạng mm/dd/yy th́ FormatDate phải là mm/dd/yy
                               
 

New

 

FieldIf(search_condition,ValueTrue,[ValueFalse],[Func])

 

 

 

 

 

 

 

 

 
   

 

 

search_condition: Là biểu thức logic, cách dùng như sau mệnh đề WHERE