需求：SQL/MS-Access查询合规主机及待修复软件逻辑

Hey Sebastian, let’s work through your two tasks with SQL and MS Access logic—this is totally doable once we break it down.

First, let’s clarify our table assumptions to make the code concrete:

• Let’s call your machine-software list table MachineSoftware with fields Hostname and SW
• Your software status table is SoftwareStatus with fields SW, Name, and Status
• From your example, I’ll assume ready is the "ok" status we’re targeting—adjust the value in the code if your actual "ok" status uses different wording.

Task 1: Filter Hosts That Only Have "Ok" Status Software #

We need to find hosts where every single installed software is marked as ok. Here are two reliable approaches:

Approach 1: Use NOT EXISTS to Exclude Bad Hosts #

This method first identifies any host that has at least one non-ok software, then excludes those hosts entirely:

-- SQL (works in most databases)
SELECT DISTINCT ms.Hostname
FROM MachineSoftware ms
WHERE NOT EXISTS (
    SELECT 1
    FROM SoftwareStatus ss
    WHERE ss.SW = ms.SW
      AND ss.Status <> 'ready' -- Replace with your actual "non-ok" status value
);

For MS Access, the syntax is almost identical (Access supports NOT EXISTS):

SELECT DISTINCT ms.Hostname
FROM MachineSoftware ms
WHERE NOT EXISTS (
    SELECT *
    FROM SoftwareStatus ss
    WHERE ss.SW = ms.SW
      AND ss.Status <> 'ready'
);

Approach 2: Group Hosts and Validate All Software #

We group hosts by their name, then check that none of their installed software is non-ok:

-- SQL
SELECT ms.Hostname
FROM MachineSoftware ms
JOIN SoftwareStatus ss ON ms.SW = ss.SW
GROUP BY ms.Hostname
HAVING COUNT(CASE WHEN ss.Status <> 'ready' THEN 1 END) = 0;

For MS Access, use IIF instead of CASE since Access doesn’t support CASE in aggregate contexts:

SELECT ms.Hostname
FROM MachineSoftware ms
INNER JOIN SoftwareStatus ss ON ms.SW = ss.SW
GROUP BY ms.Hostname
HAVING SUM(IIF(ss.Status <> 'ready', 1, 0)) = 0;

Task 2: Identify Which Software to Mark "Ok" to Add More Hosts #

We want to find software where changing its status to ok will turn existing non-compliant hosts into compliant ones. The most impactful candidates are software that, when fixed, resolves all non-ok software for a host (i.e., the host only has that one non-ok software).

SQL Version (Using CTEs) #

WITH HostNonOkCounts AS (
    -- First, count how many non-ok software each host has
    SELECT 
        ms.Hostname,
        COUNT(ss.SW) AS TotalNonOkSoftware
    FROM MachineSoftware ms
    JOIN SoftwareStatus ss ON ms.SW = ss.SW
    WHERE ss.Status <> 'ready'
    GROUP BY ms.Hostname
)
SELECT 
    ss.SW,
    ss.Name,
    COUNT(DISTINCT ms.Hostname) AS HostsThatWillBecomeCompliant,
    STRING_AGG(ms.Hostname, ', ') AS AffectedHosts -- Lists which hosts benefit
FROM MachineSoftware ms
JOIN SoftwareStatus ss ON ms.SW = ss.SW
JOIN HostNonOkCounts hnoc ON ms.Hostname = hnoc.Hostname
WHERE ss.Status <> 'ready' 
  AND hnoc.TotalNonOkSoftware = 1 -- Host only has this one non-ok software
GROUP BY ss.SW, ss.Name
ORDER BY HostsThatWillBecomeCompliant DESC; -- Prioritize software with most impact

MS Access Version #

Access doesn’t support CTEs or STRING_AGG, so we’ll use nested queries and a custom function (like ConcatRelated, which you can add via VBA) to list affected hosts:

First, create a saved query named HostNonOkCounts:

SELECT Hostname, COUNT(SW) AS TotalNonOkSoftware
FROM MachineSoftware ms
INNER JOIN SoftwareStatus ss ON ms.SW = ss.SW
WHERE ss.Status <> 'ready'
GROUP BY Hostname;

Then run this main query:

SELECT 
    ss.SW,
    ss.Name,
    COUNT(DISTINCT ms.Hostname) AS HostsThatWillBecomeCompliant,
    ConcatRelated("Hostname", "MachineSoftware", "SW = '" & ss.SW & "' AND Hostname IN (SELECT Hostname FROM HostNonOkCounts WHERE TotalNonOkSoftware = 1)") AS AffectedHosts
FROM MachineSoftware ms
INNER JOIN SoftwareStatus ss ON ms.SW = ss.SW
INNER JOIN HostNonOkCounts hnoc ON ms.Hostname = hnoc.Hostname
WHERE ss.Status <> 'ready' 
  AND hnoc.TotalNonOkSoftware = 1
GROUP BY ss.SW, ss.Name
ORDER BY HostsThatWillBecomeCompliant DESC;

What This Does #

• It highlights software where fixing its status will immediately make entire hosts compliant (since those hosts only have that one non-ok software).
• If you need to handle hosts with multiple non-ok software, you can adjust the query to look for combinations, but focusing on single-fix hosts is usually the highest-impact first step.

内容的提问来源于stack exchange，提问作者Guenny