Consider joining three aggregate saved queries:
SELECT OrderNumber
, COUNT(*) AS CountofCassetteTests
FROM TblCassetteTestSheet
GROUP BY OrderNumber
SELECT OrderNumber
, COUNT(*) AS CountofSentinelTests
FROM TblSentinelTestSheet
GROUP BY OrderNumber
SELECT OrderNumber
, COUNT(*) AS CountofHUVTests
FROM TblHUVTestSheet
GROUP BY OrderNumber
Then join to TblJobName
in your final query (parentheses are required):
SELECT j.OrderNumber, j.JobName
, c.CountofCassetteTests AS [# Of Cassettes Tested]
, s.CountofSentinelTests AS [# Of Sentinels Tested]
, h.CountofHUVTests AS [# Of HUV Tested]
, j.JobEndDate, j.SPONumber
FROM ((TblJobName j
LEFT JOIN QryCassetteTestSheet c
ON j.OrderNumber = c.OrderNumber)
LEFT JOIN QrySentinelTestSheet s
ON j.OrderNumber = s.OrderNumber)
LEFT JOIN QryHUVTestSheet h
ON j.OrderNumber = h.OrderNumber
Conceivably you can run all in one query using subqueries (and maybe one day even Common Table Expressions, CTEs, if the MS Access team ever enhances its older SQL dialect):
SELECT j.OrderNumber, j.JobName
, c.CountofCassetteTests AS [# Of Cassettes Tested]
, s.CountofSentinelTests AS [# Of Sentinels Tested]
, h.CountofHUVTests AS [# Of HUV Tested]
, j.JobEndDate, j.SPONumber
FROM ((TblJobName j
LEFT JOIN
(SELECT OrderNumber, COUNT(*) AS CountofCassetteTests
FROM TblCassetteTestSheet
GROUP BY OrderNumber) c
ON j.OrderNumber = c.OrderNumber)
LEFT JOIN
(SELECT OrderNumber, COUNT(*) AS CountofSentinelTests
FROM TblSentinelTestSheet
GROUP BY OrderNumber) s
ON j.OrderNumber = s.OrderNumber)
LEFT JOIN
(SELECT OrderNumber, COUNT(*) AS CountofHUVTests
FROM TblHUVTestSheet
GROUP BY OrderNumber) h
ON j.OrderNumber = h.OrderNumber
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…