Home  Fees/Services  Access Templates  Tutorials  Tutorial Downloads  Articles  Search  Contact  Privacy  Links

Table-Based Lookup List
Access Combo Box

Updated On 2/19/2018 1:03:46 AM

Combo Box Tutorial

Combo Box Tutorials
Fill Form Field Code
Combo Box: All/Any
Dependent DropDown
Cascading Combo Box
ComboBox Multiple Items
Lookup List Best Way
Lookup Lists 1 Table
Faster Combo Boxes
Table-based Combos
Y/N Drop Down List
Dynamic List Box

An Efficient Way to Store Many Lookup Lists

Combining Many Lookup Lists Tables Into One Table

One of the things that complicates larger Microsoft Access databases is the number of database objects - tables, queries, forms and reports.

This situation can make maintenance confusing and complicated, especially if you didn't create the database in the first place.

Our programming team has 15 members containing a wide variety of technical database knowledge and programming experience. Experts in small business management and scientific database programming.

Did you know you don't need to move to SQL/Server to run and manage your Access database on the net. There are many alternative solutions. Among them are Amazon's AWS cloud services and various hosting services in nearly every state.

 In addition to using my naming conventions (see: Database Naming Conventions) many programmers combine many lookup tables into a single table.

Here is an example of a standard design for such a table:

List of values Microsoft Access

Note the primary key fields.  Also set the LOV_ID field to Indexed (no duplicates) - this makes it equivalent to a primary key field for linking to other tables.   Also set the LOV_Category field to Indexed (duplicates OK) - this will speed up the queries.  Here's an example of the data entered into this table:

LOV examples

See the LOV_Category - this field makes it possible to combine multiple lookup lists into this single table.

The query design to create a lookup list for Product Color looks like this:

LOV Demonstration

And the final result looks like this:

All about Lists of values

Now you may say... hey! you've only substituted having many tables to having many queries!... and you would be correct.   To solve this situation consider not saving the Q_LOV_Product_Color as a query in the database.  Save the SQL statement for this query as the Row Source for the Combo Box:

Having all your lookup lists in one table probably won't increase the speed of your database.   If you have a split database (front end/back end) then you can significantly speed up your forms if you store the main L_LOVs table in the front end.  If the entries in this table change or are added to frequently then consider running a query, when the front end database starts up, that clears out the local (front end) L_LOVs table and inserts the values from the table stored in the back end database.  If you have many lookup lists on your forms then this can dramatically speed up the database once the front end is loaded.

A Blue Claw Database Design Article:

ODBC Links To Other DB Systems

Blue Claw Database Design Downloadable Tutorial:
Use Global Variables as Parameters  

A Blue Claw Software Design Template:

Farmer's Feed Inventory Tracking Database

Contact Information

Copyright 2000-2018 Blue Claw Database Design