CODEWITHSUNDEEP
ms-accessdatabase-design
Seb
Seb

Reputation: 520

Access, allow mutli non - predefined value

Issue : I created a table on Access and I am trying to add a field in which the user can enter multiple values, but which are not predefined in a list. Those values must all respect a certain format though.

My current solution :

enter image description here enter image description here

I created a combo box with a validation rule. I specified also Row Source Type as Value List and didn't put any value in the list so that the user can enter them by himself and will only be able to use them if they follow the validation rule.

As you can see in the picture above, the user could add any values to the list, but the value 88 could not be used since it doesn't obey the validation rule.

Unfortunately, this is not a nice solution since all values added by the user will sum up and the list will get bigger and bigger with time...do you know a better way ?

Upvotes: 0

Views: 34

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

A relational database system deals with related data. It uses tables to express these relations.

In your example there is a table where each record is related to a list of values. So have another table to hold this data and express this relation. This new table contains two things:

  • the main table's primary key (often this is a single ID, but it can also be a composite key, e.g. a company number plus an employee ID in order to identify an employee)
  • the four digit value

Then add a contraint to the value column to ensure it's four digits always.

If it is only certain four digits you want to allow for, then you'd even add another table holding these, so you can use them as a reference (i.e. foreign key).

Upvotes: 1

Related Questions