Db design problems and relationships

[Patrick]

I am having trouble trying to model a database and hope
that someone can help me here. I'm new at this and still
learning.

I have a design that involves what I believe needs are
several tables. One is a main PROJECT table, an
ALTERNATIVES table and (x) number of date tables that
describe different date activities (these are differing
dates for any given activity). I decided to use several
smaller tables that involve differing date-related
activities. I am trying to learn to design tables that do
not have lots of blank fields and overhead low.

Sooo...ONE Project table with unique Projects, any Project
may have MANY Alternatives (1 -> Many Yes?) and each
Alternative may have an entry in one, some or all of the
supporting Date activity tables. So, I have inserted a
foreign key (Proj_ID from tblPROJECTS) in the Alternatives
table that points back to the Project table. So far, so
good. I also assume that each date table would want a
foreign key (Alt_ID from tblALTERNATIVES) from the
Alternatives table to point back to it also.

Problem is that when I plugged in some dummy data in the
tables and setup a query based on all of this, I could not
change or modify any of the text (Comments, Names, etc.)
in resulting query.

Where am i messing up?

Thanks in advance for those that can teach the less
informed!

PAtrick

 

[iain]

i think the sort of thing you need to do is :

have your main projects table, with say Project ID as your
key. and for each Alternative table, have your Activity
ID, or Alternative ID as your key, both as Autonumbers.

then create an Interlinking Table to link the Projects
table with each Alternative Table. in each interlinking
table have two fields, with the same name as the keys from
the other two tables - ie Project ID and Alternative ID,
both as numbers.

Then create a query with the Projects Table, Alternative
Table and Interlinking Table. Add in the two fields from
the Interlinking Table, plus the fields you want to
include from the Projects and Alternative Tables (but not
the keys, ie Project ID or Alternative ID).

Or something like that - hopefully someone who knows more
about it might be able to explainit better.

Hope this helps,
Iain