如何在MS Access中基于同表数据更新空字段？

Hey there, I see you're trying to fill those empty Customer fields using the non-null value from the same Invoicenumber group in your Access table. Let's break down how to build this update query.

The core idea here is to self-join your table to itself—we'll link rows that share the same Invoicenumber, then pull the valid non-null Customer value from the row that has it to populate the empty ones.

Here's the SQL query you can use:

UPDATE YourTableName AS main
INNER JOIN (
    SELECT Invoicenumber, Customer
    FROM YourTableName
    WHERE Customer IS NOT NULL
    GROUP BY Invoicenumber, Customer
) AS source ON main.Invoicenumber = source.Invoicenumber
SET main.Customer = source.Customer
WHERE main.Customer IS NULL;

Let me walk through what each part does:

• Replace YourTableName with the actual name of your table.
• The subquery named source grabs the unique non-null Customer value for each Invoicenumber (since your sample data shows each invoice has exactly one valid customer, this works perfectly).
• We join this subquery to the main table using Invoicenumber as the matching key.
• The SET clause updates the empty Customer fields in the main table with the valid value from the subquery.
• The WHERE main.Customer IS NULL line ensures we only touch rows that actually need updating, avoiding unnecessary changes to already correct data.

A quick note: If there was ever a case where an Invoicenumber had multiple different non-null Customer values (which doesn't apply to your data), you could adjust the subquery to pick a specific value using MIN() or MAX(), but this query is tailored exactly to your scenario.

When you run this in Access, you'll get a prompt saying it's about to update X rows—just confirm that number matches the count of empty Customer fields you have, and you're all set.

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