Cracking a relationgship-nut

[Boblebad]

Hi

I have a database for registering every thing about contact letters send out by a writer, i have a lot of tables and are having trouble remembering how to relate them to each other, so i can build a form and do searches.

This table is for things related to the contact letter:

Tabel for kontaktbrevet - tbl_DK_Kontaktbreve
Letter year- sub table (1941, 1942, 1943 aso.)
Letter number
Letter date
Number of pages
Physical papers
Year / Volume - sub table (1. - 2. - 3. aso.)
Letters in a year - sub table
Pages in a year
Letter numbering in the year
Envelope stamp
Notes



This is the table for all the info regarding the articles in the contact letter:

Tabel for artikler - tbl_DK_Artikler
Article titel
Article type - sub table
Article category - sub table
Article writer - sub table
Number of article pages
Number of physical papers
Written on both sides of the paper yes/no
Page numbering this issue
Page numbering continuing
Written in country and city - sub table


Article notes



Some of the articles are written down lectures, this is the table regarding the lectures:

Tabel for foredragsinformation - tbl_DK_Foredragsinformation
Lecture date
Lecture place- sub table
Processed for writing by - sub table
Approved by Martinus (yes/no)
Approval notes
Lecture notes
Manuscript (yes/no)
Manuscript notes



This table is for what we have stored in the archive, and as so relates directly to the contact letters:

Tabel for arkiv-indhold - tbl_DK_Arkiv_Indhold
A-archive (original/fotocopi)
B-archive (original/fotocopi)
”The yellow archive” (original/fotocopi)
Archive notes



This table is for information on who is registering the data, and who is changing it:

Tabel for registreringsoplysninger - tbl_DK_Registrering
Registrered by
Registrering date
Change date
Changed by
Changing notes


My problem is i don't know what table to put in the "center" and relate the others to. The first one i thought would be the main table and the main form, with the others as sub tables and sub forms, but i does not work, cause i get out in third one-many relation in a row/string, and that does not work, so i think, does this mean that one needs to be in the "center" and then be set up as a many-to-many relation ?

Most of the tables has sub tables, and i think that's where i get lost, and need to have a "center" table with many-to-many relations.

It is so that the contact letters is actually severel letters/articles/poems/illustrtions aso., so one contact letter has real letter with information on day to day business, then an article, and that article can be a written lecture. Then there's some more "articles".

I need to register all physical things as weel, as to how many pages, page numbers, date aso.

I don't know if this makes any sence, i hope it does but if not, ask and i'll try to explain it better.

All the best
Carsten, Denmark

[ranman256]

You dont have to relationship EVERY table. Its good for 1 to many , parent / child tables, but they DONT have to be.

You can still make queries and data updates without the relationships. Its really to prevent people from deleting parents, and leaving orphan children.
I NEVER USE THEM. (almost) Because nobody can delete records, so no orphans can happen.

They tend to be more of a headache to maintain, or alter tables.

Just relate your basic needs.

[Boblebad]

Okay, maybe i don't understand it fully, but how would i then assure that the articles are assigned to the right contact letter, and as well for all other information from the other tables ?

As i understand it, it's per ID, primary and sekundary keys.

[June7]

Don't interact directly with tables and queries. Use form/subform arrangements. Master/Child links properties of the subform container control will automatically save PK of main form as FK into dependent record of subform.

[ssanfu]

And if you are having problems with defining PK fields and FK fields, here is a refresher:
http://office.microsoft.com/en-us/ac...in=HA010341462

[Boblebad]

Sorry for the late reply, didn't get a notification on the replies.

I am working with forms/sub-forms, but something is not as it should be, cause some of the sub-forms does not show the data when flipping forth and back, the data is in the table in the database, they have not bin deleted, but they are not related correctly so the sub-form shows up empty.

Any idea about that ?

I'll take a look at the link, see if it's better than the ones i have found myself

[June7]

If subform not working right then something is probably not right about PK/FK.

If you want to provide db for analysis, follow instructions at bottom of my post.