I have a table of religious denominations and another table of books. Each has as its primary key an alphanumeric code for the denomination or the book. All books are associated with a particular denomination or have the "ND" code for non-denominational. Most of the denominations have more than one book associated with them. Some of the books are associated with more than one denomination, sometimes a surprising number of them.
I set up a junction table between the book and denomination tables to handle the many to many relationship. It was my first attempt at this type of thing and, to my surprise, it seems to work.
I have started on a data entry form to add books to the database. It has gone along fine until getting to this many to many situation. Is there a "standard way" to handle this when it is not know how many associations there might be ahead of time?
I have a vague concept of creating a subroutine to call a separate "add denominations" form. When that form is closed, It needs to add the new associations to the junction table and return flow to the "Add Books" form. I then would like the form to show the codes for the associated denominations along with graphic image representing the denomination. I am at a loss as to how to do this.
The only solution that occurs to my troubled mind is a brute force solution that creates plenty of blanks that can be filled in but this seems wasteful of space and, I have no doubt that if I design for 10, some odd case will show up that demands 15.
I suppose what it boils down to is amending the form at run time but my imagination tells me that is beyond me. I'm hoping y'all will know of a "standard way" to solve this situation.
I'm also hoping that similar methods can be used to generate the report when I get that far.
Thanks,