How to find a specific item from thousands

OLUGBENGA AJAO 40 Reputation points
2026-10-26T00:54:18.6533333+00:00

I have a giant table that lists items that customers can purchase. The table has just 2 fields: unique name and detailed description. However, this is hardly useful to a customer who is selecting an item by the attributes in the description. These attributes can be up to 7 in some cases: Primary Category, Sub-category, Class, Color, Size, Package, etc. Fortunately, no 2 items have matching attributes.

My task is to make the selection process easy for a customer.

I have built another table in MS Access (NewItems) with the primary key set to auto-number and it retains the Item name and description fields of the original table. I have also created other tables for the attributes with their primary keys also set to autonumber. For example, the Primary Category table has 2 fields: PCId (Autonumber) and PrimaryCategory: Short Text. I repeated this for all other attributes. So, my NewItems table has 9 fields: ItemID (Autonumber, primary), ItemName (Short Text) ItemDescription (Short Text), PrimaryCategory (Number), SubCategory (Number), Class (Number), Color (Number), Size (Number). Package (Number).

I can query this table using any of the attribute fields. And then query the result using another attribute, till I get just one record. However, I have a hunch that there is an easier way to do this. Please help me.

Microsoft 365 and Office | Access | For home | Windows
Answer accepted by question author
Dora-T 1,990 Reputation points Independent Advisor
2026-10-26T03:12:19.68+00:00

Hi OLUGBENGA AJAO

I recommend referring to the AI-generated answer, as it outlines an alternative approach that may help simplify the item selection process by using a single query with optional criteria. 

Based on your description, I also tested this approach in my Access environment using sample data. If you have not already implemented a similar setup, you can try the following steps: 

Step 1: Create the search query 

Go to Create > Query Design, close the Add Tables dialog, and switch to SQL View. Enter the SQL below and save the query as qryItemSearch. 

SELECT N.ItemID, N.ItemName, N.ItemDescription, PC.PrimaryCategory, SC.SubCategory, C.ClassName, CO.ColorName, S.SizeName, P.PackageName
FROM (((((NewItems AS N LEFT JOIN PrimaryCategories AS PC ON N.PrimaryCategoryID = PC.PCId) LEFT JOIN SubCategories AS SC ON N.SubCategoryID = SC.SCId) LEFT JOIN Classes AS C ON N.ClassID = C.ClassId) LEFT JOIN Colors AS CO ON N.ColorID = CO.ColorId) LEFT JOIN Sizes AS S ON N.SizeID = S.SizeId) LEFT JOIN Packages AS P ON N.PackageID = P.PackageId
WHERE (Forms!frmItemFinder!cboPrimaryCategory Is Null
     OR N.PrimaryCategoryID = Forms!frmItemFinder!cboPrimaryCategory)
AND (Forms!frmItemFinder!cboSubCategory Is Null
     OR N.SubCategoryID = Forms!frmItemFinder!cboSubCategory)
AND (Forms!frmItemFinder!cboClass Is Null
     OR N.ClassID = Forms!frmItemFinder!cboClass)
AND (Forms!frmItemFinder!cboColor Is Null
     OR N.ColorID = Forms!frmItemFinder!cboColor)
AND (Forms!frmItemFinder!cboSize Is Null
     OR N.SizeID = Forms!frmItemFinder!cboSize)
AND (Forms!frmItemFinder!cboPackage Is Null
     OR N.PackageID = Forms!frmItemFinder!cboPackage)
ORDER BY N.ItemName;

This example assumes that NewItems stores the attribute IDs and that the lookup tables contain the corresponding names. Adjust the table and field names to match your database. 

Step 2: Create the frmItemFinder search form 

1.Select Create > Form Design. 

2.Save the form as frmItemFinder. 

3.Add six Combo Box controls, one Command Button, one Label, and one Subform/Subreport control. 

Arrange the controls as needed. For each combo box, set the following properties in the Property Sheet: 

-Row Source Type: Table/Query 

-Bound Column: 1 

-Column Count: 2 

-Column Widths: 0";2" 

-Control Source: leave blank

The first column contains the lookup ID, while the second displays the readable name. 

Set each combo box's Row Source to the corresponding SQL below. 

-cboPrimaryCategory 

SELECT PCId, PrimaryCategory FROM PrimaryCategories ORDER BY PrimaryCategory; 

-cboSubCategory 

SELECT SCId, SubCategory FROM SubCategories ORDER BY SubCategory;

 -cboClass 

SELECT ClassId, ClassName FROM Classes ORDER BY ClassName;

 -cboColor 

SELECT ColorId, ColorName FROM Colors ORDER BY ColorName;

-cboSize 

SELECT SizeId, SizeName FROM Sizes ORDER BY SizeName;

 -cboPackage 

SELECT PackageId, PackageName FROM Packages ORDER BY PackageName;

For each combo box, set the After Update property to [Event Procedure]. 

Next, configure the other controls: 

-cmdClear button 

+Name: cmdClear 

+Caption: Clear Filters 

+On Click: [Event Procedure] 

-lblStatus label 

+Name: lblStatus 

-subItemResults subform control 

Name: subItemResults 

You can link the results form in Step 3. 

Step 3: Create the results subform  

1.Select Create > Form Design. 

2.Set the form's Record Source to qryItemSearch. 

3.Save the form as sfrmItemResults. 

4.Set Default View to Datasheet. 

Ensure the following fields are available in the results: 

-ItemID 

-ItemName 

-ItemDescription 

-PrimaryCategory 

-SubCategory 

-ClassName 

-ColorName 

-SizeName 

-PackageName 

5.Return to frmItemFinder in Design View. 

-Select the subform control named subItemResults. 

-Set its Source Object to Form.sfrmItemResults. 

-Leave Link Master Fields and Link Child Fields blank, because the query applies the filters directly from the combo boxes. 

-Save the form. 

Step 4: Add the VBA code  

Open frmItemFinder in Design View, select the form, and open its code module. Save the code and close the VBA editor. 

Option Compare Database
Option Explicit
Private Sub ApplyItemFilter()
    On Error GoTo ErrHandler
    Dim rs As DAO.Recordset
    Me.subItemResults.Form.Requery
    Set rs = Me.subItemResults.Form.RecordsetClone
    If rs.BOF And rs.EOF Then
        Me.lblStatus.Caption = _
            "No matching items found. Try changing your selections."
    Else
        rs.MoveLast
        Me.lblStatus.Caption = _
            "Matching items: " & rs.RecordCount
    End If
    rs.Close
    Set rs = Nothing
    Exit Sub
ErrHandler:
    MsgBox "Could not refresh results. Check the control names.", _
           vbExclamation
End Sub
Private Sub cboPrimaryCategory_AfterUpdate()
    ApplyItemFilter
End Sub
Private Sub cboSubCategory_AfterUpdate()
    ApplyItemFilter
End Sub
Private Sub cboClass_AfterUpdate()
    ApplyItemFilter
End Sub
Private Sub cboColor_AfterUpdate()
    ApplyItemFilter
End Sub
Private Sub cboSize_AfterUpdate()
    ApplyItemFilter
End Sub
Private Sub cboPackage_AfterUpdate()
    ApplyItemFilter
End Sub
Private Sub cmdClear_Click()
    Me.cboPrimaryCategory = Null
    Me.cboSubCategory = Null
    Me.cboClass = Null
    Me.cboColor = Null
    Me.cboSize = Null
    Me.cboPackage = Null
    ApplyItemFilter
End Sub
Private Sub Detail_Click()
End Sub

Step 5: Test the form  

Open frmItemFinder, apply filters, verify the results, and click Clear Filters to reset. 

User's image

User's image

Please adjust the table names, field names, and form/control names in the query and VBA code to match your database structure. The exact implementation may vary depending on your existing tables and relationships. 

I hope this gives you a useful starting point. 

Was this answer helpful?

1 person found this answer helpful.
Answer accepted by question author

AI answer

2026-10-26T00:55:05.84+00:00

Create one search form with unbound combo boxes for all attributes and use a single saved query that treats blank selectors as “any value”; requery the results after each selection instead of creating a chain of queries.

1. Confirm the relationships

In Database Tools → Relationships, relate each lookup table’s primary key to its corresponding Number field in NewItems, for example:

  • PrimaryCategories.PCId → NewItems.PrimaryCategory
  • SubCategories.SCId → NewItems.SubCategory
  • Classes.ClassId → NewItems.Class
  • Continue for Color, Size, and Package.

Enable referential integrity where appropriate. The related fields must use compatible data types. Access queries can then use joins to retrieve the readable attribute values instead of displaying their numeric IDs.^1^

2. Create the search form

Create an unbound form named frmItemFinder, then add unbound combo boxes:

Control name Row Source
cboPrimaryCategory Primary Category table
cboSubCategory Sub-category table
cboClass Class table
cboColor Color table
cboSize Size table
cboPackage Package table

For each combo box:

  1. Open its property sheet.
  2. Set Row Source Type to Table/Query.
  3. Set Row Source to a query returning the ID and description, such as: 
       SELECT PCId, PrimaryCategory
   FROM PrimaryCategories
   ORDER BY PrimaryCategory;
  4. Set Bound Column to 1.
  5. Set Column Count to 2.
  6. Set Column Widths to 0";2" so the ID is stored but the customer sees the description.
  7. Leave Control Source blank, making it an unbound search control.

Access supports combo boxes populated from tables, queries, or SQL statements, including hidden bound ID columns.^2^

3. Build one search query

Create a query in SQL View named qryItemSearch:

SELECT ItemID, ItemName, ItemDescription
FROM NewItems
WHERE
    (Forms!frmItemFinder!cboPrimaryCategory Is Null
     OR PrimaryCategory = Forms!frmItemFinder!cboPrimaryCategory)
AND (Forms!frmItemFinder!cboSubCategory Is Null
     OR SubCategory = Forms!frmItemFinder!cboSubCategory)
AND (Forms!frmItemFinder!cboClass Is Null
     OR Class = Forms!frmItemFinder!cboClass)
AND (Forms!frmItemFinder!cboColor Is Null
     OR Color = Forms!frmItemFinder!cboColor)
AND (Forms!frmItemFinder!cboSize Is Null
     OR Size = Forms!frmItemFinder!cboSize)
AND (Forms!frmItemFinder!cboPackage Is Null
     OR Package = Forms!frmItemFinder!cboPackage)
ORDER BY ItemName;

Each condition means:

  • If the corresponding combo box is blank, do not restrict that attribute.
  • If it contains an ID, return only matching items.
  • All selected attributes are combined with AND.

The form must be open when this query runs; otherwise Access will prompt for the Forms!frmItemFinder!... parameters.

Create a continuous form or datasheet form named sfrmItemResults, set its Record Source to qryItemSearch, and place it on frmItemFinder as a subform. Do not set Link Master Fields or Link Child Fields.

4. Refresh the results automatically

Add this procedure to the frmItemFinder form module:

Private Sub ApplyItemFilter()
    Me.sfrmItemResults.Form.Requery
End Sub

Private Sub cboPrimaryCategory_AfterUpdate()
    ApplyItemFilter
End Sub

Private Sub cboSubCategory_AfterUpdate()
    ApplyItemFilter
End Sub

Add the same one-line AfterUpdate call for Class, Color, Size, and Package. Requery reruns the subform’s underlying query, so the displayed records reflect the current selections.^3^

Add a Clear button if needed:

Private Sub cmdClear_Click()
    Me.cboPrimaryCategory = Null
    Me.cboSubCategory = Null
    Me.cboClass = Null
    Me.cboColor = Null
    Me.cboSize = Null
    Me.cboPackage = Null
    Me.sfrmItemResults.Form.Requery
End Sub

Optional: cascading selections

If customers should only see valid subcategories after choosing a primary category, make the Sub-category combo’s Row Source depend on the first selection:

SELECT DISTINCT S.SCId, S.SubCategory
FROM SubCategories AS S
INNER JOIN NewItems AS N
    ON S.SCId = N.SubCategory
WHERE Forms!frmItemFinder!cboPrimaryCategory Is Null
   OR N.PrimaryCategory =
      Forms!frmItemFinder!cboPrimaryCategory
ORDER BY S.SubCategory;

Then extend the first combo’s event:

Private Sub cboPrimaryCategory_AfterUpdate()
    Me.cboSubCategory = Null
    Me.cboSubCategory.Requery
    Me.sfrmItemResults.Form.Requery
End Sub

Combo-box Requery reruns its Row Source and displays the currently applicable choices.^4^

This cascading pattern can be repeated for later attributes, but the single search query remains the final source of the matching items.

References

  1. Perform joins using Access SQL
  2. Add a list box or combo box | Microsoft Support
  3. Form.Requery method (Access)
  4. ComboBox.Requery method (Access)
AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.