Xuân Thanh > 10-04-20, 05:56 PM
SELECT [MaKho] & "-" & [MaVT] AS Rec, tblPhieuNhap.NgayCT, "PN" AS LoaiCT, tblPhieuNhapChiTiet.MaKho, tblPhieuNhapChiTiet.MaVT, tblPhieuNhapChiTiet.SoLuong AS SLNhap, tblPhieuNhapChiTiet.ThanhTien AS TTNhap, 0 AS SLXuat, 0 AS TTXuat
FROM tblPhieuNhap INNER JOIN tblPhieuNhapChiTiet ON tblPhieuNhap.RecKey = tblPhieuNhapChiTiet.RecKey;
UNION ALL SELECT [MaKho] & "-" & [MaVT] AS Rec, tblPhieuXuat.NgayCT, "PX" AS LoaiCT, tblPhieuXuatChiTiet.MaKho, tblPhieuXuatChiTiet.MaVT, 0 AS SLNhap, 0 AS TTNhap, tblPhieuXuatChiTiet.SoLuong AS SLXuat, tblPhieuXuatChiTiet.ThanhTien AS TTXuat
FROM tblPhieuXuat INNER JOIN tblPhieuXuatChiTiet ON tblPhieuXuat.RecKey = tblPhieuXuatChiTiet.RecKey;SELECT [MaKho] & "-" & [MaVT] AS Rec, tblKho.MaKho, tblVatTu.MaVT
FROM tblKho, tblVatTu;SELECT qryNX.Rec, qryNX.MaKho, qryNX.MaVT, qryNX.SLNhap, qryNX.TTNhap, qryNX.SLXuat, qryNX.TTXuat
FROM qryNX
WHERE (((qryNX.NgayCT)<[TuNgay]));SELECT qryNX.Rec, qryNX.MaKho, qryNX.MaVT, qryNX.SLNhap, qryNX.TTNhap, qryNX.SLXuat, qryNX.TTXuat
FROM qryNX
WHERE (((qryNX.NgayCT) Between [TuNgay] And [DenNgay]));SELECT qryTrong.Rec, qryTrong.MaKho, qryTrong.MaVT, Sum(qryTrong.SLNhap) AS NhapTrongKy, Sum(qryTrong.TTNhap) AS TienNhapTrongKy, Sum(qryTrong.SLXuat) AS XuatTrongKy, Sum(qryTrong.TTXuat) AS TienXuatTrongKy
FROM qryTrong
GROUP BY qryTrong.Rec, qryTrong.MaKho, qryTrong.MaVT;SELECT qryTruoc.Rec, qryTruoc.MaKho, qryTruoc.MaVT, Sum(qryTruoc.SLNhap) AS NhapTruoc, Sum(qryTruoc.TTNhap) AS TienNhapTruoc, Sum(qryTruoc.SLXuat) AS XuatTruoc, Sum(qryTruoc.TTXuat) AS TienXuattruoc, [NhapTruoc]-[XuatTruoc] AS TonDau, [TienNhapTruoc]-[TienXuatTruoc] AS TienDau
FROM qryTruoc
GROUP BY qryTruoc.Rec, qryTruoc.MaKho, qryTruoc.MaVT;SELECT qryKhoVT.Rec, qryKhoVT.MaKho, qryKhoVT.MaVT, Sum(Nz([TonDau])) AS TonDK, Sum(Nz([TienDau])) AS TienDK, Sum(Nz([NhapTrongKy])) AS SLNTrongKy, Sum(Nz([TienNhapTrongKy])) AS TTNTrongKy, Sum(Nz([XuatTrongKy])) AS SLXTrongKy, Sum(Nz([TienXuatTrongKy])) AS TTXTrongKy, [TonDK]+[SLNTrongKy]-[SLXTrongKy] AS TonCuoi, [TienDK]+[TTNTrongKy]-[TTXTrongKy] AS TienCuoi
FROM (qryKhoVT LEFT JOIN qryTonDau ON qryKhoVT.Rec = qryTonDau.Rec) LEFT JOIN qryTrongKy ON qryKhoVT.Rec = qryTrongKy.Rec
GROUP BY qryKhoVT.Rec, qryKhoVT.MaKho, qryKhoVT.MaVT;SELECT qryTon.Rec, qryTon.MaKho, qryTon.MaVT, qryTon.TonDK, qryTon.SLNTrongKy, qryTon.SLXTrongKy, qryTon.TonCuoi
FROM qryTon
WHERE ((([TonDK]+[SLNTrongKy]+[SLXTrongKy])>0));SELECT qryTon.MaVT, Sum(qryTon.TonDK) AS TonDauKy, Sum(qryTon.TienDK) AS TienDauKy, Sum(qryTon.SLNTrongKy) AS TongNhap, Sum(qryTon.TTNTrongKy) AS TienNhap, Sum(qryTon.SLXTrongKy) AS TongXuat, Sum(qryTon.TTXTrongKy) AS TienXuat, Sum(qryTon.TonCuoi) AS TonCuoiKy, Sum(qryTon.TienCuoi) AS TienCuoiKy, ([TienDauKy]+[TienNhap])/([TonDauKy]+[TongNhap]) AS DGBQ1, [TienCuoiKy]/[TonCuoiKy] AS DGBQGQ2
FROM qryTon
GROUP BY qryTon.MaVT;SELECT qryTonDau.MaKho, qryTonDau.MaVT, qryTonDau.TonDau, qryTonDau.TienDau
FROM qryTonDau
WHERE (((qryTonDau.MaKho)=[MaKhoX]) AND ((qryTonDau.MaVT)=[MaVTX]));SELECT qryNX.NgayCT, qryNX.LoaiCT, qryNX.MaKho, qryNX.MaVT, qryNX.SLNhap, qryNX.TTNhap, qryNX.SLXuat, qryNX.TTXuat
FROM qryNX
WHERE (((qryNX.NgayCT) Between [TuNgay] And [DenNgay]) AND ((qryNX.MaKho)=[MaKhoX]) AND ((qryNX.MaVT)=[MaVTX]));SELECT tblVatTu.MaVT, qryNXVTTrongKy.NgayCT, qryNXVTTrongKy.LoaiCT, qryTonDauVTTheoKho.TonDau, qryTonDauVTTheoKho.TienDau, qryNXVTTrongKy.SLNhap, qryNXVTTrongKy.TTNhap, qryNXVTTrongKy.SLXuat, qryNXVTTrongKy.TTXuat
FROM (tblVatTu INNER JOIN qryTonDauVTTheoKho ON tblVatTu.MaVT = qryTonDauVTTheoKho.MaVT) INNER JOIN qryNXVTTrongKy ON tblVatTu.MaVT = qryNXVTTrongKy.MaVT;KheNgot > 11-04-20, 12:48 AM
Xuân Thanh > 11-04-20, 08:30 AM
(11-04-20, 12:48 AM)KheNgot Đã viết: @Xuân Thanh: Em làm theo anh hướng dẫn nhưng có query qryKhoVT lỗi. Em tìm mãi không biết là lỗi chỗ nào ạ? Anh và mọi người chỉ giúp cho em thêm để em tham khảo nhé!
Link: https://drive.google.com/open?id=1KccBqA...YO8mXmN6jG
Cám ơn nhiều!
KheNgot > 11-04-20, 08:43 AM
Xuân Thanh > 11-04-20, 08:47 AM
Xuân Thanh > 11-04-20, 10:33 AM
SELECT qryTonKho.MaVT, tblVatTu.TenVT, tblVatTu.DVT, qryTonKho.TonDK, qryTonKho.SLNTrongKy, qryTonKho.SLXTrongKy, qryTonKho.TonCuoi
FROM qryTonKho INNER JOIN tblVatTu ON qryTonKho.MaVT = tblVatTu.MaVT
WHERE (((qryTonKho.MaKho)=[MaKhoX]));SELECT qryTon.MaVT, tblVatTu.TenVT, tblVatTu.DVT, qryTon.TonDK, qryTon.TienDK, qryTon.SLNTrongKy, qryTon.TTNTrongKy, qryTon.SLXTrongKy, qryTon.TTXTrongKy, qryTon.TonCuoi, qryTon.TienCuoi
FROM tblVatTu INNER JOIN qryTon ON tblVatTu.MaVT = qryTon.MaVT
WHERE (((qryTon.MaKho)=[MaKhoX]) AND (([TonDK]+[SLNTrongKy]+[SLXTrongKy])>0));KheNgot > 11-04-20, 03:29 PM
Xuân Thanh > 11-04-20, 05:20 PM
(11-04-20, 03:29 PM)KheNgot Đã viết: Anh @Xuân Thanh ơi, em đã làm như anh hướng dẫn rồi nhưng phần thẻ không vẫn chưa chạy được ạ. Anh và mọi người xem thêm cho em với nhé!
Link: https://drive.google.com/open?id=1oKZLgS...H1rcwdIysQ
Cám ơn nhiều!
dotrung > 11-04-20, 06:33 PM