1
ห้องสนทนาทั่วไป / : สอบถามแแนวทางกาสร้างรายงานแบบ ตารางของการซ่อมบำรุงที่บอกสถานะ ตาม สีที่กำหนด
« เมื่อ: 24 ส.ค. 62 , 21:38:36 »
ใช้ Loop + DoCmd.RUNSQL ครับ
โพสต์นี้ได้รับคำขอบคุณจาก: Peerapach
This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.
SELECT
[Month-Year], Inprogress, Complete,
[Inprogress]+[Complete] AS Total,
Format([Complete]/([Inprogress]+[Complete])*100,"0.00") AS [% Complete]
FROM
(SELECT Format([dt],"mmm-yyyy") AS [Month-Year],
First(Month([dt])) AS M,
First(Year([dt])) AS Y,
First(Val(DCount("*","TB","Status = '2' and Type = '1' and (month(dt) = " & Month([dt]) & ") and (year(dt) = " & Year([dt]) & ") "))) AS Inprogress,
First(Val(DCount("*","TB","Status = '1' and Type = '1' and (month(dt) = " & Month([dt]) & ") and (year(dt) = " & Year([dt]) & ") "))) AS Complete
FROM TB
GROUP BY Format([dt],"mmm-yyyy")) AS Q1
ORDER BY Y, M;