Others

How to Create a Bill of Materials for Assembly Inventory in Access

Steve K Sep 28, 2026 868 views

Question details

The user needs to set up a database structure that automatically identifies and deducts all underlying component parts when a parent assembly is checked out of inventory.

How to Create a Bill of Materials for Assembly Inventory in Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Managing an inventory database where finished goods (assemblies) are checked out, requiring the accurate deduction of individual raw materials or parts.
Observed behavior: Looking for the proper table design and query workflow to link assemblies to their required components and calculate checkout quantities.

Before you start

Ensure you have a basic understanding of MS Access table relationships and append queries. Back up your current database before creating staging tables to test the assembly expansion process.

Solution 1Recommended

Use an Adjacency-List Table and a Staging Table

Build a relational structure linking parent assemblies to child components and use a staging table to simulate recursion for multi-level assemblies.

A standard Bill of Materials (BOM) relies on a parent-child relationship. Because Microsoft Access does not natively support recursive SQL queries (like CTEs in SQL Server), handling multi-level assemblies requires a workaround.

By setting up an adjacency-list table and using a temporary staging table, you can run successive append queries. This progressively expands the top-level assembly layer by layer until only the base atomic components remain to be deducted from inventory.

Create a Primary Parts Table

Create a table named 'tbl_Parts' with fields like 'PartNum' (Primary Key), 'Description', and 'PartType' (to distinguish between an Assembly and an Atomic Component).

Build the Adjacency-List Table

Create a table named 'tbl_BOM' with fields 'MajorPartNum' (the parent assembly), 'MinorPartNum' (the child component), and 'Quantity'. Link both part number fields back to the 'tbl_Parts' table.

Set Up a Staging Table

Create a temporary table named 'tbl_Staging' to hold the components of the assembly currently being checked out. It should contain fields for the part number and the accumulated required quantity.

Write Expansion Queries

Create append queries that take assemblies from the staging table, look up their child components in 'tbl_BOM', multiply the quantities, and append the results back into the staging table. You will need to run this repeatedly (often automated via a VBA loop) until no more sub-assemblies are found.

Deduct from Inventory

Once the staging table contains only atomic parts, run a final append query to add these records to your 'tbl_StockOut' (stock-removal) table, permanently deducting the correct component quantities from your inventory.

Use an Adjacency-List Table and a Staging Table

Automating with VBA: Testing the expansion queries manually step-by-step ensures accurate quantities. Once verified, you can write a short VBA Do-While loop to run the append query automatically until the RecordsAffected count reaches zero.

Free Microsoft Office alternative

Manage Your Bill of Materials Easily with WPS Spreadsheet

Microsoft Access can be overly complex for standard inventory tasks due to its lack of native recursive queries. For a more straightforward approach, you can manage your Bill of Materials and track component inventory using WPS Spreadsheet. It offers full compatibility with Microsoft Excel formats and provides a familiar, code-free interface.

1. Open a BOM Template: Launch WPS Spreadsheet, search for 'Bill of Materials' in the template library, and open a layout that fits your product structure.
2. Input Assembly Data: List your parent assemblies, component parts, and required quantities in the designated columns.
3. Automate Calculations: Use standard spreadsheet formulas to dynamically calculate the total raw materials needed whenever an assembly order is placed.

100% compatible with Microsoft Excel (.xlsx) formats for seamless file sharing.Lightweight, free, and easy-to-use alternative to the Microsoft Office suite.Use built-in VLOOKUP and SUMIFS formulas to track assemblies without writing complex SQL.Access a massive library of free inventory and BOM spreadsheet templates.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use a standard query for a multi-level BOM in MS Access?

Standard SQL queries in MS Access do not support Common Table Expressions (CTEs) or recursion natively. Drilling down through multiple layers of sub-assemblies requires iterative processing, which is why staging tables or VBA loops are necessary to progressively expand the parts list.

What fields are essential for an adjacency-list BOM table?

At a minimum, your table requires a MajorPartNum (the parent assembly identifier), a MinorPartNum (the component part identifier), and a Quantity field (indicating how many of the component are needed for one unit of the parent). Both part identifier fields should link to your primary Parts table.

Can I prevent recursive infinite loops in my BOM database?

Yes. Infinite loops occur if an assembly is accidentally listed as a component of itself. You should add validation rules to your MS Access forms or VBA code to check that a MinorPartNum being inserted into the BOM table does not already exist as a parent MajorPartNum in the same hierarchical chain.

Office Quick Solutions