How to Track Inventory Additions and Subtractions in Microsoft Access

Use a transaction type code in Microsoft Access to store additions and removals, then calculate quantity on hand with a totals query.

How to structure the inventory field in Access

In Microsoft Access, the cleanest way to add an inventory addition and subtraction field is not to store a running “Quantity on Hand” value in the transactions table. Instead, store each movement as a transaction and mark it as an addition or a removal, then calculate stock from those records when needed.

Saving a running stock total can drift out of sync with real transactions.

Use a code that represents whether stock is added or removed.

Calculate stock later

A totals query can return the current quantity on hand per product.

This workflow uses a lookup table, a foreign key in the transactions table, a form combo box, and a stock totals query.

Create a small table named TransactionTypes with two fields: TransactionTypeCode as an Integer Number primary key and TransactionType as Short Text.

Add exactly two rows: 1 = Addition and -1 = Removal. This gives you the numeric value needed for later calculations.

In the Transactions table, add a field named TransactionTypeCode. Set its data type to Integer Number and index it with Duplicates allowed.

Then create a relationship from Transactions.TransactionTypeCode to TransactionTypes.TransactionTypeCode and enable Enforce Referential Integrity. This prevents invalid values from being stored.

On your data entry form, insert a combo box so users can choose Addition or Removal without typing codes manually.

• Name: cboTransactionTypeCode
• ControlSource: TransactionTypeCode
• BoundColumn: 1
• ColumnCount: 2
• Column widths: 0cm or 0"
• RowSource: SELECT TransactionTypeCode, TransactionType FROM TransactionTypes ORDER BY TransactionType;

Use a query instead of a stored field to return the current stock level for each product. The query multiplies the transaction quantity by the transaction type code, so additions stay positive and removals become negative.

Expected result: each product returns one calculated StockLevel value based on all recorded additions and removals.

Enter one test transaction for an addition and one for a removal against the same product. Then run the totals query and confirm that the result equals added quantity minus removed quantity.

If the total looks wrong, check that the combo box is saving 1 or -1, not the display text, and confirm that the relationship uses the correct key field.

Field setup at a glance

The reliable Access pattern is transaction-based stock control

For this Microsoft Access setup, use a lookup table with codes for additions and removals, store that code in each transaction, and calculate stock with a totals query. It is more accurate than saving a running quantity on hand directly in the table.

Use WPS Office as a Free Microsoft Office Alternative

Use Word, Excel, and PPT for FREE

Free Download

The source guidance treats inventory as a movement-based system, which is more reliable than storing a single changing balance in one field.

Why movement records work better

• Every stock change is preserved as a transaction, so you keep a full history of additions and removals.
• The current quantity on hand is derived from those movements, which reduces synchronization errors.
• This model also supports later adjustments, including stock takes, without redesigning the whole database.

What this method does and does not support

• It supports calculating stock by product from transaction history.
• It does not rely on a permanently stored running total in the transactions table.
• Inventory control can become complex in a relational database, so this is a solid basic pattern rather than a complete warehouse system by itself.

Reference materials mentioned in the source

• Inventory.zip demonstrates stock on hand calculations from inventory movements and stock-take adjustments.
• The Northwind Developer Edition template is suggested as a model for understanding basic inventory control patterns in Access.
• Both references are examples only and will need adaptation to your own products, parts, and transaction rules.

A practical note for spreadsheet and document work

This topic is specific to Microsoft Access database design, so WPS Office cannot create Access relationships, form combo boxes, or run Access SQL objects inside an .accdb file.

However, WPS Office can still help around this workflow for compatible local XLSX, DOCX, PPTX, and PDF files. You can document table structures, review exported stock reports, annotate PDFs, and use WPS AI for formula explanations or process summaries, with the caveat that Access-specific forms, queries, and relational controls must still be built and tested in Microsoft Access.

The combo box should store the numeric code, not the visible word.

Removal must use -1, otherwise the totals query will not subtract stock.

Use a LEFT JOIN if you want products with no transactions to remain visible.

Test one product with known values before rolling the design into live data.

Common follow-up questions

Track Inventory Additions and Subtractions in Microsoft Access FAQs

Why use 1 and -1 instead of storing Addition and Removal only as text?

The numeric code makes the totals query simple. Access can multiply Quantity by 1 or -1 so additions increase stock and removals reduce it automatically.

How can I confirm that the combo box is saving the correct value?

Open the underlying Transactions table after entering a record and check the TransactionTypeCode field. It should contain 1 for additions or -1 for removals, not the display text.

Can this approach handle stock takes or more advanced inventory adjustments?

Not by itself. The source notes that inventory control can become complex, and demo templates such as Inventory.zip are useful when you need movement-based stock plus periodic adjustment logic.

How can I confirm the stock query is working correctly?

Enter test transactions for one product, such as an addition of 10 and a removal of 3, then run the query. The expected StockLevel should return 7 for that product.