Micorosft Access SQL - Counting Number of foreign key records across 3 related tables

Question

I am experienced at VBA but new to SQL.

I am developing a test sheet program in MS Access for the plant that I work at. This test sheet program will be used across 3 product lines.

When an order is created, it can contain up to all 3 products. The products are unique enough that I cannot put them all into their own table. So I have broken the test sheets up into 3 tables, each table representing its respective product test sheet. Please see the image below for my relationship setup.

What I am trying to do:

I am trying to design a query that will be my master list (outputting to a continuous form). The master list will show all orders, and also show how many units have been tested in each order. See below for my desired output.

My Issue:

It is not properly counting the number of related records. See the linked photo.

I know my key field is Order Number but I am searching by Job name. Originally my key field was job name but then switched it to order number.

thank you for your time, I am happy to provide more information if needed.

Answer 1

I have created 3 queries. Each query counts the number of tests for that product that are associated with an order number. Below you will see my code for

QryCountofTestedHUV

SELECT TblJobName.OrderNumber, TblJobName.JobName
(
SELECT
Count(*)

FROM
TblHUVTestSheet

Where TblHUVTestSheet.OrderNumber=TblJobName.OrderNumber
) AS CountofHUVTests
FROM TblJobName LEFT JOIN TblHUVTestSheet ON TblJobName.OrderNumber = TblHUVTestSheet.OrderNumber
GROUP BY TblJobName.OrderNumber, TblJobName.JobName;

From there I have gone into the relationships view, and linked QryCountofTestedHUV.OrderNumber to TblJobName.OrderNumber. Now I can add CountofHUVTests from query QryCountofTestedHUV as a field to my query, QryAllJobs! QryAllJobs is my master list I showed earlier.

I have repeated this 3 times for all 3 product lines and it works!

Answer 2

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