MS Access Multiple Values For A Single Field: A New Data Option From Version 2007

MS Access Multiple Values For A Single Field: A New Data Option From Version 2007

With traditional Access databases, storing data values in a field were very ‘single-minded’! However with the introduction of version 2007, Access multiple values can be achieved into one field using the new element called ‘Allow Multiple Values‘ which is a new option available when using the Lookup Wizard feature to create either a drop-down Combo box or List box.

For the ‘purist’ database developer, you do not even need to use the wizard tool as there are new properties available via the ‘Lookup‘ tab for each field in your table design view.

access multiple values

Before I step you through the very basics of creating a multiple value input field, one needs to understand why and when you should consider this alternative.

MS Access Multiple Values For A Single Field: A New Data Option From Version 2007

The most important and key point here is that Microsoft Access should be designed and used as a relational database following the concepts and methodologies using related tables and using this feature may appear to be a violation of what is typically referred to as Normalisation (or Normalization for our American cousins!)

So I want to clear the air here and demystify how this application uses multiple values and in fact still maintains the data integrity with no violations at all. 

If you wanted to develop the ability to associate multiple values for a record, in the conventional sense you would be looking to create a three table (many-to-many) relationship which is an example I want to illustrate as follows:

Here, I have a Products table showing the inventory of a food wholesaler. Each potential product has varying quantity options and for the sake of keeping the database normalised (and optimised), we want to avoid record duplication; in this case repeating the same stock item for each quantity instance. 

access many to many tables

Therefore, the above illustration of a three table split allows the system to assign one or more (middle table) quantities for a known product from the outer two tables offering the permutations available (the one-sides)

Now when using Access multiple values to store one or more values, your database system still maintains this structure but the application simply suppresses this view and creates an internal collection (of elements) instead.

On the outside, your table may look something like:

access multiple values relationship

Notice the sub node called ‘Quantity Units Available.Value‘ which is the hidden assigned (many-to-many) table and the control when running your table with a Checkbox in the list!

When should you consider using this option? The simple answer is when building applications for other users to either develop alongside or want a quick fix for end users, it’s just a convenient way when wanting to generate Access forms as by default inherit this type of control from your table.

On a more technical level, make sure you know your options, here are three:

  • Don’t try to use this feature with long lists.
  • Use multivalue fields if you’re using SharePoint Services.
  • SQL Server converts a multivalue field to a Memo field, so keep upgrading in mind when making your choice.

MS Access Multiple Values: Creating a multi-value list is as follows:

1.  Open your table in design view.

2.  Depending which version you use; In Access 2007 – go to Datasheet tab, in the Fields & Columns group, click Lookup Column. For Access 2010 (onwards), go to Design tab, in the Tools group, click Modify Lookups.

3.  Follow the wizard prompts. It’s ideal to select a table or query as you source and not generate a fixed list (as this will require a manual re-development at a later stage).

4.  Make sure on the last screen of the wizard prompt that you do choose the option ‘Allow Multiple Values‘ before click the Finish button.

5. Take a quick peak at the properties generated for your new field and tweak where necessary.

6.  Save change and run your table.

Final point to mention is when building your queries, Access multiple values will also be available with a drop down control if you choose the parent node  ‘Quantity Units Available‘ but if your decide to pick the actual element itself by selecting the sub node  ‘Quantity Units Available.Value‘ it will drill-down and show a multiple list view for all assigned items for each product as if you were using the more conventional approach which really confirms the theory explained in this post.

Want to learn more about Access database? Take a look at my eBook offers.

Leave a Reply

Your email address will not be published. Required fields are marked *