如何在Access中为每个库存车辆创建关联成本子表？

Hey Zack, let's walk through your problem of tracking rolling costs for each vehicle stock# and updating the totalCosts field. First, I want to break down why creating a separate cost sub-table for every single stock# might not be the best approach, then share the standard (and far more maintainable) solution, plus cover how you'd implement the per-stock# tables if you still want to go that route.

• Database Bloat: If you have hundreds or thousands of vehicles, you'll end up with hundreds/thousands of nearly identical tables. This makes backups, maintenance, and database management a nightmare.
• Redundant Structure: All these sub-tables would have the same columns (description, cost), which violates database normalization rules (specifically, avoiding redundant schema structures).
• Query Complexity: To get a full view of costs across all vehicles, you'd have to dynamically build queries that reference every single sub-table—this is messy and error-prone.
• Scalability Issues: Adding a new vehicle would require writing code to create a new table every time, which adds unnecessary complexity to your application.

This is the industry-standard approach for relational databases, and it solves your problem cleanly while keeping things scalable. Here's how to implement it:

Step 1: Modify the COSTS Table #

Add a stock# column to link each cost entry to its corresponding vehicle:

ALTER TABLE COSTS 
ADD COLUMN stock# VARCHAR(50); -- Adjust the data type to match your ASSETS.stock# type

Step 2: Query Costs for a Specific Stock# #

To pull up the full cost list for any vehicle, use a JOIN between ASSETS and COSTS:

SELECT 
  a.stock#,
  a.make,
  a.model,
  a.purchase_price AS "Base Cost",
  c.description AS "Cost Type",
  c.cost AS "Cost Amount"
FROM ASSETS a
LEFT JOIN COSTS c ON a.stock# = c.stock#
WHERE a.stock# = 'YOUR_STOCK_NUMBER_HERE' -- Replace with the stock# you want to view
ORDER BY c.cost DESC;

Step 3: Automatically Update totalCosts #

You can keep the totalCosts field in ASSETS up-to-date automatically using a database trigger. This trigger will recalculate the total cost (purchase price + all additional costs) whenever a new cost is added, updated, or deleted:

-- Trigger to update totalCosts when COSTS is modified
CREATE TRIGGER update_asset_total_costs
AFTER INSERT OR UPDATE OR DELETE ON COSTS
FOR EACH ROW
BEGIN
  -- Recalculate total cost: purchase price + sum of all associated costs
  UPDATE ASSETS
  SET totalCosts = (
    SELECT purchase_price + COALESCE(SUM(cost), 0)
    FROM COSTS
    WHERE stock# = COALESCE(NEW.stock#, OLD.stock#)
    GROUP BY stock#
  )
  WHERE stock# = COALESCE(NEW.stock#, OLD.stock#);
END;

Note: COALESCE handles cases where we're deleting a cost entry (since NEW.stock# will be null in that scenario).

If you have a specific reason to go this route (e.g., legacy system constraints), you'll need to dynamically create tables via code or stored procedures. Here's a basic example:

Create a Sub-Table for a Specific Stock# #

-- Replace 'STOCK123' with the actual stock#
CREATE TABLE costs_STOCK123 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  description VARCHAR(100) NOT NULL,
  cost DECIMAL(10,2) NOT NULL
);

Query Costs from a Sub-Table #

SELECT description, cost FROM costs_STOCK123;

Automate Table Creation #

To avoid manually creating tables for every stock#, you'd write a script (in your application code or a stored procedure) that:

1. Takes a new stock# as input
2. Generates a table name (e.g., costs_<stock#>)
3. Executes the CREATE TABLE statement dynamically

Again, I can't stress enough that this approach will create long-term maintenance headaches. The standardized COSTS table with a stock# foreign key is almost always the better choice.

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