Access环境下基于FIFO的单条数据匹配技术求助

Let’s break this down into actionable, Access-friendly steps—this is a classic bulk-to-unit conversion problem with FIFO matching, and we can pull this off using built-in Access tools.

Step 1: Create a Numbers Helper Table #

First, we need a way to split bulk quantities into individual units. Access doesn’t have a native generate_series function, so we’ll make a simple Numbers table with values from 1 to the maximum batch size you expect (adjust TOP 1000 if you have larger batches):

-- Run this once to create your Numbers table
SELECT TOP 1000 
    IDENTITY(INT, 1, 1) AS Number
INTO Numbers
FROM msysobjects AS a, msysobjects AS b, msysobjects AS c;

Step 2: Split Bulk Records into Individual Unit Rows #

Next, convert each bulk entry in your three tables into single-unit rows. Replace placeholder field names with your actual table/column names:

Split Production Table #

-- Create a table with one row per produced unit
SELECT 
    p.ProductionID,
    p.ProductionDate,
    -- Add any other production-related fields you need
    1 AS UnitQty
INTO SplitProduction
FROM Production p
INNER JOIN Numbers n ON n.Number <= p.QtyProduced
ORDER BY p.ProductionDate, p.ProductionID;

Split Delivery Table #

-- Create a table with one row per delivered unit
SELECT 
    d.DeliveryID,
    d.DeliveryDate,
    d.Customer, -- Include this since deliveries go to different objects
    -- Add other delivery fields
    1 AS UnitQty
INTO SplitDelivery
FROM Delivery d
INNER JOIN Numbers n ON n.Number <= d.QtyDelivered
ORDER BY d.DeliveryDate, d.DeliveryID;

Split Promise Table #

-- Create a table with one row per promised unit
SELECT 
    pr.PromiseID,
    pr.PromiseDate,
    pr.Customer, -- Match this to the Delivery table's Customer field
    -- Add other promise fields
    1 AS UnitQty
INTO SplitPromise
FROM Promise pr
INNER JOIN Numbers n ON n.Number <= pr.QtyPromised
ORDER BY pr.PromiseDate, pr.PromiseID;

Step 3: Add FIFO Sequence Numbers #

Assign sequential numbers to each split table to enable FIFO matching. For Access 2010+, ROW_NUMBER() works perfectly:

Sequence for Split Production #

SELECT 
    *,
    ROW_NUMBER() OVER (ORDER BY ProductionDate, ProductionID) AS ProductionSeq
INTO SplitProduction_Seq
FROM SplitProduction;

Sequence for Split Delivery (Customer-Tied) #

Since deliveries go to different customers, partition the sequence by customer to align with promises:

SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY DeliveryDate, DeliveryID) AS DeliverySeq
INTO SplitDelivery_Seq
FROM SplitDelivery;

Sequence for Split Promise (Customer-Tied) #

Match the promise sequence to the same customer-based order:

SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY PromiseDate, PromiseID) AS PromiseSeq
INTO SplitPromise_Seq
FROM SplitPromise;

Note: For pre-2010 Access versions, replace ROW_NUMBER() with a subquery to count sequential rows (e.g., (SELECT COUNT(*) FROM SplitProduction sp2 WHERE sp2.ProductionDate < sp.ProductionDate OR (sp2.ProductionDate = sp.ProductionDate AND sp2.ProductionID <= sp.ProductionID)) AS ProductionSeq)

Step 4: FIFO Matching & Metrics Calculation #

Join the sequenced tables to link each production unit to the earliest matching delivery unit, then calculate your on-time metrics:

SELECT 
    sp.ProductionDate,
    sd.DeliveryDate,
    sprom.PromiseDate,
    sd.Customer,
    -- On-time delivery: delivery date ≤ promise date
    IIF(sd.DeliveryDate <= sprom.PromiseDate, '准时', '延迟') AS DeliveryStatus,
    -- On-time production: production date ≤ delivery date (adjust to promise date if needed)
    IIF(sp.ProductionDate <= sd.DeliveryDate, '准时', '延迟') AS ProductionStatus
FROM SplitProduction_Seq sp
INNER JOIN SplitDelivery_Seq sd 
    ON sp.ProductionSeq = sd.DeliverySeq -- Global FIFO match
INNER JOIN SplitPromise_Seq sprom 
    ON sd.Customer = sprom.Customer AND sd.DeliverySeq = sprom.PromiseSeq;

Step 5: Aggregate Summary Metrics #

To get high-level stats like on-time rates, use a grouped query:

SELECT 
    sd.Customer,
    COUNT(*) AS TotalUnits,
    SUM(IIF(sd.DeliveryDate <= sprom.PromiseDate, 1, 0)) AS OnTimeDeliveryUnits,
    ROUND(SUM(IIF(sd.DeliveryDate <= sprom.PromiseDate, 1, 0))/COUNT(*)*100, 2) AS OnTimeDeliveryRate,
    SUM(IIF(sp.ProductionDate <= sd.DeliveryDate, 1, 0)) AS OnTimeProductionUnits,
    ROUND(SUM(IIF(sp.ProductionDate <= sd.DeliveryDate, 1, 0))/COUNT(*)*100, 2) AS OnTimeProductionRate
FROM SplitProduction_Seq sp
INNER JOIN SplitDelivery_Seq sd ON sp.ProductionSeq = sd.DeliverySeq
INNER JOIN SplitPromise_Seq sprom ON sd.Customer = sprom.Customer AND sd.DeliverySeq = sprom.PromiseSeq
GROUP BY sd.Customer;

Quick Tips #

• If production/delivery/promise quantities don’t match exactly, use LEFT JOIN/RIGHT JOIN to capture unmatched units (e.g., over-produced items).
• Adjust the PARTITION BY clause if you need to match units by criteria other than customer.

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