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!
What you are after is reinforced integrity of data - something you setup at the time you set data relationship in database.
As you can see, you can enforce data referential integrity and once done, your structure should look like this:
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.