Access 2007 - multiple value field

Access now allows the designer to specify multiple values in a field. Users like this feature because the wizard allows them to build relationships between tables without having to learn about one-to-many relationships or primary keys. The wizard hides all this complexity but the danger is that the wizard can lead the users to build a structure that's impossible to understand or maintain. Just as an example, I tried the new feature on the link between Customers and Employees in the old NorthWind sample database from Access XP.

This was difficult to set up before because each customer will have been served many times by several different employees and each employee will have made many sales to many different customers. In a traditional relational database this is known as a "many to many" relationship and has to be implemented by creating a third table to record every interaction between a Customer and an Employee.

The old database did this properly with the Orders table which held a foreign key into the Customer table and a foreign key into the Employee table. For each order this gave us a reference to the customer who had placed the order and a reference to the employee who had taken the order. That allowed us to record the fact that an employee can serve many different customers and that a customer can be served by many different employees but it did mean that a little thought had to go into the design of the tables.

The new way is very much easier. All that the developer has to do is add a "ServedBy" field to the Customer table. Make it a "Lookup" field and the wizard will ask you which table and fields you want to look up. All this is much as it was in earlier versions of Microsoft Access but in the final stage the wizard gives you the extra option of ticking "Allow Multiple Values". Click on this and the damage is done. You now have a table which looks as though it's storing multiple values in a single field.

When you open the table you find that this arrangement works very well for the typical user. They will see the familiar drop-down list for the lookup field but the difference is that the list now has tick boxes so that they can tick the multiple employees who have served this particular customer.

At first sight it's very much easier to set this up than it is to create a new table and use it to hold foreign keys to the Customers and Employees tables and then write a sub form to show the details of the employees associated with a particular customer. There are however some drawbacks to the scheme and you should think carefully before making use of this feature.

Misleading displays

The first disadvantage is that we can't always be sure that we're seeing the full picture. The screen shot shows that this customer has used four employees and we can see in the field that there are four codes, 2, 3, 7, & 9. We can see what's happening here but it becomes more dificult when there are a hundred items in the list and a dozen have been ticked. The list of names will scroll off the screen vertically and the codes will scroll off horizontally. The user can never be sure that they are seeing all the details.

Exporting and upsizing

The Help system in Access 2007 boasts that few other database systems give you this feature. That's certainly true and it brings difficulties when you want to export your data. It brings more difficulties if you ever want to migrate the data to SQL Server. Access 2007 has good links with SQL Server and you can easily move to using SQL Server when your system outgrows Access. The problem is that SQL Server doesn't have an equivalent to multiple valued fields so the data, and any code that uses it, will have to be converted.

Queries

Queries in Access 2007 have been extended to be able to cope with multiple value fields. When you have a field with multiple values then the Query Designer gives you a new option. You can select the field "ServedBy" and get the value store in that field - "2, 3, 7, 9" for example - or you can select "ServedBy.Value" and have that list expanded as four identical Customer records, one for each value in the list.

Be careful to choose the right option and be aware that this query will have to be rewritten if you have to move your data to another system.

When to use multiple valued fields

Despite these reservations, there are times when multiple value fields are very useful:

• If you are using SharePoint. SharePoint Services 3.0 has this feature and multiple valued fields were introduced to Access 2007 to make it easier for you to share data between Access and SharePoint.
• If you require only a few choices in the lookup. It may not be worth the effort of setting up the additional fields and tables if there are only ever going to be two options to choose from.
• If this is a small system - perhaps for personal use - and you are absolutely sure that you are not going to be upgrading to anything larger in the future.
• If this is a temporary system. If it's only going to be used for a particular task for a short period of time then this feature will save you a lot of development time.