External Database Query - CÁC THÍ DỤ THỰC HÀNH
1. ONLY RECORD FROM DSHH AND DATA WHERE
DSHH.MSHH=DATA.MS:
2. Chỉ lấy dữ liệu nào mà có sự tương ứng giữa 2 trường liên kết.
3. 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:
4. - hoặc không liên quan đến nhập xuất ( mà liên quan đến thu chi chẳng hạn)
5. - 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.
6. 2. Tương tự là loại 3: ALL VALUES FROM DATA AND ONLY
RECORD FROM DSHH WHERE DSHH.MSHH=DATA.MS
7. Số dòng dữ liệu là 80 dòng.
8. 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:
9. 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.
10. 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.
11. Vậy tuỳ theo nhu cầu ta sẽ chọn 1 trong 2 loại liên kết trên:
12. - 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.
13. - 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.
14. BƯỚC 2
15. 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
16. 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?
17. - 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.
18. - 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.
19. Các trường còn lại là SlIn, SlOut của Data.
20. ta được Query như sau:
21.
22. 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
23. 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.
24. 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.
25. Ta sẽ được:
26.
27. 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 đó.
28. Đến đây ta hiểu tại sao không lấy trường giá nhập:
29. 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.
30. Tuy nhiên ta còn 1 điều chưa vừa ý về tên trường sumof ...
31. Hãy vào cửa sổ SQL để sửa: mở Menu View - SQL
32.
33. 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
34. Sum(Data.SLIn) AS 'SLNhap', Sum(Data.ttiennhap) AS
'ThanhtienNhap', Sum(Data.SLout) AS 'SlXuat'
35. Kết quả là:
36.
37.
38. 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.
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.
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
