Bài tập về ADO căn bản.

[dhn46]

- Xin cảm ơn các anh chị đã quan tâm giúp đỡ và chỉ ra những điểm vấn đề cho dhn46. Quả thực bạn đầu dhn46 chưa hiểu hết sự nhận xét của bạn Xi tin. Sau khi nghiên cứu lại Inner Join dhn46 mới nhận ra 1 điều là mình chưa tìm hiểu kỹ nên gây sai sót.

- Inner Join: trả về tất cả các hàng từ hai bảng khi điều kiện được so trùng. Nếu các hàng trong bảng 1 không so trùng trong bảng 2, hàng đó sẽ không được liệt kê ra. => đây là cái mà dhn46 chưa tìm hiểu thấu đáo

- Nếu dùng Left Join: trả về tất cả các hàng từ bảng thứ nhất , cho dù nó không được so trùng trong bảng thứ hai. Nếu các hàng trong bảng Employees không so trùng trong bảng Orders, những hàng này cũng được liệt kê.

Trong các bài hướng dẫn của anh VetMini đã khéo léo đề cập tới vấn đề này nhưng do dhn46 mới “tập tành” nên chưa hiểu được ý hay trong các lời hường dẫn.
=> Câu lệnh dhn46 thay Inner Join bằng Left Join sẽ được kết quả

Đúng như anh VetMini đã nhận xét tại bài #220. Qua ví dụ này dhn46 đã biết thêm 1 chút về việc chia nhỏ SQL và định danh cột, bảng khi sử dụng hàm gộp để lập câu lệnh SQL.

Xin cảm ơn các anh chị rất nhiều!

 

[Hai Lúa Miền Tây]

Nếu chỉ đếm xem ở sheet2, 3, 4 có bao nhiêu lần xuất hiện của mã số và cập nhật Sheet1 (viết tắt Sheet1 lá S1, Sheet2 là S2... cho nhanh):

Update S1 set Dem=tt2.Dem From S1 as tt1 Inner Join (
Select t1.MS, count(t2.MS)+count(t3.MS)+count(t4.MS) As Dem
From S1 As t1 Left Join S2 As t2 on t1.MS=t2.MS Left Join S3 As t3 on t1.MS=t3.MS Left Join S4 As t4 on t1.MS=t4.MS
Group By t1.MS ) As tt2 on tt2.MS = tt1.MS

Hàm count tự động loại bỏ null nên dùng vời Left Join không có vấn đề

Lưu ý là câu truy vấn trên viết cho SQL tiêu chuẩn. Access không thích lệnh join nhiều hơn 2 bảng nên phải dùng dấu ngoặc

From S1 As t1 Left Join S2 As t2 on t1.MS=t2.MS Left Join S3 As t3 on t1.MS=t3.MS Left Join S4 As t4 on t1.MS=t4.MS

phải viết là

From ((S1 As t1 Left Join S2 As t2 on t1.MS=t2.MS) Left Join S3 As t3 on t1.MS=t3.MS) Left Join S4 As t4 on t1.MS=t4.MS

Đó là theo lý thuyết, trên thực tế nó có khác nhiều lắm đó bạn. Ở Access, bạn hãy thử tạo 1 SubQuery để đếm hết các mã số của 4 bảng rồi update vào bảng 1 thử coi nó báo thế nào nhé, mặc dù subquery đó nó có kết quả giống y hệt như 1 table khác nhưng với 1 table thì có thể update bình thường còn query thì nó sẽ báo lỗi. Đó là vấn đề mình muốn nói để khắc phục.

 

[VetMini]

Vậy thì gay dữ. Để tôi hỏi mấy người bạn tôi chuyên môn về ngành chuyển đổi dữ liệu xem họ có giải pháp không.
Kiến thức bản thân tôi chỉ biết cơ cấu Access chứ không có đi vào chi tiết.

 

[nhanvvGPE]

Hi All,

Mọi người giúp em với, trong máy em dù chỉnh sửa IMEX như thế nào, bỏ khoảng trắng hay thêm gì thì vẫn không lấy được dữ liệu dạng Text lẫn với số trên cùng 1 Field. Mọi người giúp em với nhé! (Em đã đọc rất kĩ các hướng dẫn trong trang 12, 13 của page này.

Thanks!

[GPECODE=vb]Sub getDataFromcsvfiles(csvPath As Variant, csvFilename As String, desColumn As Integer) Dim rsCon As Object: Dim rsData As Object
Dim arrayRows As Variant
Dim rowloops As Integer
Set rsCon = New ADODB.Connection
Set rsData = New ADODB.Recordset
With rsCon
.CursorLocation = 3
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & csvPath & ";" & _
"Extended Properties=""text;HDR=NO;FMT=Delimited;IMEX=1"";"
End With
rsData.Open "SELECT * FROM " & csvFilename, rsCon, 0, 1, 1
Cells(5, desColumn).Value = rsData.Fields(1)
arrayRows = rsData.GetRows(26)
Range(Cells(7, desColumn), Cells(14, desColumn)) = ""
For rowloops = 7 To 14
Cells(rowloops, desColumn).Value = arrayRows(1, rowloops + 11) * 10 ^ 9
Next rowloops
rsData.Close: Set rsData = Nothing
rsCon.Close: Set rsCon = Nothing
End Sub[/GPECODE]

Hi!

Em sửa được rồi nhé mọi người, nhưng ở file csv thì không được. Mong mọi người tìm giúp em lôix trong đoạn code trên.

Regards

 

[Hai Lúa Miền Tây]

Hi All,

Mọi người giúp em với, trong máy em dù chỉnh sửa IMEX như thế nào, bỏ khoảng trắng hay thêm gì thì vẫn không lấy được dữ liệu dạng Text lẫn với số trên cùng 1 Field. Mọi người giúp em với nhé! (Em đã đọc rất kĩ các hướng dẫn trong trang 12, 13 của page này.

Thanks!

[GPECODE=vb]Sub getDataFromcsvfiles(csvPath As Variant, csvFilename As String, desColumn As Integer) Dim rsCon As Object: Dim rsData As Object
Dim arrayRows As Variant
Dim rowloops As Integer
Set rsCon = New ADODB.Connection
Set rsData = New ADODB.Recordset
With rsCon
.CursorLocation = 3
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & csvPath & ";" & _
"Extended Properties=""text;HDR=NO;FMT=Delimited;IMEX=1"";"
End With
rsData.Open "SELECT * FROM " & csvFilename, rsCon, 0, 1, 1
Cells(5, desColumn).Value = rsData.Fields(1)
arrayRows = rsData.GetRows(26)
Range(Cells(7, desColumn), Cells(14, desColumn)) = ""
For rowloops = 7 To 14
Cells(rowloops, desColumn).Value = arrayRows(1, rowloops + 11) * 10 ^ 9
Next rowloops
rsData.Close: Set rsData = Nothing
rsCon.Close: Set rsCon = Nothing
End Sub[/GPECODE]

Hi!

Em sửa được rồi nhé mọi người, nhưng ở file csv thì không được. Mong mọi người tìm giúp em lôix trong đoạn code trên.

Regards

Chịu thôi, bởi vì kiểu dữ liệu cột A của file 001.csv là dạng text nên nó chỉ hiểu là text

 

[nhanvvGPE]

Chịu thôi, bởi vì kiểu dữ liệu cột A của file 001.csv là dạng text nên nó chỉ hiểu là text

Dạ khi cột B hoàn toàn là số hoặc text thì Ok, nhưng khi một số ô là text thì nó chỉ lấy được số còn text thì không hiểu.
File xlsx vẫn là cột A là text tuy nhiên vẫn lấy đủ dữ liệu.
Em thử chuyển dữ liệu cột A trong file csv hoàn toàn về số nhưng kết quả vẫn vậy, không hiểu vì sau IMEX không có tác dụng với file csv.

Rgds

 

[nhanvvGPE]

Chịu thôi, bởi vì kiểu dữ liệu cột A của file 001.csv là dạng text nên nó chỉ hiểu là text

Hi Anh Hai Lúa!

Vấn đề trên em mò hoài cũng không được, em có bài tập thế này để học và cũng có thể áp dụng vào công việc. Anh xem giúp em với nhé:
1/ Lấy dữ liệu từ csv file đang đóng để đưa vào excel.
2/ Từ dữ liệu này và dữ liệu mẫu ta vẽ đồ thị đường dích dắc để so sánh (theo cách chia toạ độ excel).
3/ Lưu dữ liệu vào file xlsx hoặc xls để sau này cần truy xuất (không cần vẽ đồ thị).

Trong 3 bước trên thì 2 bước đầu em đã hoàn thành, chỉ còn bước thứ ba đọc tài liệu hoài mà không biết xử lý thế nào, vì dữ liệu của em không có Header cũng như ID cho cột đầu tiên. Attached file mẫu lên mọi người giúp em với.
Đề bài này là:

Khi thay đổi một ô C6 chẳng hạn trong vùng từ C6:AY6 tại file Source thì dữ liệu từ C3:C22 truyền sang file xlsx đang đóng ở cùng vị trí này. Nếu B6 thay đổi thì B3:B22 được truyền đi.

Em mới tìm hiểu thêm về ADO mong mọi người giúp đỡ!

 

[Hai Lúa Miền Tây]

Dạ khi cột B hoàn toàn là số hoặc text thì Ok, nhưng khi một số ô là text thì nó chỉ lấy được số còn text thì không hiểu.
File xlsx vẫn là cột A là text tuy nhiên vẫn lấy đủ dữ liệu.
Em thử chuyển dữ liệu cột A trong file csv hoàn toàn về số nhưng kết quả vẫn vậy, không hiểu vì sau IMEX không có tác dụng với file csv.

Rgds

Trong CSDL qui định kiểu dữ liệu của các trường 1 cách rõ ràng, hoặc là số hoặc text... Kiểu dữ liệu của bạn vừa là số vừa là text trong cùng 1 trường thì nó sẽ tự hiểu trường đó là text là phải rồi.

 

[Hai Lúa Miền Tây]

Hi Anh Hai Lúa!

Vấn đề trên em mò hoài cũng không được, em có bài tập thế này để học và cũng có thể áp dụng vào công việc. Anh xem giúp em với nhé:
1/ Lấy dữ liệu từ csv file đang đóng để đưa vào excel.
2/ Từ dữ liệu này và dữ liệu mẫu ta vẽ đồ thị đường dích dắc để so sánh (theo cách chia toạ độ excel).
3/ Lưu dữ liệu vào file xlsx hoặc xls để sau này cần truy xuất (không cần vẽ đồ thị).

Trong 3 bước trên thì 2 bước đầu em đã hoàn thành, chỉ còn bước thứ ba đọc tài liệu hoài mà không biết xử lý thế nào, vì dữ liệu của em không có Header cũng như ID cho cột đầu tiên. Attached file mẫu lên mọi người giúp em với.
Đề bài này là:

Khi thay đổi một ô C6 chẳng hạn trong vùng từ C6:AY6 tại file Source thì dữ liệu từ C3:C22 truyền sang file xlsx đang đóng ở cùng vị trí này. Nếu B6 thay đổi thì B3:B22 được truyền đi.

Em mới tìm hiểu thêm về ADO mong mọi người giúp đỡ!

Nhìn bảng dữ liệu của bạn mình rối quá, trộn ô nhiều quá, để làm = ado thì csdl phải chuẩn mới có thể làm đúng yêu cầu bạn được.

 

[nhanvvGPE]

Nhìn bảng dữ liệu của bạn mình rối quá, trộn ô nhiều quá, để làm = ado thì csdl phải chuẩn mới có thể làm đúng yêu cầu bạn được.

Hi Anh,

Vậy anh chỉ dùm em cách chuyển dữ liệu vùng từ C3:C22 vào range C3:C22 file xlsx đang đóng ạ, phần còn lại em sẽ tự tìm hiểu thêm.

Em cảm ơn!

 

[dhn46]

Các anh chị cho Dhn46 hỏi:
- Dùng câu lệnh SQL đơn giản nhất để lọc ra danh sách những dòng có Qty lớn thứ 2 (hoặc thứ n) tương ứng với mỗi ID.
- Dhn46 đính kèm file mẫu để anh chị hiểu hơn câu hỏi.
Xin cảm ơn GPE

 

[VetMini]

Nguyên tắc giải đề này là sô lớn thứ 2 cách số lớn nhất 1 khoảng nhỏ nhất
- dùng group by để nhóm ra theo ID và dùng hàm max để lấy qty lớn nhất, gọi là qmax
- dùng inner join để kết nối câu truy vấn con trên với bảng chính theo ID
- tạo trường qmax - qty as khoangCach
- group by lần nữa để nhóm ra theo ID, qmax và dùng hàm min để lấy khoangCach nhỏ nhất, goi là kcMin
- inner join với bảng chính theo ID lần nữa và lọc thoe điều kiện qmax-qty = kcMin

Chạy thử, nếu thấy ra đúng thì tìm cách rút gọn.

??? Đây không phải là một vấn đề đơn giản để gọi là "bài tập căn bản". Mục đích truy vấn này quá sức rắc rối.
Theo nguyên tắc CSDL, việc truy vấn phải tương đối dễ dàng. Nếu có sự rắc rối thì bảng và CSDL nên thiết kế lại cho phù hợp.

 

[Hai Lúa Miền Tây]

Các anh chị cho Dhn46 hỏi:
- Dùng câu lệnh SQL đơn giản nhất để lọc ra danh sách những dòng có Qty lớn thứ 2 (hoặc thứ n) tương ứng với mỗi ID.
- Dhn46 đính kèm file mẫu để anh chị hiểu hơn câu hỏi.
Xin cảm ơn GPE

Không biết đây là câu đố hay là vướng mắc của bạn? Nếu đố thì mình sẽ di chuyển bài này sang topic khác.

Nguyên tắc giải đề này là sô lớn thứ 2 cách số lớn nhất 1 khoảng nhỏ nhất
- dùng group by để nhóm ra theo ID và dùng hàm max để lấy qty lớn nhất, gọi là qmax
- dùng inner join để kết nối câu truy vấn con trên với bảng chính theo ID
- tạo trường qmax - qty as khoangCach
- group by lần nữa để nhóm ra theo ID, qmax và dùng hàm min để lấy khoangCach nhỏ nhất, goi là kcMin
- inner join với bảng chính theo ID lần nữa và lọc thoe điều kiện qmax-qty = kcMin

Chạy thử, nếu thấy ra đúng thì tìm cách rút gọn.

??? Đây không phải là một vấn đề đơn giản để gọi là "bài tập căn bản". Mục đích truy vấn này quá sức rắc rối.
Theo nguyên tắc CSDL, việc truy vấn phải tương đối dễ dàng. Nếu có sự rắc rối thì bảng và CSDL nên thiết kế lại cho phù hợp.

Không nhất thiết phải group quá nhiều lần như vậy đâu bạn.

 

[VetMini]

...
Không nhất thiết phải group quá nhiều lần như vậy đâu bạn.

Đơn giản cho dễ thôi. Tôi có nói là sau khi chạy ra kết quả thì tìm cách rút gọn lại.
Nếu dùng thủ thuật nội kết (correlated query) thì có thể không phải group lần nào cả. Tuy nhiên nội kết nhiều lần xem chẳng ai hiểu gì cả, lúc cần chỉnh sửa có mà tỏi.

 

[Hai Lúa Miền Tây]

Đơn giản cho dễ thôi. Tôi có nói là sau khi chạy ra kết quả thì tìm cách rút gọn lại.
Nếu dùng thủ thuật nội kết (correlated query) thì có thể không phải group lần nào cả. Tuy nhiên nội kết nhiều lần xem chẳng ai hiểu gì cả, lúc cần chỉnh sửa có mà tỏi.

Chỉ cần select 3 cột, join, group và 1 điều kiện là đủ. Không cần dùng sub query chi cho rắc rối.

 

[dhn46]

Không biết đây là câu đố hay là vướng mắc của bạn? Nếu đố thì mình sẽ di chuyển bài này sang topic khác..

Anh HLMT, đây là vướng mắc của em anh ah. Em cũng mới đang tìm hiểu ADO nên có rất nhiều vướng mắc mong các anh chị quan tâm và cho câu trả lời. Cảm ơn anh!

 

[Hai Lúa Miền Tây]

Anh HLMT, đây là vướng mắc của em anh ah. Em cũng mới đang tìm hiểu ADO nên có rất nhiều vướng mắc mong các anh chị quan tâm và cho câu trả lời. Cảm ơn anh!

Vậy thì bạn thử làm như sau, không biết có trúng hay không, phần điều kiện tôi làm theo ý bạn, bạn muốn chỉnh sửa thì thay đổi lại theo ý muốn nhé.

[GPECODE=sql]Sub Loc_HLMT()
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
With adoRS
.ActiveConnection = adoConn
.Open "SELECT a.id,a.qty,a.remarks " & _
"FROM [Sheet1$a1:C1000] AS a " & _
"INNER JOIN [Sheet1$a1:C1000] AS b ON (a.id = b.id) AND (a.qty < b.qty) " & _
"GROUP BY a.id,a.qty,a.remarks " & _
"HAVING COUNT(*) = 1"
End With
Sheet1.[H2:J1000].Clear
If adoRS.EOF Then
MsgBox "Khong co du lieu roi", vbCritical
Else
Sheet1.[H2].CopyFromRecordset adoRS
End If
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing

End Sub

[/GPECODE]

P/S: Đang tìm hiểu mà có cái "vướng mắc" không hề đơn giản

 

[VetMini]

Nếu mỗi tổ hợp ID và Qty là duy nhất thì đúng (mỗi ID có thể nhiều Qty, nhưng các Qty không được giống nhau)
Nếu không có điều kiện trên thìn không làm vậy được.

 

[Hai Lúa Miền Tây]

Nếu mỗi tổ hợp ID và Qty là duy nhất thì đúng (mỗi ID có thể nhiều Qty, nhưng các Qty không được giống nhau)
Nếu không có điều kiện trên thìn không làm vậy được.

Để xử lý cái này dể mà bạn, chỉ cần thêm 1 subquery là ra, chứ không nhiều sub như bạn nói.

 

[dhn46]

Vậy thì bạn thử làm như sau, không biết có trúng hay không, phần điều kiện tôi làm theo ý bạn, bạn muốn chỉnh sửa thì thay đổi lại theo ý muốn nhé.
P/S: Đang tìm hiểu mà có cái "vướng mắc" không hề đơn giản

- Cảm ơn anh HLMT, kết quả đúng ý em rồi ạ. Mặc dù em chưa nghĩ đến tình huống bài #234 nhưng sẽ tìm hiểu sau khi hiểu thấu đáo câu truy vấn của anh.
- Anh có thể giải thích cho em kỹ hơn 1 chút về đoạn Count(*) được không ạ? Em đọc 1 số tài liệu nhưng chưa hiểu thấu đáo cái này.

* Em xin giải thích 1 chút về phần chữ màu xanh và bài #233:
Trong quá trình tìm hiểu ADO em có đọc topic này : http://www.giaiphapexcel.com/forum/showthread.php?82846-Gi%C3%BAp-m%C3%ACnh-code-loc-d%E1%BB%AF-li%E1%BB%87u-theo-%C4%91i%E1%BB%81u-ki%E1%BB%87n và em thấy có thể giải quyết được bằng SQL. Trích lọc Max, Min thì em làm được còn lấy giá trị Max2 thì đang gặp khó nên hỏi (và e hỏi mở rộng 1 chút là lấy Max thứ n nữa). Mới tìm hiểu nên đâu dám thách đố ai đâu anh.

dhn46 cảm ơn anh và các anh chị đã quan tâm giúp đỡ.