Ms Access 2010 - mimic multivalued fields with real relationships

324 views Asked by At

I've building a database in which I have a main table and then many-to-many relationships with this table. I want to have an easy way to, in a single view (preferably datasheet view), have the ability to supply all data to my tables (with all its relationships). As of know, I use one record at a time and then use subforms in different tabs to accomplish this. However, I would like this to be done in a table-like environment rather than a form and from what I understand, using subforms are not allowed in continuous forms.

What I ask is if there is a way to, in datasheet view, insert all this data? From what I can tell, the multivalued fields does exactly this, with the only difference that Access hides its internal dependency table from the user. Thus, at least in theory, this should be possible.

As an example, I might have a database three tables:

People: ID, Name

Colors: ID, Colorname

PeopleColorMap: PeopleID, ColorID

Each person can have multiple favourite colors, and this should preferably be set in datasheet view which shows the following fields: People.ID, People.Name, People.ColorID.

The latter column shall however be shown as a drop-down with checkboxes in order to avoid listing multiple rows apart from this field.

I have several such dependencies, although if I can solve one of them, then I'll hopefully figure the rest out.

Thanks for any help!

1

There are 1 answers

2
Velid On

What you are after is reinforced integrity of data - something you setup at the time you set data relationship in database.

enter image description here

As you can see, you can enforce data referential integrity and once done, your structure should look like this:

enter image description here

Once done, you can create query where you populate data. I still would suggest to use form, to validate input and to make it easier for users.