Others

How to Create Microsoft Access SKUs with Department Prefixes

Phi Hung Vo Oct 1, 2026 869 views

Question details

The user needs to generate custom stock-keeping units (SKUs) in a database where the prefix identifies the department and the suffix identifies the product.

How to Create Microsoft Access SKUs with Department-Based Prefixes

Product: Microsoft Access
Device & OS: not provided
Scenario: Managing an inventory database and assigning structured product codes dynamically.
Observed behavior: Needs to automatically combine a department code with an AutoNumber or calculated sequence to display a formatted SKU like 101-0001.

Before you start

Ensure you have a foundational understanding of table design in Access, specifically involving primary keys, foreign keys, and basic calculated fields.

Solution 1Recommended

Use AutoNumber with Concatenation for a Global Sequence

Best for databases that do not require the product ID to restart at 1 for every new department.

If a single continuous numerical sequence across all departments is acceptable, you can combine the standard AutoNumber feature with the department code for display purposes.

Set up the primary and foreign keys

Open your Products table in Design View. Set an AutoNumber field as the primary key (ProductID) and add a foreign key field for DepartmentID.

Create a display expression

In your form or query, create a new calculated field to combine the two values into a single SKU string.

Apply the formatting formula

Use the expression =Format([DepartmentID],"000") & "-" & Format([ProductID],"0000") to generate the final SKU output (e.g., 101-0001).

Use AutoNumber with Concatenation for a Global Sequence

Simplifies Database Design: This method ensures every product has a globally unique ID without requiring complex VBA code to handle incremental sequencing.

Solution 2

Calculate Department-Specific Sequences in Code

Use this method if each department's SKU sequence must start from 0001 independently.

Free Microsoft Office alternative

Manage Inventory and SKUs Easily with WPS Office

While Microsoft Access requires complex setup for databases, WPS Spreadsheet offers an intuitive and free alternative for managing inventory, tracking stock, and generating custom SKUs with simple formulas. WPS Office provides full compatibility with Microsoft Office formats, a familiar interface, and lightweight performance.

1. Open WPS Spreadsheet: Create a new workbook or open your existing inventory tracker document.
2. Set up your tracking columns: Create adjacent columns for 'Department Code', 'Item Number', and 'SKU'.
3. Apply a text formula: In the SKU column, enter a formula like =TEXT(A2,"000")&"-"&TEXT(B2,"0000") and drag it down to auto-generate structured SKUs.

Easily generate custom SKUs using TEXT and CONCATENATE formulas.Fully compatible with Microsoft Excel (.xlsx) formats.Free, lightweight, and easy-to-use alternative to Microsoft Office.Seamlessly migrate your inventory trackers without losing data.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't AutoNumber work for department-specific sequences?

AutoNumber in Microsoft Access is designed to create a single, continuous, and globally unique sequence for an entire table. It cannot reset or run multiple independent sequences based on a foreign key like a Department ID.

How do I prevent duplicate SKUs in a multi-user Access database?

When calculating custom sequences via code (like using DMax), you must apply a unique composite index combining the DepartmentID and ProductID in the table design. This ensures that simultaneous entries do not accidentally generate the same SKU.

Can I hide the base IDs and only show the formatted SKU in forms?

Yes, you can bind your form's text box to a query that concatenates the DepartmentID and ProductID into the formatted SKU, or input the Format expression directly into the control source of an unbound text box.

Office Quick Solutions