Table Structure Commentary

[Andren]

I'm not as stupid as I thought - I am worse ...

Hmmm ... This was hard to admit.

It seams that I lack some very basic understanding of Access/database developement and the like. However - I admit it and feel SO stupid - after all good support I got from all of you.

As you know by now - I am since a long time trying to build a research litterature database containing basic info about research litterature, that I want to describe and classifye in a number of ways.

By now I have six tables:

(1) Main (Containing name of publication, name of writer/researcher etc), (2) Type (classifying whether it is a book, paper, dissertation etc and whether it is an open project or closed), (3) Area (biology, psychiatry, psychology etc etc), (4) Group (Gender questions, ethnique questions etc),(5) Problem (whether the litterature relates to any of seven predefined areas of life), (6) Research (what kind of research methods and statistical design that are used in the paryicular study). Table 1 (Main) is on the many side (as proposed by Acess) and tables 2 - 6 are "one sided".

All tables have one common autoincreament field called "ID".
In the Main-table I also have fields called "Type_ID", "Problem_ID" etc.
I have related "ID" (in tables 2 - 6) with fields in the Main table like "Type_ID" etc.

I created a tabbed page, putting fields on various tabbed pages with info relating to table 1 (Main). Then on one tabbed page I put five buttons that brings five pop-up forms up relating to info in tables 2 - 6.

Now my problems start;

Of course I want the five classifications to be related to the same reccord as I have in the Main table.

Then I read something that Mile-O-Phile (that I learned a lot from) wrote in another thread and got confused ("Put whatever comes from the one-side table on the main form. Put the fields from the many-side tables in separate subforms").

So - have I built the relationships entirely wrong, do I have basic problems with my relations (between tables that is....). Should my Main table be on the one-side and tables 2-6 on the many-side?

Questions as above arise because I

(1) get more reccords in tables 2 - 6 than I have in the Main table,
(2) In the pop-up forms I can't get the reccord ID the same as I can see in the navigation-bar on the main form.

Will any of you tell me whats wrong?

Thanks!

 

[Mile-O]

(1) Main (Containing name of publication, name of writer/researcher etc), (2) Type (classifying whether it is a book, paper, dissertation etc and whether it is an open project or closed), (3) Area (biology, psychiatry, psychology etc etc), (4) Group (Gender questions, ethnique questions etc),(5) Problem (whether the litterature relates to any of seven predefined areas of life), (6) Research (what kind of research methods and statistical design that are used in the paryicular study).

Your table structure is lacking.

tblMain is bad. You should split this into three tables. tblWriters, tblPublications, and tblWritersToPublications as you need to simulate the many-to-many relationship between authors and books (one author can write many books and one book can have many authors)

I don't know enough about the structure of the other tables to pass any commentary but if the ID from Main can be found in any of these tables then that is incorrect.

 

[Andren]

Thanks

Your table structure is lacking.

Thanks Mile-O-Phile!

Of course you were right. I adjusted the structure to Many-To-Many.
And read about Pop-Up's and suddenly it started to work.

/ Anders