Form Design

[muriel]

I'm designing data entry forms and have a few many to many relationships that I've broken down into one to many relationships with join tables. The type of data entered will be bibliographic data.

Example of tables (to name a few)

Author_tbl
AuthorID (primary key)
AuthorName

Organization_tbl
OrganizationID (primary key)
OrganizationName

AuthorOrganization_tbl
AuthorID (primary key)
OrganizationID (primary key)

I've designed forms with subforms to accept the one to many relationships. Question - since one article can have up to 4 authors, who in turn can have 4 separate organizations, how do I allow for data entry people to put in all the data? Should I have Author1, Author2, Author3, Author4 as fields in my Author table, and the same for Organizations? I'm seeing a potential problem in the AuthorOrganization_tbl which holds the values for the entries - how will the table keep track of which record the authors and orgs belong to?

I hope this makes sense. I've received a lot of great advice from this forum, and I really appreciate it. I apologize to anyone who is getting tired of my posts, but I'm really trying to figure it out myself before I post.

 

[GKProgrammer]

Hi Muriel,

I could see you creating an Author1 - 4 if the table was for the article details but these tables appear to be an Author and Orginization list, with the AuthorOrganization_tbl correlating the two together. I am not sure what the problem is you are seeing, the way you have it listed above appears to be good to me. I could see you adding an Article table and an ArticleAuthor table to correlate the authors that will be responsible for each article.

Regards,
gkprogrammer

 

[muriel]

Maybe I'm making this more difficult than it is - believe me, that would be a relief.

The 3 tables I listed above are just part of the table design. I also have an articles table, publication table and keywords table. Authors and publication tables are many to many; publication and organization tables are many to many; and keywords ans articles are many to many.

I'm trying to figure out how to set this up so that users can enter data and it will all be linked to the same record. Each article can have up to 4 authors (with potentially each having a different, separate organization).

Here's where I'm seeing the problem: I have the articles table set up as the main form. It contains name of article title, etc. Then I have the author table, with the organization subform. The users will enter the data on the articles form first, then open up the authors table and type in the author and choose the organization from a combo box. Now, if there's another author, where do they enter it? Do I need 4 author fields there?

Does that make sense?

 

[muriel]

Correction to my most recent post:

Here's where I'm seeing the problem: I have the articles table set up as the main form. It contains name of article title, etc. Then I have the author table, with the organization subform. The users will enter the data on the articles form first, then open up the authors FORM and type in the author and choose the organization from a combo box. Now, if there's another author, where do they enter it? Do I need 4 author fields there?

Another question - how would the join table store the ID numbers and keep them all straight? Would I need AuthorID1, AuthorID2, etc?

 

[GKProgrammer]

Hi Muriel,

Yes, if you have a possiblity of 4 different inputs than you would obviously need a way for the user to enter these seperatly. How you handle this will be your personal preference on appearance for your form but using four seperate fields is a viable option.

In your orignial post you displayed how one author can be associated to many orginizations, this logic could also be applied to one article being associated to many authors. I would suggest that you could do something similar and have (like I sugggested above) an ArticleAuthor table that would store the ArticleID and the AuthorID which would end up being very similar to the AuthorOrginization_tbl. Hope this helps.

Regards,
gkprogrammer

 

[muriel]

Thanks - that does help. I really appreciate your advice.

So would I also need to have the four separate fields in my join tables. Eg: AuthorID1, AuthorID2, AuthorID3, AuthorID4, OrganizationID1, OrganizationID2, OrganizationID3, OrganizationID4?

 

[GKProgrammer]

You should only need one Author and one Organization field for the respective tables. Each Author would be treated as a separate record. As an example this could be what a set of records could look like under the ArticleAuthor table if four authors were entered for the same article:

ArticleAuthorID AuthorID ArticleID
1 2 5
2 3 5
3 4 5
4 5 5

You could set ArticleAuthorID to be an AutoNumber and then the AuthorID and ArticleID will be primary keys.




Regards,
gkprogrammer

 

[GKProgrammer]

In the above post the three columns of numbers should be aligned with the three columns of text. When I submitted the post they were but it appears that the formatting has been altered a bit, anyway I am sure you get the gist of it.

Regards,
gkprogrammer

 

[muriel]

Thanks for your help - much appreciated.