Cách tính hàng tồn kho trong CSDL Access bên dưới bằng lệnh SQL

Table NHAP:
MaSP Soluong
01 100
02 50
03 50
Table XUAT:
MaSP Soluong
01 70
03 50
04 10
Kết quả mong muốn trong table TONKHO:
Table TONKHO
MaSp SLTon
01 30
02 50
03 0
04 -10
Trả lời :

Cách giải quyết bài toán của bạn dễ dàng và tin cậy nhất là lập trình truy xuất database thông qua các đối tượng của thư viện Microsoft ADO. Qui trình xây dựng bảng TonKho gồm các bước chính như sau:
1. Dùng đối tượng ADODB.Connection để quản lý file database Access, file này chứa các bảng số lượng cần xử lý.
2. Dùng đối tượng ADODB.Recordset để chứa các record của từng bảng dữ liệu.
3. Duyệt từng record trong bảng Nhap, đọc record có maSP tương ứng trong bảng Xuat, nếu không có thì coi như số lượng xuất = 0. Cập nhật field số lượng rồi lưu record này lên bảng TonKho.
4. Duyệt từng record trong bảng Xuat, đọc record có maSP tương ứng trong bảng Nhap, nếu có thì bỏ qua, nếu không có thì hiệu chỉnh field Soluong (thêm dấu trừ vào) rồi lưu record này lên bảng TonKho.

Cụ thể, đoạn code VB 6.0 được viết như sau:

Code:

'Khai báo các biến cần dùng
Dim Connection1 As ADODB.Connection
Dim Command1 As ADODB.Command
Dim RecordSet1 As ADODB.Recordset
Dim RecordSet2 As ADODB.Recordset
 
'Thủ tục xử lý Click chuột vào button Start
Private Sub btnStart_Click()
'Khai báo các biến cần dùng
Dim strSQL As String
Dim MyConString As String
MyConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:YourDB.mdb"
'1. Tạo connection tới database
Set Connection1 = New ADODB.Connection
Connection1.Open MyConString
'2. Tạo recordset chứa các data của table Nhap
Set RecordSet1 = New ADODB.Recordset
RecordSet1.Open "Nhap", Connection1, adOpenStatic, adLockReadOnly, adCmdTable
'Tạo command làm việc với database
Set Command1 = New ADODB.Command
Command1.ActiveConnection = Connection1
'xóa table Tonkho nếu đã có rồi
Command1.CommandText = "drop table TonKho"
On Error Resume Next
Command1.Execute
'xây dựng SQL tạo mới Table Tonkho
strSQL = "CREATE TABLE TonKho (maSP varchar(10), Soluong integer)"
'Tạo table TonKho
Command1.CommandText = strSQL
Command1.Execute
'Tạo recordset quản lý dữ liệu của table Tonkho
Set RecordSet2 = New ADODB.Recordset
RecordSet2.Open "Tonkho", Connection1, adOpenKeyset, adLockOptimistic, adCmdTable
'3. duyệt copy từng record từ Nhap sang Tonkho
While Not RecordSet1.EOF
'tạo record mới trên table Tonkho
RecordSet2.AddNew
RecordSet2.Fields("maSP").Value = RecordSet1.Fields("maSP").Value
RecordSet2.Fields("Soluong").Value = RecordSet1.Fields("Soluong").Value - slXuat(RecordSet1.Fields("maSP").Value)
RecordSet2.Update
'di chuyển đến record kế
RecordSet1.MoveNext
Wend
'đóng đối tượng RecordSet1 đã dùng lại
RecordSet1.Close
'Tạo recordset chứa các data của table Xuat
RecordSet1.Open "Xuat", Connection1, adOpenStatic, adLockReadOnly, adCmdTable
'4. duyệt copy từng record của bảng Xuat
While Not RecordSet1.EOF
'kiểm tra record có trên table Nhap khong
If Not CoNhap(RecordSet1.Fields("maSP").Value) Then
' nếu không có thì ghi lên bảng Tonkho
RecordSet2.AddNew
RecordSet2.Fields("maSP").Value = RecordSet1.Fields("maSP").Value
RecordSet2.Fields("Soluong").Value = -RecordSet1.Fields("Soluong").Value
RecordSet2.Update
End If
'di chuyển đến record kế
RecordSet1.MoveNext
Wend
'đóng các đối tượng đã dùng lại
RecordSet1.Close
RecordSet2.Close
Connection1.Close
End Sub
 
'hàm tìm số lượng xuất của 1 sản phẩm
Private Function slXuat(maSP As String) As Integer
Dim Rs As ADODB.Recordset
Command1.CommandText = "SELECT * FROM Xuat where maSP = '" & maSP & "'"
Set Rs = Command1.Execute()
'kiểm tra xem có không
If Not Rs.EOF() Then
'Nếu có thì trả về số lượng
slXuat = Rs.Fields("Soluong")
Else
'Nếu không có thì trả về số 0
slXuat = 0
End If
Rs.Close
End Function
 
'hàm kiểm tra sự tồn tại của 1 sản phẩm
Private Function CoNhap(maSP As String) As Boolean
Dim Rs As ADODB.Recordset
Command1.CommandText = "SELECT * FROM Nhap where maSP = '" & maSP & "'"
Set Rs = Command1.Execute()
'kiểm tra sản phẩm có không
If Not Rs.EOF() Then
'Nếu có thì trả về TRUE
CoNhap = True
Else
'Nếu không có thì trả về FALSE
CoNhap = False
End If
Rs.Close
End Function


Lưu ý đoạn code trên có sử dụng các đối tượng trong thư viện “Microsoft ADO” để truy xuất database dễ dàng. Để dùng được các đối tượng ADO trong 1 Project ứng dụng, bạn phải “add” chúng vào Project của mình bằng cách chọn menu Project.References để hiển thị cửa sổ References, duyệt tìm và chọn mục “Microsoft ActiveX Data Objects 2.x Library” rồi nhấn Ok.