如何为每条主记录生成两条追加记录？工时卡SQL/Access查询问题

To achieve your goal of creating two cost log entries per record in TBL_WheelHours (one for each operator) and keeping those entries updated from the main table, here's how to approach it:

1. Append Query: Add Two Entries per New Record #

This query will take each new record from TBL_WheelHours and insert two separate rows into TBL_CostLog—one for Operator 1 and one for Operator 2.

INSERT INTO TBL_CostLog (TableID, GUID, Operator, Data1, Data2, Data3, Data4)
SELECT 
    -- Replace with your actual TableID value (could be a constant or dynamic value)
    1 AS TableID, 
    GUID,
    [Operator 1],
    Data1,
    Data2,
    Data3,
    Data4
FROM TBL_WheelHours
-- Optional: Filter to only append records not already in CostLog
WHERE GUID NOT IN (SELECT GUID FROM TBL_CostLog)

UNION ALL

SELECT 
    1 AS TableID,
    GUID,
    [Operator 2],
    Data1,
    Data2,
    Data3,
    Data4
FROM TBL_WheelHours
WHERE GUID NOT IN (SELECT GUID FROM TBL_CostLog);

Notes: #

• Replace 1 AS TableID with your actual TableID value. If TableID is an auto-generated field, you might omit it (depending on your table setup) or pull it from another source.
• The UNION ALL ensures both operator entries are added without removing duplicates (since we intentionally want two entries per GUID).
• The WHERE clause prevents re-adding existing records—remove it if you want to append all records every time.

2. Update Query: Sync CostLog with TBL_WheelHours #

This query will update all existing TBL_CostLog entries linked to TBL_WheelHours (via GUID) with the latest data from the main table. It targets both operator entries for each GUID:

UPDATE TBL_CostLog
INNER JOIN TBL_WheelHours 
    ON TBL_CostLog.GUID = TBL_WheelHours.GUID
SET 
    TBL_CostLog.Data1 = TBL_WheelHours.Data1,
    TBL_CostLog.Data2 = TBL_WheelHours.Data2,
    TBL_CostLog.Data3 = TBL_WheelHours.Data3,
    TBL_CostLog.Data4 = TBL_WheelHours.Data4
WHERE 
    TBL_CostLog.Operator = TBL_WheelHours.[Operator 1]
    OR TBL_CostLog.Operator = TBL_WheelHours.[Operator 2];

Notes: #

• This joins the two tables on GUID, then updates the Data fields in TBL_CostLog to match the latest values in TBL_WheelHours.
• The WHERE clause ensures we only update entries that correspond to either Operator 1 or Operator 2 from the main table.

How to Use: #

1. Run the append query first to add new records as dual entries in the cost log.
2. Run the update query periodically (or as needed) to sync any changes from TBL_WheelHours to the existing cost log entries.

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