Góp ý, thắc mắc trong đề tài Database Queries, nhiều tables . . . (1 người xem)

[ptm0412]

Tôi mở chủ đề Database Queries, nhiều tables với relations:
http://www.giaiphapexcel.com/forum/showthread.php?t=7358
dựa trên ý kiến riêng của tôi:
1. Cơ sở dữ liệu khi được tổ chức tốt, chỉ toàn là dữ liệu thô, phân ra các table và các trường dữ liệu. Các table này cần có những relationship ràng buộc lẫn nhau.
2. Tính toán, lọc lựa, trích xuất ra báo cáo có nhiều cách khác nhau, nhưng dùng query là biện pháp tiết kiệm tài nguyên máy tính tốt nhất, vì cái không tốt duy nhất của Excel là chiếm dụng bộ nhớ của máy tính, cả dung lượng file lẫn Ram. Ngoài ra mỗi lần có 1 thay đổi nhỏ, Excel tính lại toàn bộ worksheet làm mất thì giờ nhất là với cơ sở dữ liệu lớn..
Trong khi đó nếu ta dùng query, mỗi query chỉ được lưu như 1 câu lệnh SQL, khi mở file, nó chỉ thể hiện như những số liệu thô không tính toán, không công thức.

Tôi đã viết qua 12 bài từ cơ bản đến nâng cao, từ đơn giản đến phức tạp, từ thấp đến cao, áp dụng 1 số thủ thuật mà tôi đã từng làm trong Access.
Tôi đã cố tạo ra 1 file dữ liệu giả lập và mày mò thực nghiệm trước, cho đến khi query hoạt động tốt rồi mới dám đưa lên.
Tôi cũng đã cố ứng dụng query vào công việc kế toán với 1 số mẫu sổ sách, báo cáo cơ bản.
Nhưng không biết trong quá trình viết bài có bị nhầm lẫn hay thiếu sót gì không, vì Excel không phải là sở trường của tôi.
Nay tôi mở topic mới này những mong các bạn cao thủ giúp đỡ, góp ý, vạch ra những thiếu sót của tôi để tôi có thể sửa chữa và hoàn thiện nốt chủ đề.

Đồng thời đối với các bạn khác, không biết các bài viết của tôi có giúp ich cho các bạn ít nhiều gì không, cũng xin các bạn góp ý, thắc mắc, chê khen, post lên trong topic này. Nếu trong khả năng, tôi sẽ giải thích, nếu sai tôi sẽ sửa, nếu ngoài khả năng thì nhờ các cao thủ khác giúp thêm.

Xin cám ơn tất cả các bạn.

 

[TranThanhPhong]

Chào anh ptm0412,

Em thấy bài của anh viết rất hay và rất thực tế. Em không có góp ý nào về phần nội dung bài viết, tuy nhiên để thuận tiện cho người đọc và học thì em nghĩ cần có CSDL Access mà anh demo trong suốt bài viết khi đó sẽ dễ tiếp cận hơn và nếu gặp khó khăn sẽ có phản hồi ngay.

Thân,
Thanh Phong

 

[ptm0412]

Thanks Thanh Phong, mình đưa file lên đây.
Hai file Access này mình làm vội nên chưa hoàn chỉnh các query, form và report. Trong đó file Accounting chưa có cái form hoặc report nào. Còn file Kho&KH thì tương đối.
Nhưng mình còn chưa vừa ý cái số dư công nợ chỉ 1 bên, nên nếu dư có sẽ thể hiện âm. Sửa query thì nhanh nhưng sửa report lâu lắm.

Các bạn cứ thực hiện theo hướng dẫn của bài viết, chỗ nào thắc mắc thì hỏi, nếu phát huy hay hơn hoặc làm hay hơn cũng xin vui lòng chia sẻ cho tôi và mọi người được học hỏi.

 

[ptm0412]

CHÚ Ý: FORM HỖ TRỢ NHẬP LIỆU VÀO DATABASE TRONG EXCEL

Không biết chủ đề Database Query có khô khan quá không, hay các bạn không thích hoặc không có dữ liệu từ phần mềm; mà đã lâu không thấy ai phản hồi ý kiến cho topic ấy.
Đoán rằng các bạn thích Excel và muốn làm trong Excel, nay tôi đã tạo 1 file Excel là 1 CSDL đã tổ chức tốt, kèm theo là 1 form nhập liệu tự động nhập kho hàng (Xem bài 14 topic ấy http://www.giaiphapexcel.com/forum/showthread.php?p=54818#post54818 )

Các bạn dùng thử, nếu được thì các bạn phát triển thêm, vì tôi vẫn cho rằng nhâp liệu CSDL và tính toán từ CSDL ra các báo cáo là 2 việc khác nhau, độc lập với nhau. Nếu CSDL được tổ chức tốt và bạn sử dụng thành thạo Database Query, bạn sẽ được ít nhất 2 điều lợi:
- File Excel của bạn không bị nặng và chạy chậm khi Dữ liệu lớn
- Bạn không cần sử dụng nhiều công thức phức tạp mà vẫn có thể tính tổng với nhiều điều kiện.

Hơn thế nữa:
- Bạn có thể lọc dữ liệu với nhiều tiêu chí cùng lúc
- Thậm chí bạn có thể cho dữ liệu nhiều tháng vào CSDL và chỉ cần lọc theo tiêu chí thời gian, lấy ra báo cáo của 1 tháng.
- Nếu kết hợp với VBA theo cách của bài hướng dẫn, việc tạo báo cáo sẽ trở nên tự động và bạn chỉ việc nhấn nút.

Và mong mỏi các bạn góp ý thêm.

 

[Nguyễn Duy Tuân]

Chào bạn ptm0412, tôi đã đọc các bài viết về Query..của bạn. Bạn rất tâm huyết và đã hướng dẫn chi tiết về cách dùng công cụ Query cho cho người dùng. Đây là những kiến thức rất hay và quan trọng với những người quản trị CSDL. Tôi cảm nhận hiện nay những người đang dùng Excel khái niệm về SQL còn hơi xa lạ? Dù vậy tôi vẫn hy vọng những khái niệm về CSDL có quan hệ với các câu lênh truy vấn SQL sẽ dần phổ biến với người dùng Excel vì sức mạnh của nó (tất cả các PM liên quan tới CSDL phấn lớn là dùng SQL). Bản thân tôi cũng đang cố gắng truyền tải những kiến thức SQL trên GPE này nhưng tôi cũng cảm nhận như một mình tôi đang đi trên một con đường (xét về khía cạnh đóng góp của các thành viên). Chỉ cần biết nó là tốt thì mình cứ làm bạn ạ! Thế giới quanh ta họ biết và đang rất cần bạn à!
Tôi thấy bài viết của bạn rất hữu ích! Mong bạn tiếp tục viết về chủ đề này như đã đang thực hiện.
Các kiến thức về SQL tôi cũng đã thể hiện trong rất nhiều ví dụ trong A-Tools, bạn có thể tham khảo phiên bản mới tại đây:
http://www.bluesofts.net/Products/AddIns/A-Tools/Setup_A-Tools.exe

Chúc bạn năm mới mạnh khoẻ và thêm nhiều thành công!

(Em không biết tuổi của bác nên dùng từ "Bạn" có gì không phải xin bác lượng thứ).

 

[ptm0412]

Ý bạn là độc hành trên 1 con đường = độc hành đại đạo?
Hìi, hôm qua mình cũng nghĩ thế, vậy là không phải độc hành nữa rồi.
Có điều, không phải chỉ những người quản trị cơ sở dữ liệu mới cần quan tâm. Số này ít lắm, 1 công ty chỉ có 1 vài người quản trị CSDL, công ty vừa và nhỏ có khi không có ai quan tâm.
Ý mình muốn nói là: với mọi file dữ liệu bạn có và đang thực hiện, dễ hoặc khó, đơn giản hoặc phức tạp, hãy quản lý nó như 1 CSDL.
Thí dụ
- quản lý hồ sơ nhân viên dù chỉ 10 người hay 1.000 người
- quản lý việc in phiếu xuất nhập (in sẵn và đóng thành cuốn có đánh số và có serie )
- quản lý tủ sách gia đình hay tủ sách thư viện
- hồ sơ học sinh của riêng 1 giáo viên hay của cả trường
v. v ....

(Em không biết tuổi của bác nên dùng từ "Bạn" có gì không phải xin bác lượng thứ).

Hic, tết này, ngày mai, mình bị 47t, già mất rồi. Chỉ muốn làm bạn với mọi người thôi (cả nam và nữ, cả già và trẻ). Chỉ cần tôn trọng lẫn nhau, xưng hô không quan trọng lắm.

TB: Xin hiểu theo nghĩa đẹp của từ bạn, ví như Bá Nha, Tử Kỳ.
Nhớ năm xưa mình phải làm 1 bài văn về bạn và 1 bài về người vợ hoặc chồng, vì ít chữ mình đã ăn gian chỉ làm 1 bài: Ma femme est mon ami aussi!!!!

 

[Nguyễn Duy Tuân]

Hi, vậy em đã biết anh rõ hơn rồi. Em kém anh 14 tuổi.
Ý của em "quản trị CSDL" là người làm việc với dữ liệu chứ không phải chỉ là Admin. Em nói không rõ nghĩa.

 

[ptm0412]

ĐÃ CÓ FORM NHẬP LIỆU XUẤT BÁN
Tôi đã tạo thêm form nhập liệu xuất kho bán hàng ở đây http://www.giaiphapexcel.com/forum/showthread.php?p=55016#post55016
cộng với nhập liệu nhập kho của bài trước là đủ cho file CSDL tôi đưa lên lần trước. Các dữ liệu trong các danh mục có thể nhập trực tiếp vào các sheet liên quan vì chỉ nhập riêng lẻ. Trong file có đặt các name động theo hướng dẫn của thầy Tedaynui, nên dữ liệu nhập thêm vào các DM sẽ tự động cập nhật vào form.
Code của form cũng rất đơn giản, các bạn mới có thể nghiên cứu. Nếu muốn dùng cho CSDL của riêng mình, các bạn cứ cải tiến lại. Có thắc mắc gì các bạn cứ hỏi.

Và nhờ các cao thủ góp ý thêm.

 

[levanduyet]

Trong file có đặt các name động theo hướng dẫn của thầy Tedaynui, nên dữ liệu nhập thêm vào các DM sẽ tự động cập nhật vào form.

Khi chúng ta đã dùng Access làm CSDL thì việc tạo ra các bảng trên Excel chỉ là tạm thôi, nên tôi đề nghị không nên dùng name động. Tại sao?
_ Khi người dùng nhập vào các bảng trên Excel (với name động) và việc tính toán là Auto thì người dùng sẽ chán nãn vì tốc độ của nó. (Tôi đã có kinh nghiệm về việc này. Người dùng không chấp nhận được!)
_ Trong lúc người dùng lỡ xóa đi "không đúng nguyên tắc" thì bảng dữ liệu sẽ coi như "đi tong". Vì vậy việc cập nhật chúng ta có thể đưa vào một thủ tục để làm việc này.

Lê Văn Duyệt

 

[ptm0412]

Cám ơn Anh Duyệt. Tuy nhiên:
- 3 bài cuối của loạt bài bên kia là tôi viết cho CSDL là Excel, MirosoftQuery lấy nguồn từ Excel, không còn Access. Tôi cũng đã viết rằng CSDL nên là dữ liệu thô không tính toán, nên chắc không ảnh hưởng đến tốc độ.
- Name động trong bài là name tự động thay đổi kích thước khi dữ liệu thêm hoặc bớt, chỉ dùng cho các table danh mục, không dùng cho dữ liệu hàng ngày, không biết có gây ảnh hưởng gì không, nhờ anh chỉ giào thêm.

- Việc xoá không đúng nguyên tắc, tôi thực sự chưa biết, cũng nhờ anh chỉ bảo thêm.

 

[nmh_phocu]

Đọc bài của Bác PTM quá hay, thú thực với bác là từ khi A-Tools tăng lên 400k/bản/máy nên em quyết tâm tìm hiểu cái SQL này để đỡ tốn tiền, hihihihih. Thú thực với bác là em định nhờ các chuyên gia crack cái A-Tools nhưng nay đọc được cái này thì em từ bỏ cái ý nghĩ đó đi. Mà giả sử A-Tools giảm xuống 200K thì đúng hơn nhỉ?

 

[manhhung12]

To Ptm0412: Anh chưa đọc kỹ bài Em viết nhưng việc ứng dụng SQL và vấn đề quản lý CSDL thì anh cũng rất thích.
Cũng như ý của LVD, dù là Sheet quản lý danh mục đi chăng nữa thì theo Anh kg nên sử dụng name động. Tại sao kg khai thác trực tiếp trên Sheet đó mà lại dùng name động nhỉ ?(hãy xem đây là 1 câu hỏi vì có thể có những khía cạnh trong Excel anh chưa biết đến). Nếu để lôi DL từ Excel vào Access thì dùng SQL, còn để load DL lên Form thì dùng khai thác trực tiếp trên Sheet bằng VBA cùng các control của Form.
Vài ý tham gia như vậy, có gì thông cảm nhé.
TB: Anh hơn Em 5t.

Chúc vui.
NHB

 

[Minh Nguyen]

Trích phần của bác ptm0412

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

Em đã làm như bác, nhưng còn muốn thay đổi đường dẫn đến cơ sở dữ liệu (như phần màu đỏ em highlight) bằng cách:
1. Đặt cho nó thành một cái biến, VD là Data_source
2. Chỉnh sửa code thành ... FROM '" & Data_source & "'.DSNCC ...

Nhưng khi em chạy thì nó cứ báo lỗi ở dòng này. Em đã thử đi thử lại bằng cách thêm/bớt kí tự ' (vì em không biết gọi chính xác là cái gì). Vẫn failed các bác ạ .
Em kính mong các bác giải thích giùm em được không ạ, ý em muốn thay đường dẫn database linh hoạt hơn ạ.
Cám ơn các bác !

 

[ptm0412]

cái ta cần là cái dấu ' là cái mà chả biết gọi tên là gì ấy. Vậy muốn có được nó riêng rẽ thì phải "'".
Còn cho vào cả câu thì là:

"FROM " & "'" & Data_source & "'" &".DSNCC ...

 

[Minh Nguyen]

Dạ, em cám ơn bác nhiều.
Thú thật là sau khi bác hướng dẫn, em được củng cố niềm tin rằng mình đi đúng hướng. Em kiểm tra lại, thì ra là cái biến của em nó có vấn đề, và thêm mấy dấu "" của bác thật hiệu quả.
Một lần nữa cám ơn bác ptm0412

 

[nmh_phocu]

Có một vấn đề như thế này em xin được chỉ giáo, đó là cái SQL ở trong MS query đó, phần
FROM `E:\Data\Thanh My\Access\Kho & KHang` nếu mà em copy cái file có chứa MS query này ra chỗ có thư mục khác thì nó lại không thực hiện được anh à, nếu muốn thực hiện được mình lại phải đổi cái FROM đó (sửa lại nguồn, cái này hơi mất thời gian) anh có cách nào để khi em copy ra chỗ thư mục khác thì nó tự sửa không anh (tức là sửa cái SQL ở ngay file ban đầu ý)

 

[Minh Nguyen]

Có một vấn đề như thế này em xin được chỉ giáo, đó là cái SQL ở trong MS query đó, phần
FROM `E:\Data\Thanh My\Access\Kho & KHang` nếu mà em copy cái file có chứa MS query này ra chỗ có thư mục khác thì nó lại không thực hiện được anh à, nếu muốn thực hiện được mình lại phải đổi cái FROM đó (sửa lại nguồn, cái này hơi mất thời gian) anh có cách nào để khi em copy ra chỗ thư mục khác thì nó tự sửa không anh (tức là sửa cái SQL ở ngay file ban đầu ý)

Chào bác,
Nếu bác copy "file chứa MS query " thì ở đâu trong máy của bác nó vẫn chạy bình thường.
Vấn đề là khi bác copy file dữ liệu nguồn ra chỗ khác đúng không ạ ?
Như hiểu biết của em, đường dẫn đến cơ sở dữ liệu sẽ được thay đổi bằng cách (1) bác đổi = tay và (2) là bác gán đường dẫn thành một biến, và biến này tham chiếu đến một địa chỉ nào đó trong file chứa query của mình.
VD: Data_source= sheets("Sheet2").range("A10").value
... và bác làm như em lúc trước ấy "FROM " & "'" & Data_source & "'" &".DSNCC ...
OK, khi đó, bác chỉ cần nhập đường dẫn mới của cơ sở dữ liệu vào ô A10 là hệ thống tự nhận biết thôi.
Chúc bác thành công !

 

[nmh_phocu]

Cho mình hỏi cách gán biến như thế nào với. Xin đa tạ

 

[nmh_phocu]

Cảm ơn các bạn mình đã biết cách đặt biến.
Hôm nay mình muốn hỏi thêm một chút :
Mình có cái
SELECT Ten.`Dia chi`, Ten.Luong, Ten.Ten
FROM `C:\Documents and Settings\nguyenmanhhung6\Desktop\Book1`.Ten Ten
WHERE (Ten.`Dia chi`=?)

Cái dấu hỏi mầu đỏ đó nó sẽ liên quan tới một ô ở trên bảng tính excel của mình. Nó gọi là Parameter1; bây giờ mình muốn gõ thẳng địa chỉ đó vào từ ngay lúc đang ở trong Microsoft Query liệu có được không?, không phải ra ngoài excel để dùng tay chọn ô nữa.

Tức là cái dấu hỏi được hiểu ngay là 1 ô, ví dụ là ô A1 của Sheet 1

Cảm ơn các bạn nhiều

 

[ptm0412]

Mình có cái
SELECT Ten.`Dia chi`, Ten.Luong, Ten.Ten
FROM `C:\Documents and Settings\nguyenmanhhung6\Desktop\Book1`.Ten Ten
WHERE (Ten.`Dia chi`=?)
Cái dấu hỏi mầu đỏ đó nó sẽ liên quan tới một ô ở trên bảng tính excel của mình. Nó gọi là Parameter1; bây giờ mình muốn gõ thẳng địa chỉ đó vào từ ngay lúc đang ở trong Microsoft Query liệu có được không?

Bạn đang làm việc với VBA hay trên cửa sổ Edit MS Query? Trên cửa sổ Edit MS Query thì kể cả ra bảng tính chọn cũng không được.
Còn làm việc trên cửa sổ VBA, bạ ncó thể xem lại bài 12, sẽ thấy cách để lấy 1 ô trên sheet vào câu lệnh SQL

Cụ thể như query của bạn:

"Where (Ten.'Dia chi' = '" & [F1].Value & "')"