Eliminated evil lookup field in table by creating separate junction table, now confounded by form field.

[Missomissou]

Earlier, some of you helped me overhaul my table/relationships design. The solution you offered was very elegant (thank you again!). My queries are still working well. But I have been stymied by how to create an associated data entry field on the user input form. I tried creating a combo box, and then a list box, in the form design, to populate the AllALWRIAuthors field shown below. But this doesn't seem quite right. For one thing, the records in form view are displaying incorrect data--almost like the field contains a legacy of the last record. Luckily, so far, it doesn't appear as if it's overwritten any of my existing data. In general, it seems like MVFs are frowned upon, and so I have a feeling I should somehow be storing these values (AllALWRIAuthors) in the junction table I've already created (and so eliminate this field here). But I'm confused as how exactly to do this. And also, maybe worse, what question exactly to ask google. Any ideas?

 

[tvanstiphout]

In database terms we can say: one review has 1 to many authors, and each author writes 0 to many reviews.
That is a classic "many-to-many" relation, often written as "M:M". It is created in an RDBMS like Access with 3 tables: Reviews, Authors, and the junction table ReviewingAuthors. It sounds like you already have this db design. Hopefully you also have enforced relations.
To create a form to edit such data, in its simplest incarnation you do this:
1. Select the Reviews table in Navigation Pane. On the Create tab of the ribbon, click on Form. Access will create an auto-form. Don't worry about looks right now.
2. In design view, expand the form vertically and in the open space, drop the ReviewingAuthors table. Access creates a subform and if you have the Relationships setup, it will prompt you for the linking information between this table and the parent table.
3. Design the form again, step into the subform, and change the AuthorID control to a combobox. Set the RowSource to "select AuthorID, AuthorName from Authors", the column count to 2, and column widths to "0;1".
Run the form. You can now enter data in the form and subform, selecting author from dropdown.
(of course you change my object names to yours throughout)

 

[mike60smart]

Earlier, some of you helped me overhaul my table/relationships design. The solution you offered was very elegant (thank you again!). My queries are still working well. But I have been stymied by how to create an associated data entry field on the user input form. I tried creating a combo box, and then a list box, in the form design, to populate the AllALWRIAuthors field shown below. But this doesn't seem quite right. For one thing, the records in form view are displaying incorrect data--almost like the field contains a legacy of the last record. Luckily, so far, it doesn't appear as if it's overwritten any of my existing data. In general, it seems like MVFs are frowned upon, and so I have a feeling I should somehow be storing these values (AllALWRIAuthors) in the junction table I've already created (and so eliminate this field here). But I'm confused as how exactly to do this. And also, maybe worse, what question exactly to ask google. Any ideas?

View attachment 112420

Can you upload a copy of your database?

 

[GPGeorge]

Earlier, some of you helped me overhaul my table/relationships design. The solution you offered was very elegant (thank you again!). My queries are still working well. But I have been stymied by how to create an associated data entry field on the user input form. I tried creating a combo box, and then a list box, in the form design, to populate the AllALWRIAuthors field shown below. But this doesn't seem quite right. For one thing, the records in form view are displaying incorrect data--almost like the field contains a legacy of the last record. Luckily, so far, it doesn't appear as if it's overwritten any of my existing data. In general, it seems like MVFs are frowned upon, and so I have a feeling I should somehow be storing these values (AllALWRIAuthors) in the junction table I've already created (and so eliminate this field here). But I'm confused as how exactly to do this. And also, maybe worse, what question exactly to ask google. Any ideas?

View attachment 112420

Here's the ERD from a library catalog database that I created and manage for a non-profit. The relevant part is outlined in green. It illustrates the point Tom made about many-to-many relationships. Note that your "Manuscript" would correspond to my "Publication".

Other parts are not relevant to your scenario, of course.

 

[Missomissou]

@GPGeorge Here's a screenshot of the relationship--it's a bit different in that there is a junction table for the many possible authors to one policy review, and a direct relationship for the one point of contact per policy review.

 

[mike60smart]

@tvanstiphout hi--I followed your instructions above and that *almost* worked! I added a third column, FirstName. The dropdown arrow appears under ResearcherID, as it should according to these instructions--though in retrospect, the ResearcherID is irrelevant to the user, so that field could be dropped from the subform. The biggest issue with the subform, is that when I run it, there is no information in that ResearcherID dropdown. On an existing record, the correct author's name appears, but when I go into editing mode, there are no other options to choose from. When I add a new record, there are no options at all in the dropdown. Here's a screenshot:

View attachment 112426

@GPGeorge Yes, that's the design I am using (thanks again to this group!).
@mike60smart I'll upload the DB for you. Let me know if you see anything glaringly awry!

Hi
Your relationship should be as shown.

 

[mike60smart]

@mike60smart I'm confused. That appears to be the way that I have it set up (other than in my junction table, the PolicyReviewID and ResearcherID are both the primary key.

View attachment 112433

Hi
ResearcherID should be removed from the PrimaryTablePolicyReviews together with the Join to your ListALWRIStaff table.

I was always taught to have the Primary Key set as it is in my example.

 

[mike60smart]

@mike60smart The ResearcherID field in the policy review table is meant to capture the point of contact, in this case, the person who submitted the article for review. Is there a better way to capture this information, enable the end user to select from a list of choices, rather than typing in the name themselves? Here's a screenshot of the table design:

View attachment 112434

So the name of the field should be SubmittedByID and then on the Form you would use a Combobox to select whichever table contains
the list of people who would submit Policy Reviews.

The other structure was to capture the Multiple Researchers

 

[mike60smart]

Thanks again @mike60smart ! That is all working ok--there is a direct one to many relationship between the list of staff and the policy review table. It's the many to many (which is really many to one) relationship with the junction table that I'm just banging my head against. I followed @tvanstiphout 's instructions above and they almost seemed to work, but the subform/combo box isn't working (I tried both a combo and a list box). The syntax seems ok. But though there is a drop down list of ID numbers, they aren't associated with names, so I don't know which belongs to whom; I can't select any of the items from the drop down list in the ResearcherID field; and of course here I may need to select more than one. Incidentally, I have set the Multi Select control to "Simple".

Hi
What M:M relationship are we talking about here?

All we did was allow a Policy Review to be related to Many Researchers.

 

[Missomissou]

Note: regarding my above comment, "But though there is a drop down list of ID numbers, they aren't associated with names, so I don't know which belongs to whom"--I sorted that out. It's working ok now. I also locked down the last name/first name fields so that names can't be typed in manually. However, I still can't select from the dropdown list.

 

[mike60smart]

Note: regarding my above comment, "But though there is a drop down list of ID numbers, they aren't associated with names, so I don't know which belongs to whom"--I sorted that out. It's working ok now. I also locked down the last name/first name fields so that names can't be typed in manually. However, I still can't select from the dropdown list.

Upload your database again please

 

[mike60smart]

Thanks so much for considering this. I'm afraid I am confusing us both. My original post above included "But I have been stymied by how to create an associated data entry field on the user input form. I tried creating a combo box, and then a list box, in the form design, to populate the AllALWRIAuthors field shown below." In the Reviews table, there are two fields, one for the submitting author, and another for all the authors in our organization who are involved on a given project. The first of these, the submitting author field, is a single value field, is associated with a combo box on the form through which an end user can populate the field in the table. The second field, meant to capture all the authors involved in a given project, is the one that is giving me issues here. I am trying to find a way for the end user to select multiple authors via a form control, which would populate the associated multivalue field in the table.

You should NOT be using MVF

 

[mike60smart]

Thanks so much for considering this. I'm afraid I am confusing us both. My original post above included "But I have been stymied by how to create an associated data entry field on the user input form. I tried creating a combo box, and then a list box, in the form design, to populate the AllALWRIAuthors field shown below." In the Reviews table, there are two fields, one for the submitting author, and another for all the authors in our organization who are involved on a given project. The first of these, the submitting author field, is a single value field, is associated with a combo box on the form through which an end user can populate the field in the table. The second field, meant to capture all the authors involved in a given project, is the one that is giving me issues here. I am trying to find a way for the end user to select multiple authors via a form control, which would populate the associated multivalue field in the table.

The table shown in the attached should NOT be using MVF

 

[GPGeorge]

Did you look closely at the screenshot I gave you? It illustrates the many-to-many relationships needed for authors as well as for categories.

I did call them "publications" rather than "books", but that shouldn't be a problem in understanding the concept.

 

[GPGeorge]

@mike60smart I'm confused. That appears to be the way that I have it set up (other than in my junction table, the PolicyReviewID and ResearcherID are both the primary key.

View attachment 112433

Sorry, apparently I missed this post in this lengthy string.

Sorry, but this is not properly set up.

There should not be a field for "ResearcherID" in table "PrimaryTablePolicyReviews". The many-to-many relationship is handled in the Junction table only.

 

[Missomissou]

@GPGeorge I did! Did you not see my response to you? I included a screenshot for you to see. I think mine is set up similarly.

 

[Missomissou]

Sorry, apparently I missed this post in this lengthy string.
View attachment 112437
Sorry, but this is not properly set up.

There should not be a field for "ResearcherID" in table "PrimaryTablePolicyReviews". The many-to-many relationship is handled in the Junction table only.

Ok--I think there was some confusion because there are two fields in the PrimaryTablePolicyReviews table that draw information from the ListALWRIStaff table. The first is the ResearcherID field. That one is currently fine. It is serving its purpose in the table and it works fine in the query/report. The one that has been giving me trouble is the AllALWRIAuthors field, which I had set up as a MVF. I've since moved all that data over to the junction table and removed that field from the Reviews table. But I'm still struggling with how to set up the form (which is what started this very lengthy thread in the first place). Again, I so appreciate the time and thought you are giving to this issue. I'm otherwise working in a vacuum here, with naught but Google as my guide.

 

[GPGeorge]

Ok--I think there was some confusion because there are two fields in the PrimaryTablePolicyReviews table that draw information from the ListALWRIStaff table. The first is the ResearcherID field. That one is currently fine. It is serving its purpose in the table and it works fine in the query/report. The one that has been giving me trouble is the AllALWRIAuthors field, which I had set up as a MVF. I've since moved all that data over to the junction table and removed that field from the Reviews table. But I'm still struggling with how to set up the form (which is what started this very lengthy thread in the first place). Again, I so appreciate the time and thought you are giving to this issue. I'm otherwise working in a vacuum here, with naught but Google as my guide.

Is a "Researcher" the same entity as a "Reviewer"? Is it always the case that a single "Researcher" is responsible for each Policy Review?

The answers to those questions will determine what the table design needs to be.

 

[Missomissou]

Is a "Researcher" the same entity as a "Reviewer"? Is it always the case that a single "Researcher" is responsible for each Policy Review?

The answers to those questions will determine what the table design needs to be.

Probably just easier to do a screen shot of the table design, rather than try to explain this one in words. This is the table with the AllALWRIAuthors field removed (moved into a new junction table connected to ALWRIStaff.). I am trying to figure out how to create an input form that will allow the end users to select multiple authors from a list. The form

Probably unhelpful, but the form currently looks like this:

The hole under Publication Date was the spot that the AllALWRIAuthors field occupied. It was very nice to have a lookup field in the table--it translated seamlessly to the form, allowing the user to select multiple authors from a drop down menu. But now that I've eliminated the lookup field and shifted the data from a multivalue field to a junction table, in the form above there is just an blank space which might as well signify the black hole that it has become for my time today. Sorry to waste yours with this as well. But also thank you. :-(

 

[Missomissou]

Side note: I created a subform from a query that will at least display information for records already entered.

Then inspired I created a subform with PRID from the junction table and names from the staff table.

I expected the query just to act as a report of all internal authors for each review, but it appears the subform I created is also read-only. When I try to input data there (by creating a new record, for example), I get an error message, "Cannot add record(s); join key of table 'TblJnctPolRevIDResIDAllALWRIAuthors' not in recordset" (yes, I know that table name is ridiculous.). Incidentally, in spite of this error message, the new record is added to the Policy Review Table, but not to the junction table).

This seems so close, but I'm not sure what to change to get the right setup.

Ideally, for starters, with each new record (both on the parent and child forms), I would want the PolicyReviewID field/s on the subform to autopopulate based on the associated master field; and then the names to be selectable from the dropdown list (which appears with the last name for each row/record on the subform, as it should). If this is the optimal format here, I don't think it wouldn't be too much to ask our staff to advance to the next line on the subform to enter/select an additional author, rather than as before (with a lookup field in the table (long since deleted) checking them all off at once. Anyway, I may be shooting in the dark, but at least this feels like a step closer?