0

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!

chrillof
  • 325
  • 2
  • 15

1 Answers1

0

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.

Velid
  • 104
  • 7
  • Hi and thanks for your reply. I've already set up the relations during my design and when I perform a query, I get a recordset which is not updatable. Also, I get duplicates when I've many relations (e.g. many colors for a person in the sample db). I'm looking for if Access can merge these two rows and instead give a dropdown with checkboxes as it would with multivalue fields. Unfortunately this does not solve my issue. Personally, I'd use a form as well, however, this is a feature requested by the recipient of the database, so I cannot bargain so much with it :) – chrillof Dec 27 '16 at 17:16
  • You can use continuous forms with sub forms, just ignore the warning, and change the setting, that will solve your issue. – Minty Dec 28 '16 at 11:01