Linking and entering data

[NielsE]

I am building a new DB for humanitarian projects. I will create a main
tablerojects that will have a unique key for each project. it will contain
fields like title, region, budget, etc.
The projects can deal with more than one subject each, eg. child labour,
migrants, etc. These subjects will be in a separate table, also with a unique
key. Same for target groups, each project can have several targets groups and
they will be in a separate table with unique key as well.
My first question is how do I link the tables? Do I need to have a link
table between the project table and each of the tables containing the
variables?
Second question, I want the user to be able to enter all relevant data for
each project through a form. It is easy with the values in the main project
table. But can I enter, for example, two, three or four different subjects
for each project from a multi-select list? How and where to do I direct the
values? Or is there a another way to enter these multi values?
Thanks in advance. Niels

 

[Allen Browne]

Niels, this is a very big question. I can only take you the very first step
of the journey, which will probably last about 2 years if this is your first
project and you are doing it part time.

One project can relate to many subjects. One subject can be studied in many
projects. There is therefore a many-to-many relationship between projects
and subjects. The standard way to resolve this is to create a 3rd table that
contains the connections between the other two, like this:

Project Table (one record for each project):
ProjectID AutoNumber primary key
ProjectName Text indexed, unique?
RegionID foreign key to Region.RegionID
...

Subject table (one record for each subject):
SubjectID SubjectID AutoNum
SubjectName Text
...

ProjectSubject table (one record for each connection):
ProjectID Number (Long) foreign key to Project.ProjectID
SubjectID Number (Long) foreign key to Subject.SubjectID
So, if project 72 is studing 3 different subjects, there will be 3 records
in this table that contain 72 in the ProjectID column.

For another example of using this "junction" table to resolve the
many-to-many into a pair of one-to-many relations, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

Well, that's the first (and most crucial!) step. There will be heaps of
other tables, as well, e.g. a table of regions. Personally I use a
text-based key for these little lookup tables, e.g.:
Region table:
RegionID Text (24) primary key
Use an AutoNumber if you prefer

Beyond that, you will need to consider which attributes of the different
subjects overlap to decide whether you can create a table that copes with
all the attributes of the subjects, or whether you need to group to subjects
into subsets of related tables that handle the attributes specific to each
subject grouping.

Once the data structure is in place, you can begin to consider how to design
the interface to handle that structure--*never* the other way around. In
general, you will use subforms to enter multiple related records into the
related tables. Multi-select list boxes are not suitable for binding to
data.

 

[NielsE]

Thanks Allen, this was very helpful. I do not hope it will take me two years
to finish my data base . Actually I used access2 way back, but I am afraid
I have to learn most tricks again.
I have now established the following tables:

tblProjects
-ProjectID, autonumber
-ProjectSymbol, text
-Title, text
-Region, lookup value list
-Status, lookup value list
-StatusDate, date
-Budget, currency
-Donor, text

tblSubjects (fourteen records)
-SubjectID, autonumber
-SubjectName, text

tblTargetGroups (ten records)
-TargetGrID, autonumber
-TargetGrName, text

tblCountry (countries updated when not in list)
-CountryID, autonumber
-Country, text

I need to link the last three tables somehow to the main tblProjects and
should have a many-to-many relatioship. The last three tables do not
necessarily need to relate to each other.

I need to enter data for each project record from a form. My problem is not
how to fill the fiields in the main tblProjects, since each record will have
only one value. If I can figure out how to relate the tblProjects and
tblSubjects, for example, I think I can repeat it for the other tables. This
means how I make relationship between tblProjects and tblSubjects (multiple
choices) and how I can enter data from a form (and possible sub-forms) and
how to bind the form entries to which tables and fields.
Maybe it a lot to ask for, but any help will be greatly appreciated.
Niels

 

[Allen Browne]

Okay, that's great. If you have some Access experience, then you may already
be aware of normalization techniques, and hopefully this will all come back
to you.

Once you have created the ProjectSubject table as suggested, you will have a
main form bound to the Project table, and a subform bound to the
ProjectSubject junction table. The continuous subform will have a combo box,
so you can select as many subject as you need on successive rows in the
subform.

If you have a many-to-many between target groups and projects, you will also
need a junction table named (say) tblProjectTargetgroup, with fields:
ProjectID which project relates to this group;
TargetGrID which target group this project relates to.

This junction table will be another subform on the Project form. You might
decide to use a tab control, where the main project data is in the first tab
page, and the other subforms are in subsequent tab pages.

You might find that a ProjectCountry junction table is appropriate. Or, you
might discover that the country is actual an attribute of something else,
e.g. "project A handles subject B in country C, and subject D in country E,
and ..." in which case the CountryID would become part of the ProjectSubject
table. I have no idea how the data should be related: just raising that as a
possibility.

BTW, really minor point, but I personally use the country name as the
primary key in those little lookup tables, rather than introduce an
artificial (AutoNumber) key. I only do that in very simple tables, but it
has a couple of advantages:
a) You can actually use NotInList to add values, as described here:
http://allenbrowne.com/ser-27.html

b) You don't lose the visual presentation of the data if you filter the
combo (since the bound column is visible.)

 

[NielsE]

Thanks Allen. I will start to pull it together. Hope i can come back if I
need further assistance as I advance.
Niels