Microsoft Access更新查询：关联其他表获取主键并插入订单表的求助

Hey there! Let's work through this Access challenge together—since you've already nailed updating the CustomerTable, you're already ahead of the game. Here's a step-by-step approach to get your OrderTable populated correctly by linking OP-tMain to CustomerTable for the custID:

Core Idea #

OrderTable relies on custID (instead of raw customer details) which lives in CustomerTable. We need to map the customer info from OP-tMain to the corresponding custID in CustomerTable, then use that to either insert new orders or update existing ones in OrderTable.

1. Insert New Orders into OrderTable #

If you need to add orders that don't already exist in OrderTable (using the Ref field as the unique identifier), use this INSERT INTO query:

INSERT INTO OrderTable (custID, Ref)
SELECT ct.custID, op.Ref
FROM [OP-tMain] AS op
INNER JOIN CustomerTable AS ct 
    ON Trim(UCase(op.custName)) = Trim(UCase(ct.custName))
    AND Trim(UCase(op.custPhone)) = Trim(UCase(ct.custPhone))
    AND Trim(UCase(op.custEmail)) = Trim(UCase(ct.custEmail))
WHERE op.Ref NOT IN (SELECT Ref FROM OrderTable);

Quick Notes:

• We use Trim() and UCase() to avoid mismatches from extra spaces or case differences (e.g., "John Doe" vs " john doe").
• The INNER JOIN ensures we only pull orders where the customer exists in CustomerTable (which you've already updated, so this should cover all valid customers).
• The WHERE clause prevents duplicate orders by skipping any Ref values already in OrderTable.

2. Update Existing Orders in OrderTable #

If you need to refresh the custID for orders that already exist in OrderTable (e.g., if a customer's details were updated and you need to link to the correct custID), use this UPDATE query:

UPDATE OrderTable AS ot
INNER JOIN (
    SELECT op.Ref, ct.custID
    FROM [OP-tMain] AS op
    INNER JOIN CustomerTable AS ct 
        ON Trim(UCase(op.custName)) = Trim(UCase(ct.custName))
        AND Trim(UCase(op.custPhone)) = Trim(UCase(ct.custPhone))
        AND Trim(UCase(op.custEmail)) = Trim(UCase(ct.custEmail))
) AS op_ct ON ot.Ref = op_ct.Ref
SET ot.custID = op_ct.custID;

Quick Notes:

• The subquery first maps each Ref from OP-tMain to the correct custID from CustomerTable.
• We then join this mapped data to OrderTable using Ref and update the custID field.

Key Tips to Avoid Headaches #

• Verify Customer Coverage: Double-check that CustomerTable has all the customers from OP-tMain. If there are new customers in OP-tMain that aren't in CustomerTable, run an INSERT INTO CustomerTable query first to add them before processing OrderTable.
• Test First, Modify Later: Always run a SELECT version of the query (without INSERT or UPDATE) to preview which records will be affected. This catches mistakes before you alter your data.
• Handle Nulls Gracefully: If some customer fields (like custEmail) might be empty, adjust the join condition to account for nulls (e.g., (op.custEmail IS NULL AND ct.custEmail IS NULL) OR op.custEmail = ct.custEmail).

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