VBA & Macro Problems

How to Create Separate Records Based on Order Quantity in Microsoft Access

Khadija Khan Oct 9, 2026 868 views

Question details

The user wants to generate individual tracking records in a database when the ordered quantity of an item is greater than one to properly manage shipments and backorders.

How to Create Separate Records Based on Order Quantity in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Designing a database order form that needs to track partial shipments, individual item deliveries, or calculate remaining backorders based on order quantities.
Observed behavior: Instead of duplicating order records to reflect multiple items, the user is looking for an optimal relational design or VBA method to track multiple shipments for a single order.

Before you start

Before altering your database schema or writing VBA code, analyze your workflow to determine whether you simply need to track bulk shipment quantities or if you need to track each item individually (e.g., by serial number).

Solution 1Recommended

Use a Relational 'Received' Table for Bulk Shipment Tracking

This is the optimal relational database approach. Instead of duplicating order rows, you keep one order record and track multiple receipt transactions in a separate table.

Duplicating order records is not recommended for a relational database. Keeping a single order record and logging receipts in a child table ensures your data remains normalized. You can then use aggregate queries to calculate backorders dynamically.

Create a Received Table

Open Microsoft Access, navigate to the 'Create' tab on the Ribbon, and click 'Table Design'. Create a new table named tblReceived.

Define Tracking Fields

Add the following fields: ReceivedID (AutoNumber, Primary Key), OrderID (Number, Foreign Key), DateReceived (Date/Time), and QuantityReceived (Number). Save the table.

Establish Relationships

Go to the 'Database Tools' tab and click 'Relationships'. Drag the OrderID field from your main Orders table to the OrderID field in tblReceived to enforce referential integrity.

Calculate Backorders with SQL

Go to the 'Create' tab, click 'Query Design', close the dialog, and switch to 'SQL View'. Paste an SQL query that selects the order data and subtracts the SUM(QuantityReceived) from the main Order Quantity to calculate the backorder.

Use a Relational 'Received' Table for Bulk Shipment Tracking

Best Practice: Using a separate Received table prevents redundant data entry and makes it much easier to generate accurate partial-delivery reports.

Solution 2

Create Individual Item Records using VBA

Use this method if your business logic requires tracking each ordered instrument individually, such as logging distinct serial numbers or specific lead times per item.

Free Microsoft Office alternative

Track Orders Efficiently with WPS Spreadsheets

While WPS Office does not include a direct relational database application like Microsoft Access, it features a highly capable Spreadsheet program that is perfect for tracking order quantities, logging partial shipments, and calculating backorders using formulas and macros.

1. Set Up Order Logs: Open WPS Spreadsheets and create a primary sheet for Orders, recording order numbers and total quantities.
2. Track Deliveries: Create a secondary sheet for Shipments. Use the VLOOKUP or SUMIFS functions to link shipment quantities back to the main order.
3. Calculate Backorders: Add a formula column in the Orders sheet that subtracts the sum of received shipments from the total quantity to instantly view backordered amounts.

Free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel (.xlsx) formats for advanced data trackingBuilt-in macro and VBA support for automating repetitive data entry tasksIntuitive tabbed interface that makes organizing multiple order logs simple

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I just duplicate the order records for each item?

Duplicating the main order record for each item violates database normalization principles. It creates redundant data, making it difficult to update customer information or overarching order details without causing inconsistencies.

How do I calculate remaining backorders in Access queries?

You can calculate backorders by subtracting the total received quantities from the original ordered quantity. Use a subquery in your SQL statement, such as: Quantity - (SELECT SUM(QuantityReceived) FROM tblReceived WHERE tblReceived.OrderID = Orders.OrderID).

Can I automate the creation of individual child records?

Yes. You can use an 'After Update' event on your order entry form to trigger a VBA macro. A standard 'For...Next' loop can run an INSERT SQL statement matching the number of items ordered, generating the exact number of blank child records needed.

Excel & Data Problems