External Database Query: nhiều tables với relations

[ptm0412]

1. Nhắc lại về Database Query:
Cơ sở dữ liệu là gì, xin được phép không nhắc lại.
Lấy cơ sở dữ liệu từ bên ngoài (Text, Access, Fox, . ..) đã được Hanhdung107 trình bày ở bài : - Công cụ Database Query
Tuy nhiên bài đó mới giới thiệu phương pháp lấy dữ liệu từ 1 bảng dữ liệu của Access.
Hôm nay tôi muốn giới thiệu phương pháp lấy dữ liệu từ 2 bảng hoặc 3 bảng trong Access, kèm theo nó là các mối quan hệ (relations) giữa các bảng nhằm những mục đích khác nhau. Tôi sẽ đưa 1 số thí dụ ứng dụng được trong kế toán.

2. Thuật ngữ dùng trong bài:

Trường dữ liệu: (Data Field) nói nôm na là tương ứng một cột dữ liệu trong bảng tính Excel. Trường dữ liệu có thể là 1 trong các kiểu dữ liệu số, ngày, text, luận lý, . . . và trường tính toán.
One to Many : Mối quan hệ 1 ~ nhiều
One to One: Mối quan hệ 1 ~ 1
3. Sơ lược về mối quan hệ:

3.1 Mối quan hệ One to Many:

Trong cơ sở dữ liệu hiện đại, các nhà viết phần mềm hiện nay thường áp dụng cấu trúc dữ liệu kiểu Master - Child theo ngôn ngữ Access, hoặc Parent - Child theo ngôn ngữ Fox, hoặc master - Detail theo ngôn ngữ Oracle. Dù gọi là gì đi nữa thì đều có nghĩa là 1 khối dữ liệu được chia làm 2 phần: phần chung và phần riêng.

a. Thí dụ 1: Phiếu nhập kho, xuất kho
Phần chung gồm những thông tin chung cho 1 tờ phiếu như: Số chứng từ, ngày chứng từ, Nội dung chính (diễn giải), đối tượng nhận.
Phần riêng gồm các mục mã hàng, số lượng, đơn giá, thành tiền.

Ta có thể thấy ngay rằng 1 phiếu nhập hoặc 1 phiếu xuất sẽ có nhiều dòng ứng với nhiều mặt hàng. Nếu ta tạo được mối liên kết giữa số phiếu và các dòng dữ liệu trong phiếu đó thì giữa 1 rừng dữ liệu ta có thể lọc ra các dòng nào của phiếu nhập, xuất nào.
Ở đây ta chọn số chứng từ làm cơ sở cho sự liên kết. Muốn vậy trong phần riêng ta tạo thêm 1 trường cũng là số chứng từ. Mối quan hệ giữa 2 phần ở trên là mối quan hệ One to Many : 1 phiếu nhập / xuất tương ứng nhiều mặt hàng nhập xuất.
b. Thí dụ 2: Phiếu thu tiền
Phần chung gồm số phiếu, ngày phiếu, đối tượng thu, diễn giải
Phần riêng gồm các dòng dữ liệu liên quan đến từng món nợ như liệt kê các khoản thu tương ứng với từng món (thí dụ như thu tiền 3 tờ hoá đơn): số hoá đơn, số tiền của tờ hoá đơn đó, số hợp đồng mua bán nếu có.

3.2 Mối quan hệ One to One

là mối quan hệ 1 ~ 1 như thí dụ sau:
a. Thí dụ 1: Danh sách đối tượng và danh sách liên quan
Giả sử ta có 1 danh sách khách hàng gồm các trường: mã KH, tên KH, mã số thuế, địa chỉ
Và ta lại có 1 danh sách tồn nợ đầu kỳ của một số KH trong số đó, gồm mã KH, số tiền dư nợ, số tiền dư có.
Ta muốn thiết lập 1 mối quan hệ để từ DS 1 ta biết được đối tượng KH đó nợ bao nhiêu trong ADS 2. Vậy ta tạo mối quan hệ giữa trường mã KH của bảng 1 và mã KH của bảng 2.
Chú ý: Là giải thích thì như vậy nhưng khi thực hành, các bạn nên đặt tên trường khác nhau cho cùng 1 loại dữ liệu ở 2 hoặc 3 bảng. Thí dụ MaKH và Ma_KH, SoCT và So_CT . . .

3.3 Mối quan hệ đa phương: (tay ba, tay tư . . .)

Từ các thí dụ trên ta có thể mường tượng các mối quan hệ đa phương:
- 1 phiếu xuất kho liên quan đến nhiều mặt hàng và
- 1 mặt hàng liên quan đến nhiều lần nhập xuất
- 1 khách hàng liên quan đến nhiều lần mua hàng và nhiều lần thanh toán
- 1 Tài khoản quỹ liên quan đến nhiều phiếu thu chi. và liên quan đến các tài khoản khác trong Danh mục tài khoản.
- 1 Dữ liệu gốc liên quan đến DS mặt hàng, DS khách hàng, DS nhà cung cấp, DS Tài khoản KT, Danh mục ngoại tệ . . .
Các mối quan hệ thể hiện trong Access như sau:

 

[ptm0412]

External Database Query: nhiều tables với relations

Các thuật ngữ dùng trong bài:
- Inner relation: Mối quan hệ nội tại giữa bản thân các trường của các table dữ liệu trong 1 cơ sở dữ liệu.
- Outer join : Mối liên kết ngoại lai
- Record: Bản ghi, là 1 dòng dữ liệu.
- Master: là bảng chứa trường là 1 trong quan hệ 1 nhiều
- Child: là bảng chứa trường là nhiều trong quan hệ 1 nhiều
- Query: Truy vấn, là 1 bảng dữ liệu mới gồm các trường dữ liệu lấy từ 2 hoặc 3 bảng dữ liệu, các bảng này có thể có sẵn mối quan hệ với nhau. Query có thể lấy dữ liệu từ cả query khác. Lúc đó giữa table và query đó chưa có quan hệ, ta sẽ tạo mối liên kết ngoại (outer join) giữa các trường của qyery và các bảng, query khác.

4. Ích lợi của các mối quan hệ Inner Relations:
Các mối quan hệ trong bài trước là các mối quan hệ nội tại.
Khi 2 bảng dữ liệu có 1 mối quan hệ Master - Child (1 ~ nhiều):
- Ta không thể tự tiện xóa 1 dòng dữ liệu trong bảng Master nếu đã có dữ liệu liên quan trong bảng Child. Nếu muốn xoá 1 phiếu thu, trước hết phải xoá các chi tiết thu trong Child, sau đó mới được xoá phiếu thu trong Master
- Ta không thể nhập liệu trong Child nếu không có hoặc chưa có record tương ứng trong Master. Thí dụ ta không thể xuất nhập kho 1 mặt hàng chưa được đăng ký mã hàng trong bảng DM vật tư hàng hoá. Hoặc ta không thể bán hàng cho 1 khách hàng chưa có mã trong DS Khách hàng.

Đó là nói về các ràng buộc trong các phần mềm, còn trong Excel ta hiểu điều đó như sau:

- Khi các dữ liệu thuộc Bảng Child có tham chiếu đến dữ liệu từ Master như từ mã hàng của Child ta tìm được tên hàng và đơn vị tính tương ứng trong Master, ta sẽ không nên xoá dòng dữ liệu tương ứng đó bên Master vì sẽ bị lỗi #VALUE bên Child.
- Khi nhập liệu trong Child 1 record chứa dữ liệu chưa có trong bảng Master, các cell có công thức dò tìm dựa trên cell đó đến dữ liệu liên quan trong Master sẽ bị lỗi #VALUE.

Phần 2: Các mối liên kết ngoại: (outer join)

Khi tạo nên 1 Query từ dữ liệu bảng và/ hoặc query khác ta cần phải tạo những mối liên kết dữ liệu giữa chúng. Nhất là khi lấy dữ liệu link từ nguồn ngoài, ta sẽ không lấy được những mối quan hệ có sẵn của chúng mà phải tạo ra.

Tại sao phải tạo mối liên kết ngoại?

Theo nguyên tắc, khi kết hợp 2 bảng, mỗi bảng có lần lượt m và n record, mà không tạo mối liên kết giữa các trường của chúng, thì query mới tạo sẽ có m x n record. Tương tự nếu 3 bảng thì có m x n x p record. Đó là điều chúng ta không bao giờ muốn.

Vậy mối liên kết tạo ra có tác dụng gì, và số record tạo ra cho query mới là bao nhiêu nếu có được mối liên kết? Ta sẽ xem và thực hành 1 số thí dụ.

 

[ptm0412]

External Database Query: nhiều tables với relations
CÁC THÍ DỤ THỰC HÀNH

Giả sử ta có sẵn 2 bảng dữ liệu, 1 Master và 1 Child trong 1 file Access: -
- bảng 1 là DSHH là danh sách các mặt hàng có sẵn
- và bảng 2 là Data là dữ liệu các lần nhập xuất

Cấu trúc như sau:




Bây giờ từ trong Excel ta làm từng bước như sau:

Bước 1:

Mở menu Data - Import External Data - New Database Query
Trong cửa sổ hiện ra double click dòng Access Database,
Trong cửa sổ kế tiếp tìm đường dẫn đến file Access trên, OK. ta sẽ có 1 danh sách các table và query có sẵn trong File Access, chọn DSHH, --> Add, chọn thêm Data, --> Add --> Close.

Hiện lên như sau:



Ta thấy 1 mối liên kết được tạo sẵn từ các trường mang tên id của cả 2 bảng. Ta cần sửa lại mối liên kết này. Click chọn đường kẻ biểu hiện mối quan hệ và doubled-click nó:
Một hộp thoại Joins hiện ra :


Trước hết ta sửa lại bên left là trường MS, bên Right là trường MSHH.

Sau đó Ta có thể chọn 1 trong 3 loại liên kết trong hộp thoại.

 

[ptm0412]

TIẾP THEO

1. ONLY RECORD FROM DSHH AND DATA WHERE DSHH.MSHH=DATA.MS:

Chỉ lấy dữ liệu nào mà có sự tương ứng giữa 2 trường liên kết.
Giả sử DSHH có 13 mặt hàng, DATA có 200 dòng dữ liệu trong đó chỉ có 80 dòng nhập xuất của 12 trong 13 mặt hàng trên, Query sẽ hiện lên đủ 80 dòng của DATA. 120 dòng còn lại:
- hoặc không liên quan đến nhập xuất ( mà liên quan đến thu chi chẳng hạn)
- hoặc nhập xuất vật liệu hoặc nhập xuất công cụ, không phải hàng hoá, mà mã vật liệu, công cụ . . . thì không nằm trong DSHH.

2. Tương tự là loại 3: ALL VALUES FROM DATA AND ONLY RECORD FROM DSHH WHERE DSHH.MSHH=DATA.MS
Số dòng dữ liệu là 80 dòng.

3. Loại 2 là đáng nói hơn cả: ALL VALUES FROM DSHH AND ONLY RECORD FROM DATA WHERE DSHH.MSHH=DATA.MS:
Lấy mọi dữ liệu của DSHH và chỉ những dữ liệu của DATA có liên quan đến DSHH trong trường có liên quan.
Nghĩa là ngoài 80 dòng của DATA có liên quan nhậo xuất đến 12 mặt hàng, 1 mặt hàng còn lại không phát sinh nhập xuất cũng sẽ hiện lên. ta sẽ có 81 dòng.

Vậy tuỳ theo nhu cầu ta sẽ chọn 1 trong 2 loại liên kết trên:
- Nếu chỉ muốn liệt kê các giao dịch nhập xuất phát sinh, ta chọn loại 3.
- Nếu muốn lên bảng cânđối nhập xuất tồn HH với mọi mặt hàng (giả sử cả 2 mặt hàng còn lại đều có số dư đầu kỳ, không phát sinh trong kỳ nhưng sẽ còn tồn cuối kỳ), ta chọn loại liên kết 2.

BƯỚC 2
1. Giả sử ta muốn lập bảng liệt kê số lượng giao dịch và chọn loại liên kết 3
Kéo thả các trường cần thiết vào vùng lưới. Thông thường ta nên kéo trường có mối liên kết từ table Master. Vì sao?
- Khi tạo table DATA, để đơn giản người ta chỉ tạo trường mã, còn tên hàng, đơn vị tính sẽ được dò tìm từ Master. Lấy MSHH từ Master tiện thể lấy luôn tên hàng và đơn vị tính trong Master.
- Nếu muốn lập bảng cân đối NXT thì danh sách lấy từ Master sẽ đầy đủ. Danh sách lấy từ Child chưa hẳn đầy đủ vì rât có thể không phải mọi mặt hàng có xảy ra giao dịch.

Các trường còn lại là SlIn, SlOut của Data.
ta được Query như sau:



2. Giả sử ta muốn lập bảng cân đối nhập xuất tồn và chọn loại liên kết 2

a. Kéo thả các trường tương tự như phần trên, thêm trường Ttiennhap cuả DATA, 2 trường SLDK, Ttiendk của DSHH. Ta không lấy trường giá nhập vì một số lý do sẽ nói sau.
b. Click chọn trường SlIn, nhấn nút Sum trên toolbar. Tương tự làm với trường SlOut và TtienNhap.
Ta sẽ được:



Ta thấy query đã thu ngắn lại chỉ còn vừa đúng 13 dòng cho 13 mặt hàng. mặt hàng thứ 13 không có nhập xuất, chỉ có số dư đâu kỳ. Các dòng còn lại đã được tính tổng sl và tổng giá trị nhập xuất. Hai trường sldk và ttiendk không tính tổng, mặc đinh là Groupby. Một trường Groupby theo thuật ngữ nghĩa là nhóm lại các dữ liệu giống nhau của trường đó.

Đến đây ta hiểu tại sao không lấy trường giá nhập:

Giá nhập 1 mặt hàng có thể không giống nhau trong những lần nhập khác nhau, Nếu nhập với 2 giá khác nhau, mặt hàng đó sẽ bị group thành 2 nhóm, thể hiện lên 2 dòng.

Tuy nhiên ta còn 1 điều chưa vừa ý về tên trường sumof ...
Hãy vào cửa sổ SQL để sửa: mở Menu View - SQL



Sửa lại: Sum(Data.SLIn) AS 'Sum of SLIn', Sum(Data.ttiennhap) AS 'Sum of ttiennhap', Sum(Data.SLout) AS 'Sum of SLout' thành
Sum(Data.SLIn) AS 'SLNhap', Sum(Data.ttiennhap) AS 'ThanhtienNhap', Sum(Data.SLout) AS 'SlXuat'

Kết quả là:




Vẫn còn phải tính thành tiền xuất, sl tồn cuối kỳ và thành tiền tồn cuối kỳ. Bạn có thể kết thúc tại đây, đưa ra Excel và tính bằng công thức Excel. Nhưng cũng có cách tính luôn trên Query. Hẹn lần sau.

 

[ptm0412]

TIẾP THEO

Nếu công thức đơn giản ta có thể tính ngay trên tiêu đề của vùng lưới:

Giả sử tính Số lượng tồn cuối kỳ:
Gõ trực tiếp vào tiêu đề của cột trống trong vùng lưới:
DSHH.SLdk+Sum(DATA.SLin)-Sum(DATA.SLOut)



Kết quả:



Tuy nhiên làm như vậy tên trường sẽ bị đặt tên tự động là Express1009 không hay. Ta sẽ gõ công thức trong SQL:
DSHH.SLdk+Sum(DATA.SLin)-Sum(DATA.SLOut) AS SLCuoiky
Tương tự tính giá bình quân gia quyền:
(Sum(data.ttiennhap)+dshh.ttiendk)/(Sum(Data.SLIn)+dshh.sldk) AS 'gbq'

Ta thấy có thể tính trong Query nhưng khá phức tạp, khi tính đến Thành tiền xuất kho ta không thể lấy trực tiếp gbq nhân SLXuat mà phải lập lại công thức tính gbq và sum(DATA.SlOut). Nó dài ngoằng thế này:

(Sum(data.ttiennhap)+dshh.ttiendk)/(Sum(Data.SLIn)+dshh.sldk)*Sum(DATA.SLOut) AS ThanhtienXuat

Và tính Thành tiền tồn cuối kỳ cũng thế. Vì Excel không hỗ trợ tốt như Access

Vậy các bạn tốt nhất nên dừng lại ở phần tính giá bình quân, còn thành tiền xuất, SL tồn cuối kỳ và Thành tiền cuối kỳ để tính sau bằng hàm của Excel.

Vậy mã lệnh SQL cuối cùng là:

SELECT DSHH.MSHH, DSHH.Tenhang, DSHH.Dvtinh, (Sum(data.ttiennhap)+dshh.ttiendk)/(Sum(Data.SLIn)+dshh.sldk) AS 'gbq', DSHH.sldk, DSHH.ttiendk, Sum(Data.SLIn) AS 'SLNhap', Sum(Data.ttiennhap) AS 'ThanhtienNhap', Sum(Data.SLout) AS 'SlXuat'
FROM {oj `E:\Data\Thanh My\Access\Kho & KHang`.DSHH DSHH LEFT OUTER JOIN `E:\Data\Thanh My\Access\Kho & KHang`.Data Data ON DSHH.MSHH = Data.MS}
GROUP BY DSHH.MSHH, DSHH.Tenhang, DSHH.Dvtinh,

Cuối cùng bạn vào menu File - Return Data to Microsoft Excel. Tính toán cho 3 cột còn lại, trang trí, và in ra.

 

[ptm0412]

TIẾP THEO: MỐI LIÊN KẾT TAY BA

CHI TIẾT 1 MẶT HÀNG (SỔ CHI TIẾT PHÁT SINH)

BƯỚC 2
1. Giả sử ta muốn lập bảng liệt kê số lượng giao dịch và chọn loại liên kết 3

Sử dụng Query trong bài 3 liệt kê giao dịch nhập xuất, nếu ta lọc lấy 1 mặt hàng và lấy thêm số dư đầu kỳ của mặt hàng đó, ta có thể lập 1 bảng chi tiết phát sinh và số dư của mặt hàng đó trong kỳ. ta cần thêm thông tin về CHỨNG TỪ NHẬP XUẤT.
Giả sử ta có sẵn 1 table CtNX liệt kê các phiếu nhập xuất, số CT, ngày, diễn giải.
Công với 2 table DSHH và DATA ta có 3 table. ta cần tạo mối liên kết giữa CTNX và DATA dựa trên Field CTID, 2 field này thực ra có mối quan hệ One to Many từ trước: 1 Phiếu nhập/xuất có nhiều mặt hàng nhập/xuất, nhưng mối quan hệ này không được Excel lấy ra.
CHọn mối quan hệ là loại 1 cho cả 2 mối liên kết, Excel không hỗ trợ cho loại 2 và 3 khi có 3 table trở lên. Như vậy là đủ cho SỔ chi tiết này rồi.

Lấy Query như sau:



Nhấn vào nút Show/Hide Criteria trên tool Bar, chọn field MSHH, gõ a là mã 1 mặt hàng. Để dấu nháy trong field NgayCT, nhấn nút Sort Ascending.

Đưa ra Excel, bằng 1 vài công thức Excel ta sẽ chuyển Query thành 1 sổ chi tiết phát sinh (gọi là thẻ kho cũng được) như sau:



Mã SQL của mối quan hệ tay ba, filter, và sort như sau:

SELECT DSHH.MSHH, DSHH.Tenhang, DSHH.Dvtinh, DSHH.sldk, CTnx.SoCT, CTnx.NgayCT, CTnx.Noidung, Data.SLIn, Data.SLout
FROM `E:\Data\Thanh My\Access\Kho & KHang`.CTnx CTnx, `E:\Data\Thanh My\Access\Kho & KHang`.Data Data, `E:\Data\Thanh My\Access\Kho & KHang`.DSHH DSHH
WHERE CTnx.CTID = Data.ctid AND DSHH.MSHH = Data.MS AND ((DSHH.MSHH='a'))
ORDER BY CTnx.NgayCT

 

[ptm0412]

TIẾP THEO: ĐIỀU KIỆN LỌC THEO Ý MUỐN

CÔNG NỢ KHÁCH HÀNG, CÔNG NỢ NHÀ CUNG CẤP, TỔNG HỢP & CHI TIẾT.

Giả sử ta có 1 table DSKH gồm MSKH, TenKH, MST, DiaChi, dư nợ đầu kỳ và 1 table DSNCC gồm MSNCC, Ten NCC, dư nợ đầu kỳ.
Lưu ý số dư nợ của KH và nhà CC có thể dư nợ và cũng có thể dư có nếu ứng trước tiền hàng.
Lấy bảng CTNX để có thông tin Số phiếu thu, số phiếu xuất bán, số tiền thu, cộng tiền bán

Lập bảng tổng hợp công nợ KH trước:

Mối liên kết là loại 2. Mã KH lấy từ Master để khỏi sót KH không giao dịch trong kỳ này nhưng có dư nợ đầu kỳ.

Tính sum của số tiền thu và sum của cộng thành tiền bán trong CtNX



Nhưng ta lại thấy khách hàng mã y (Yến) không dư nợ đầu kỳ, không mua hàng, không trả tiền, và ta không muốn y ta hiện lên báo cáo. ta thêm đk lọc loại y ta ra:
Nhấn vào nút Show/Hide Criteria trên tool Bar, gõ dòng sau vào Criteria field:
dunodk+ducodk+IIf(Sum(congtienNX)>0;Sum(congtienNX);0)+IIf(Sum(ttoan)>0;Sum(ttoan);0)

ở Criteria gõ: >0

Ghi chú: IIf có 2 chữ I

Mục đích là loại những KH không có số dư đk, không giao dịch trong kỳ.

Tại sao phải có hàm IIf()?

Vì trong 2 field tính toán mới tạo có những record bị null, công thức cộng trong Query gặp null thì phép cộng cho ra kết quả 0 (trong Access cho kết quả #VALUE!).



Đưa ra Excel, tính toán bằng công thức ra số dư cuối kỳ.




Bạn có thể trang trí lại.

Bảng tổng hợp công nợ nhà cung cấp làm tương tự như trên với 2 bảng CtNX và DSNCC.

 

[ptm0412]

TIẾP THEO: CHI TIẾT CÔNG NỢ NHÀ CUNG CẤP:

Làm theo minh hoạ, tương tự như Sổ chi tiết hàng hoá



Đưa ra Excel, tính toán trình bày lại:

 

[ptm0412]

TIẾP THEO: SỔ CÁI CHI TIẾT TÀI KHOẢN
MỐI LIÊN KẾT TAY TƯ

Giả sử ta có 4 table :
- DSTK
- CtKT
- NkKT
- Sodu01

Có cấu trúc và mối quan hệ trong Access như sau:



Ta thấy rõ các mối quan hệ 1 ~ nhiều và 1 ~ 1. Tuy nhiên đưa vào Excel ta phải tạo lại chúng.



Giả sử ta muốn sổ chi tiết tài khoản 111 Tiền mặt. Trong vùng Criteria Field, chọn lần lượt 2 field TKno và TKco, Criteria là '111' và or '111'
Trong vùng grid:
- Lấy các trường DmTK.SoTK, DmTK.Ten, Sodu01.SdNo, Sodu01.SdCo là thông tin về tài khoản
- Lấy các trường Ctkt.sct, Ctkt.date, Ctkt.ctgoc, Ctkt.ngaygoc, Ctkt.diengiai là thông tin về các chứng từ
- Ta cần thông tin về tài khoản đối ứng: IIf(tkno='111',tkco,tkno)
- Và số tiền ghi nợ, số tiền ghi có.

Nhưng số tiền trong NKKt ghi chung 1 field nên phải tách ra:
Số tiền ghi nợ: IIf(tkno='111',stien,0)
Số tiền ghi có: IIf(tkco='111',stien,0)
Các công thức này gõ trực tiếp vào tên field hoặc sửa trong SQL:



SELECT DmTK.SoTK, DmTK.Ten, Sodu01.SdNo, Sodu01.SdCo, Ctkt.sct, Ctkt.date, Ctkt.ctgoc, Ctkt.ngaygoc, Ctkt.diengiai, iif(tkno='111',stien,0) as 'Sotien_no', iif(tkco='111',stien,0) as 'Sotien_co', iif(tkno='111',tkco,tkno) as 'TK_doiung'
FROM `E:\Data\Thanh My\Access\Accounting`.Ctkt Ctkt, `E:\Data\Thanh My\Access\Accounting`.DmTK DmTK, `E:\Data\Thanh My\Access\Accounting`.NkKt NkKt, `E:\Data\Thanh My\Access\Accounting`.Sodu01 Sodu01
WHERE Ctkt.id = NkKt.ctid AND Sodu01.MsTK = NkKt.TKCo AND Sodu01.MsTK = DmTK.SoTK AND ((NkKt.TkNo='111') OR (NkKt.TKCo='111'))
ORDER BY Ctkt.date




Đưa ra Excel:





Tính toán trang trí lại ta sẽ có sổ cái chi tiết TK 111 theo ý bạn.

 

[ptm0412]

TIẾP THEO: SỔ CÁI TỔNG HỢP TÀI KHOẢN

ADD TABLE 2 LẦN ĐỂ ĐƯỢC 2 MỐI LIÊN KẾT TỪ 1 FIELD TỚI 2 FIELD CỦA CÙNG 1 TABLE KHÁC

Giả sử ta muốn tạo Sổ cái tổng hợp TK 331.1

Trong sổ cái tổng hợp ta sẽ không cần thông tin về các chứng từ nên ta sẽ không lấy table CtKT.
Mà ta lại cần số hiệu và tên của tài khoản đối ứng. Số hiệu TK đối ứng ta đã biết làm, còn tên, cần phải có mối quan hệ liên kết đến Số hiệu TK đối ứng . Thế mà ta lại không có sẵn. Ta có thể lấy gián tiếp qua mối quan hệ giữa table DmTK và TKno, TKco của Data.
Tạo 2 mối liên kết từ 1 field của table này đến 2 field của 1 table khác là không thể. Nhưng ta có thể Add 1 table 2 lần:
Xem mối liên kết sau:



Ta thấy table DmTK khi add lần 2 bị đổi tên DmTK_1. Ta sẽ lấy tên TK đối ứng từ 2 bảng này:
Trước đây ta gõ trực tiếp công thức vào tên field và sửa hiển thị tên field trong SQL, ta có 1 cách khác cũng hay và không cần biết SQL:
Mở Menu Record - Add Column:



Gõ vào Field công thức của trường tính toán, gõ vào Column Heading tên field bạn muốn hiển thị.

- Lấy số hiệu TK đối ứng: iif(tkno='331.1';tkco;tkno)
- Lấy tên TK đối ứng: IIf(tkno='331.1';Dmtk.ten;Dmtk_1.Ten)
Lưu ý DMTK liên kết đến TKco và DmTK_1 liên kết đến TKno để lấy tên cho đúng.
- Lấy sum của số tiền nợ: Sum(iif(tkno='331.1';stien;0))
- Lấy sum của số tiền có: Sum(iif(tkco='331.1';stien;0))
- Lấy thêm MsTK, số dư nợ, số dư có

ta được:



Đưa ra Excel:



Bạn trang trí lại để được sổ cái như ý.

 

[ptm0412]

TIẾP THEO : BẢNG CÂN ĐỐI PHÁT SINH

3 QUERY CHO 1 BÁO CÁO

Với dữ liệu đã cho ở phần trước, trong Access việc tạo ra các báo cáo là dễ dàng vì Access hỗ trợ tốt các mối quan hệ, các mối liên kết. Ngoài ra Access còn tạo và lưu các Query trung gian để từ Query này tạo nên query khác thỏa mãn yêu cầu của chúng ta.
Đối với báo cáo Bảng cân đối phát sinh, trong Access tôi tạo 2 Query trung gian và 1 query chính lấy dữ liệu từ 1 table và 2 query này. Trong Excel thì không thể.

Tuy nhiên ta sẽ biến điều không thể thành có thể:

Tạo Query thứ nhất lấy số dư đầu kỳ của các tài khoản, liệt kê tất cả tài khoản trong DmTK dù có số dư hay không. (Bằng cách chọn loại liên kết). Đưa ra Excel vào 4 cột từ A đến D



Tạo query thứ hai lấy tổng phát sinh bên nợ các TK, liệt kê tất cả TK dù có phát sinh nợ hay không. Đưa ra Excel 2 cột E và F



Tạo query thứ 3 lấy tổng phát sinh bên có các TK, liệt kê tất cả TK dù có phát sinh có hay không. Đưa ra Excel 2 cột G và H



Coi trên Excel khá là xấu xí:



Phải dấu bớt cột E và G, tính toán 2 cột dư nợ và dư có cuối kỳ và trang trí lại. Hoặc tạo 1 báo cáo đàng hoàng ở sheet khác với số liệu từ sheet này.

 

[ptm0412]

TIẾP THEO: LẤY QUERY BẰNG LỆNH VBA

Một số Query bạn chỉ cần lấy 1 lần rồi sau đó khi có sự thay đổi dữ liệu gốc trong file Access, bạn chỉ cần nhấn Refresh, dữ liệu sẽ tự động update. Đặc biệt là các Query nhằm tạo báo cáo tổng hợp như: Tổng hợp công nợ nhà cung cấp, tổng hợp công nợ khách hàng, tổng hợp nhập xuất tồn vật tư hàng hoá, Bảng cân đối phát sinh.
Riêng những báo cáo chi tiết như: Sổ Chi tiết nhập xuất 1 mặt hàng, Sổ chi tiết công nợ 1 khách hàng, sổ cái tài khoản, chúng ta thấy khá là khổ công nếu làm cho mỗi đối tượng hoặc mỗi tài khoản 1 query riêng. Ta nhớ lại khi làm các query loại này ta có sử dụng bộ lọc theo đối tượng. Nếu có cách nào chỉ cần chọn 1 đối tượng trong DS cho vào 1 cell nào đó của Excel, rồi Excel tự lấy query (xác định trước) theo đối tượng đó thì hay. Cái này làm ta liên tưởng ngay đến Macro của VBA.
Trước tiên ta cần cái DS đó trước. Giả sử là DS nhà cung cấp và ta muốn các sổ chi tiết công nợ nhà cung cấp. Vậy thì ta thực hành ngay lấy DS bằng VBA:
Cách dễ nhất để tạo 1 đoạn code VBA mà bạn chả biết tí gì về VBA là Record Macro.

Bật record Macro lên, làm từng bước để lấy table DSNCC trong file Access, chỉ cần 2 trường là đủ. Sau khi kết thúc ra Excel xong, tắt record macro.
Mở cửa sổ code ra xem ta thấy đoạn code:

<FONT style="BACKGROUND-COLOR: #f5f5ff">

Sub GetData()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=E:\Data\Thanh My\Access\Kho & KHang.mdb;_
DefaultDir=E:\Data\Thanh My\Access;DriverId=25;FIL=MS Access" _
), Array(";MaxBufferSize=2048;PageTimeout=5;")),_
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT DSNCC.msNCC, DSNCC.TenNCC" & Chr(13) & "" & Chr(10) &_
"FROM `E:\Data\Thanh My\Access\Kho & KHang`.DSNCC DSNCC" _
)
.Name = "Query from MS Access Database"
. . .
.Refresh BackgroundQuery:=False
End With
End Sub

Xem cho biết thôi, vì cái chính là ta cần cái query Chi tiết nhà CC kia.

Lấy lại cái Query Sổ chi tiết nhà cung cấp w: Win Engineering ở bài trước mà có record macro: ta sẽ có đoạn code:

Sub GetcongnoNCC()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=E:\Data\Thanh My\Access\Kho & KHang.mdb;_
DefaultDir=E:\Data\Thanh My\Access;DriverId=25;FIL=MS Access" _
), Array(";MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT DSNCC.msNCC, DSNCC.TenNCC, DSNCC.MST, DSNCC.Dunodky,_
DSNCC.Ducodky, CTnx.NgayCT, CTnx.SoCT, CTnx.Noidung,_
CTnx.TratienNCC, CTnx.CongtienNX" & Chr(13) & "" & Chr(10) _
& "FROM `E:\Data\Thanh My\Access\Kho & KHang`.CTnx CTnx," _
, _
" `E:\Data\Thanh My\Access\Kho & KHang`.DSNCC DSNCC" & Chr(13) & "" & Chr(10)_
& "WHERE DSNCC.msNCC = CTnx.NhaCC AND ((DSNCC.msNCC='w'))" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

End Sub

Chú ý phần tô đậm màu đỏ: WHERE DSNCC.msNCC = CTnx.NhaCC AND ((DSNCC.msNCC='w'))". Đây là cái mà ta cần.
nêu ta thay DSNCC.msNCC='nt' ta sẽ được chi tiết của nhà cung cấp Nam Thịnh có mã 'nt'.
Ta có thể dùng 1 validation tại cell L1 với list là cái DS lúc nãy mới lấy xuống, nhưng validation chỉ có 1 cột mã, không hình tượng lắm.

Tôi thích dùng combobox hơn, nó cho phép coi 2 cột vừa mã vừa tên nhà CC, dễ chọn lựa.



Double click vào cái combo tạo code cho combo như sau:

Private Sub ComboBox1_Change()
GetcongnoNCC
End Sub

Còn cái Code GetcongnoNCC ta phải sửa 1 tí:
Thêm vào sub ở ngay trên cùng:

Dim MS as string
MS=combobox1.value
Range("a1:j7").Clear

Câu màu đỏ sửa như sau:
"WHERE DSNCC.msNCC = CTnx.NhaCC AND ((DSNCC.msNCC=' " & MS & " '))"

Chú ý dấu nháy đơn màu đỏ.

Bạn sẽ có được 1 Macro hoạt động ngon lành: Chỉ cần chọn tên NCC trong combobox:





Tương tự bạn làm combobox cho tài khoản KT, DS khách hàng, DS mặt hàng.

 

[ptm0412]

TIẾP THEO VÀ HẾT: LẤY DỮ LIỆU TỪ NGUỒN KHÁC ACCESS

Microsoft Database Query có thể lấy dữ liệu từ nhiều nguồn. Giả sử bạn có 1 phần mềm kế toán và bạn biết phần mềm viết bằng ngôn ngữ gì, bạn biết địa chỉ lưu giữ dữ liệu, và quan trọng nhất bạn biết table nào có nội dung nào, bạn có thể truy cập vào dữ liệu thông qua Database Query.

1. Lấy từ Visual Foxpro:
a. Trước tiên bạn phải cài đặt driver cho Visual Foxpro database:
- Mở menu Data, Import External data - Import Data
- Trong màn hình hiện ra bạn click new source
- Trong màn hình kế tiếp chọn Other/Advanced, click next
- Kế đến chọn Microsoft OLE DB for Visual FoxPro, click next
- Chọn trong mục số 1, click nút vuông kế bên mục số 1
- chọn free table
- nhấn nút browse, chọn đường dẫn đến folder chứa dữ liệu phần mềm, OK



- OK lần nữa hoặc Cancel, vì bạn không muốn lấy 1 bảng dữ liệu, bạn muốn tạo query cơ.
b. Tạo query: làm bình thường như các bài trên đã hướng dẫn

2. Lấy từ nguồn khác: Oracle, SQL, v.v...

Làm tương tự như Visual Foxpro

TIẾP THEO: LẤY DỮ LIỆU TỪ NGUỒN EXCEL

Uhm. Các bạn thích Excel. Thì đây: Excel

Bạn có thể tạo bộ cơ sở dữ kiệu từ Excel. OK.

1. Bạn phải tổ chức dữ liệu 1 cách khoa học:
- Phải có các bộ mã: mã KH, mã NCC, mã NVL, mã HH, mã TK. Mỗi bộ mã 1 bảng Excel, có thể cùng sheet nhưng khác cột. Mã không được trùng nhau, tên field không được trùng nhau. bạn có thể kết hợp Mã, tên, và số dư đầu kỳ cho mỗi bảng mã.
- Thiết lập các table dữ liệu thô, tốt nhất là tách ra mỗi bộ dữ liệu 1 master, 1 Child. Tạo sẵn các field có thể liên kết giữa master và child bằng quan hệ One to many. Có thể dùng field có sẵn như DSHH.MaHH và DataNX.Ma_HH, DSTK.MaTK và DataKT.TKno, nhưng có khi phải tạo thêm 1 field như CTNX.SoCT và DataNX.So_CT, CTKT.SoCT và DataKT.So_CT.
Các dữ liệu thô này không có công thức hoặc chỉ có công thức nhân (nhân đơn giá ra thành tiền) thậm chí không cần thành tiền, trừ số tiền trong hạch toán tài khoản không có số lượng đơn giá.
Mỗi table dữ liệu thô nên để riêng 1 sheet.
Tất cả table phải bắt đầu từ dòng 1, với dòng 1 là dòng tên field không merge cell.
- các field như tên hàng, đơn vị tính, tên tài khoản, tên KH, mã số thuế . . . chỉ nên đặt trong master, trong detail không cần và không nên. Trong detail chỉ cần các field mã và mã. Càng đơn giản càng tốt.

2. Bạn phải đặt tên vùng dữ liệu (name) cho mỗi table. tên chọn theo nội dung chính của dữ liệu và không nên dài quá như có thể đặt: DSHH, DMTK, DSKH, DSNCC, CTNX, DataNX, CTKT, DataKT.
Tôi đã định đặt tên vùng dữ liệu giới hạn bằng số dòng không trống, nhưng sau khi học cách đặt name trên GPE và làm thử, MS Query không nhận dạng được các table này. Vậy là phải đặt name bằng đủ số dòng của Excel ( 2^16>65.000 dòng). Bạn chọn hết cả các cột của vùng dữ liệu muốn đặt tên và vào menu insert - name - Define, chọn name, thí dụ:
DSHH=(A:E)
DSKH=(F:K)
DMTK=(L:O)
CTNX=Sheet2!A:H
. . .

3. Nhập liệu:
Bạn có thể tham khảo trên GPE cách thức nhập liệu cùng lúc 2 sheet liên quan như nhập liệu phiếu nhập kho liên quan đến 2 sheet CTNX và DataNX, (dùng VBA), hoặc bạn sử dụng form nhập liệu. Làm sao cho dữ liệu càng nhẹ càng tốt (ít công thức)
4. Lưu file và đóng lại.
5. Tạo file mới lấy các query và tạo các báo cáo từ query đó. khi chọn External database - new query, chọn from Microsoft Excel, chọn tên file dữ liệu thô trên, làm bình thường như hướng dẫn của các bài trên.

Theo thử nghiệm của tôi trước khi viết bài này, Excel lấy dữ liệu từ Excel cũng nhanh, nhưng khi cần Edit Query, anh Bill bắt chọn lại các field từ đầu. Nếu gặp màn hình đòi add field lại, các bạn nhấn cancel , sau dó khi anh Bill hỏi có edit query không thì nhấn Yes. Chậm hơn mấy bước so với lấy từ Access.

Để kết thúc

Đến đây tôi xin kết thúc loạt bài về Database Query, nhiều table với relationships. Hy vọng rằng với kiến thức nhỏ nhoi của tôi, loạt bài này sẽ giúp ích được cho các bạn không nhiều thì ít. Các bạn nếu có thắc mắc hoặc đóng góp gì xin vui lòng thẳng thắn góp ý, tôi sẽ sửa sai hoặc nhờ các cao thủ thực sự thứ thiệt giúp thêm.
Các chê khen góp ý xin vui lòng post vào topic sau:
http://www.giaiphapexcel.com/forum/showthread.php?t=7863

XIN CHÂN THÀNH CÁM ƠN TẤT CẢ CÁC BẠN.

 

[ptm0412]

HỖ TRỢ NHẬP LIỆU BẰNG FORM VÀO DATABASE TRONG EXCEL

Tôi có tạo 1 form nhập liệu tự động cho 1 cơ sở dữ liệu chuyên đề Kho và Khách hàng, sử dụng database như của File Access trong các bài trên.
Như đã nói, vì trình độ có hạn nên form này chỉ là 1 ý tưởng, không phải là hay. Các bạn hãy cải tiến thêm và tạo thêm những form nhập liệu trả tiền NCC, thu tiền KH, xuất bán hàng.
Riêng Database phần DSHH có hỗ trợ 3 loại giá bán tương ứng với 3 loại Khách hàng mô tả trong DSKH (với giả định rằng công ty bạn có áp dụng chính sách giá khác nhau cho các loại KH khác nhau). Nếu tạo form nhập liệu xuất bán, ta có thể truy xuất giá bán dựa trên loại KH nếu dùng combobox DSKH có thêm cột loại KH.
Cụ thể là:
Combobox1: KH: có 3 column: MaKH, TenKH, LoaiKH
Combobox2: MsHH: có 5 column: MaHH, TenHH, Price1, Price2, Price3

Khi chọn mã KH trong combobox1 xong, chọn tới mã HH trong combobox2:

sub combobox2_afterupdate
Select Case combobox1.column(2)
Case 1
Me.Giaban = Me.Combobox2.column(2)
Case 2
Me.Giaban = Me.Combobox2.column(3)
Case 3
Me.Giaban = Me.Combobox2.column(4)
End Select
Me.TTban = Me.Giaban * Me.Sl
Cong = Cong + Me.TTBan
. . .
End Sub

Các bạn cũng có thể cài làm sao cho 1 form sử dụng chung vừa nhập kho, vừa xuất kho.

Chúc Thành Công.

 

[ptm0412]

Chú ý: Chỉ 1 Form nhập liệu vừa xuất kho, vừa nhập kho

Nhằm hỗ trợ cho các bạn nhập liệu cho CSDL có mối liên kết giữa 2 table nằm ở 2 sheet khác nhau, nghĩa là nhập liệu 1 lúc ở cả 2 sheet, tôi đã tạo 1 form nhập liệu nhập kho như ở bài trên.
Nay làm thêm phần nhập liệu xuất bán với giá bán tự động chọn theo loại KH. Nhưng xài tiếp form cũ, không cần tạo thêm form mới.
Tôi đã từng dùng 1 form 10 lần cho 10 mục đích khác nhau trong file Access Kho&KHang đã post lên, nhưng trong Excel, phương pháp giống mà thủ thuật khác, phải mày mò lại. Rồi cũng ra.
Form kỳ này có ứng dụng Input Mask __/__/____ cho textbox dạng dữ liệu ngày của MrOKBab, cám ơn bạn ấy.