Newbie struggling with inputting data via subforms

[Matilda]

Anticipating sighs of exasperation. Clearly I'm another person who thought designing a simple database with no programming skills would be easy but please help me!



Am trying to design a simple archiving database. Each "shelf item" has a unique number but can have many authors, keywords or "articles" (if the shelf item is a journal). I've largely got these relationships to work in terms of the searching but when it comes to having one page, with subforms, to input these one to many relationships I've clearly got something wrong. New authors entered through the subform, for instance, will be listed in the Authors table but the link between new authors and the shelf item ID is not registered in the Author Match table.

http://dl.dropbox.com/u/65557234/Matilda.mdb

In the end I took the subforms out and started constructing a very inelegant way around it, as you can see (under Input New Shelf Item), but this is clearly a nonsense and is sure to make you all laugh very hard.

I feel I'm missing something very obvious. Please let it be something simple and obvious and something my little noodle can manage! Any and all help would be very gratefully received!

Mattie

[Jester0001]

I noticed that you don't really have one-to-many relationships between the table [Shelf Items] and the tables [Keywords], [Articles], and [Authors]. These relationships appear to be set up as a many-to-many relationship, as designated by the intermediate tables between them: [Keyword Matches], [ArticlesShelfItemMatch], and [Author Matches].

I also noticed that there is not a primary key in these intermediate tables (sometimes called "mm" tables for "many-to-many".

I discovered that the "mm" tables ([Keyword Matches], [ArticlesShelfItemMatch], and [Author Matches]) didn't have a primary key when I created a subform and tried to link the subform with the main form.

I could be wrong, but I think that this is why you're having trouble getting the subform to link with the records on the main form.

I believe you are on the right track by implementing the many-to-many tables, but their design may need to be adjusted (make one field in each table a primary key)

Here is an article about many-to-many relationships that may help:
http://www.techrepublic.com/article/...access/5285168

After looking through this, and after assigning primary keys, you may have better luck at linking your subform(s).

By the way, nice initial design on the database! And welcome to the forums!

[Matilda]

I noticed that you don't really have one-to-many relationships between the table [Shelf Items] and the tables [Keywords], [Articles], and [Authors]. These relationships appear to be set up as a many-to-many relationship, as designated by the intermediate tables between them: [Keyword Matches], [ArticlesShelfItemMatch], and [Author Matches].

I also noticed that there is not a primary key in these intermediate tables (sometimes called "mm" tables for "many-to-many".

I discovered that the "mm" tables ([Keyword Matches], [ArticlesShelfItemMatch], and [Author Matches]) didn't have a primary key when I created a subform and tried to link the subform with the main form.

I could be wrong, but I think that this is why you're having trouble getting the subform to link with the records on the main form.

I believe you are on the right track by implementing the many-to-many tables, but their design may need to be adjusted (make one field in each table a primary key)

Here is an article about many-to-many relationships that may help:
http://www.techrepublic.com/article/...access/5285168

After looking through this, and after assigning primary keys, you may have better luck at linking your subform(s).

By the way, nice initial design on the database! And welcome to the forums!

Thank you very much for this reply. It was very helpful. I see now which are the many-to-many relationships. I don't think the joining tables can have a primary key though can they? Because of the duplicate values? I've had a good tidy up and tried following the the instructions in the article but something in the relationships is still not working. Is there anyone who can point me in the right direction. Thank you again, Jester, for your kind help.
http://dl.dropbox.com/u/65557234/Matilda.mdb

Mattie

[ssanfu]

A few tips:

For object names (field, table, query, form, report, module) use only letters, numbers and the underscore (_). NO spaces or special characters.
Use descriptive, short names : if you have 5 tables that has "ID", which table is it referring to?

Do not use lookup fields in tables.. see http://access.mvps.org/access/lookupfields.htm

It is better to relate tables on number fields (Long integers) rather than text fields.

Autonumber fields should not have a meaning except to relate tables. See http://access.mvps.org/access/general/gen0025.htm
Therefore, they shouldn't be visible on forms.

Here is a good tutorial site http://www.accessmvp.com/strive4peace/