Inline editing - many to many relation

[HrErik]

Hey,



I have a database that I use for time budgeting in a school. I have a table with the activities, the teacher, number of hours in each week ond much more. The teacher field is related to a teachers table. Sometimes i have to put two ore more teachers on one activity. A simple example of the table could look like this:

ID	Activity	Teacher	Hours W1	Hours W2	Hours W3
1	Math	John	6	6	2
2	Economy	Adam	4	4	6
3	Project	John,Adam			12

At the moment I use a multi valued fielt for the teacher, and that works for now. But it is a problem for me, that the only way I can populate the teacher field is by using the mouse in a combo box. I would like to be able to just enter "John,Adam" in the field, og maybe insert values from the clipboard for fast editing.

So question 1 is:
Is there a way of populating a multivalued field without having to use a combobox in the form?

I've read many places, that multivalued fields is a bad solution, that can cause problems on the long term. So I've tried to use a many to many relationship instead. The database design works fine, bud the problem again is the list form. All the videos I've watched about the subject shows how to build a form with a subform. And that works fine, but again - I want to be able to simply enter the data in a field separated with a comma.

Question 2:
Is it possible to populate a fiels with comma saparated values i a form, and that way select values in a many to many relationship?

Erik

[rpeare]

My suggestion is... don't use multi valued fields. They are an incredibly bad idea because accessing the data in them is extremely difficult. You should properly normalize your data, you're essentially just making a flat excel file which really cuts down on the strength of using a relational database.

i.e.

tblTeacher
T_ID  T_Name ---> other teacher related fields
1     John
2     Adam

tblActivity
A_ID  A_Name ---> other activity specific fields
1     Math
2     Economy
3     Project

tblActivityTracker
AT_ID  A_ID  A_StartDate  A_EndDate A_Hours
1      1     1/1/2019     1/7/2019   6
2      2     1/1/2019     1/7/2019   4


tblActivityTrackerTeacher
ATT_ID  AT_ID  T_ID
1       1      1
2       2      2
3       3      1
4       3      2

Multi value fields are a menace!

[HrErik]

The model you describe is the database design I was planning (many to many). So my problem is how I make that look like a field with multiple values in my form, where i can edit the values fast.

[rpeare]

Everything in the setup I described is one to many.

if you want to be able to type something like

john, adam, susan

and have your database figure out which teachers those are you can parse the string at the commas then look up a teacher with those first names. However, it would break down any time you had a teacher with the same first name, then you'd have to do something like

john f, john b, adam, susan

where the identical first names would have to have the first letter of their last name in EVERY instance of, not just the ones where they appear together, that person.

Personally, if you are dealing with a small number of teachers you might be able to do a multi select list box to select the teachers to add, if you have a large number of teachers (more than say 15) that becomes impractical but still doable.

[davegri]

How about the approach in this example? Pretty quick to pick the teachers, and no name ambiguity.
MTM Schedule Events-davegri-v01.zip

[June7]

If you use approach of typing CSV names, I guarantee you will enter spelling errors and this is road to insanity.

[rpeare]

HAH, could not agree more with june here. Allowing the great unwashed to free form enter text fields is a recipe for headaches.