Is there a simpler way to input into a multivalue field in Access?

Question

I have a database for the items which are kept in our office. I want to attach tags to those items so that the database can be easily searched - e.g. if I want to know how many staplers and hole punches we have, I can filter by the Stationery tag.

To do this, I have a multivalue lookup field called Tags in my Item table, with a list of all the tags that an item could be categorised by (it could be more than one).

Currently to input data, in either a form or a datasheet, I need to click on the drop-down menu and click on each tag I want to add. I want data entry to be as smooth and easy as possible, and all doable using the keyboard - but when I try to type in a tag, it doesn't let me. I can only click. For hundreds of items, this is too cumbersome.

Is there a solution, shortcut, or way to enter in values into a multivalue field just by using the keyboard, or with fewer steps?

Answer 1

I would strongly suggest avoiding using multivalue fields in MS Access: they go against database normalisation rules and furthermore you cannot easily access or filter the data they hold as you might any other single-valued field as part of a query.

Instead, consider that you have a many-to-many relationship: an Item may have many Tags, and a Tag may be assigned to many Items.

Therefore, if you wish to follow best practice and adhere to the rules of database normalisation, I would suggest using two separate tables to house the Items and Tags, and a Junction Table in the following manner:

Items table

This table would contain a primary key (PK) which uniquely identifies each item in the table, and properties whose values would not typically be shared by other items in the table, such as a Description.

In its most basic form, such a table might look like this:

+-------------+------------+
| itm_ID (PK) | Autonumber |
| itm_Desc    | Text       |
+-------------+------------+

+-------------+----------+
| itm_ID (PK) | itm_Desc |
+-------------+----------+
|           1 | Pen      |
|           2 | Pencil   |
|           3 | Chair    |
|         ... | ...      |
+-------------+----------+

Properties which may be shared by other items in the table (for example, Colour, Location, Type) should appear in their own separate tables, and be referenced by the item record using a foreign key (FK).

Tags table

This table would contain all available Tags, each of which would again be uniquely identified by a primary key field within the table:

+-------------+------------+
| tag_ID (PK) | Autonumber |
| tag_Desc    | Text       |
+-------------+------------+

+-------------+------------+
| tag_ID (PK) |  tag_Desc  |
+-------------+------------+
|           1 | Stationery |
|           2 | Furniture  |
|         ... | ...        |
+-------------+------------+

Items-Tags Junction Table

This junction table is used to represent the many-to-many relationship between Items and Tags, and could be structured in the following way:

+----------------+--------------+
| itg_itmID (PK) | Long Integer |
| itg_tagID (PK) | Long Integer |
+----------------+--------------+

+----------------+----------------+
| itg_itmID (PK) | itg_tagID (PK) |
+----------------+----------------+
|              1 |              1 |
|              2 |              1 |
|              3 |              2 |
+----------------+----------------+

As you can see from the above, we have easily assigned the Stationery tag to both the Pen and Pencil in our Items table (demonstrating a one-to-many relationship between Tags & Items) and similarly, we could assign multiple tags to any item (demonstrating a one-to-many relationship between Items & Tags).