Connect one table to multiple fields in the same table

[Peter Stone]

Novice/Access 2003

How can I connect one table to two or more fields in the same table in a
one-to-many relationship?

Example tblWriters to fields: Writer1, Writer2, & Writer3.

I want to record who updated some text and several writers could have
contributed to the same text.

I've joined tblWriters to Writer1, tblWriters_1 to Writer2 , and
tblWriters_2 to Writer3. This updates the fields successfully, but when I
check the table and click the + sign to look at the attached records, there's
nothing there.

I've searched, but can't find any reference to this. Does it need to be done
by a query?

Thank you

Peter Stone

 

[Wayne-I-M]

Hi

It is a good idea to create a query 1st with just tth information you want
on your subdatasheet

Then just open the table and click insert

Select subdatasheet and select the one you want from the list (select the
query you have just made)

Of course you "can" choose to have a whole table as the subdatasheet "but"
that may be bit a big big for what you want.

Give it a try. You can always delete it after if you dont like it.

 

[Ken Sheridan]

Peter:

Your problem results from a basic design flaw in your databse. You should
not have multiple Writer fields, but just the one Writer field in the Writers
table and one row per writer. Better still would be to have separate
FirstName, LastName fields etc. as its easy to join field values together,
but not so easy to split them if required. The table should have a numeric
primary key column, e.g. WriterID (which can be an autonumber) as names can
be duplicated and consequently don't make good keys (I have written technical
articles, but I once came across another Ken Sheridan who wrote a guide book
to the city of Buffalo).

Your Texts records should be in another table, Texts say with one row per
text and once again a TextID numeric primary key field.

In database terms the updating of texts is a relationship type between Texts
and Writers. There is a many-to-many relationship between them as each text
can be updated by one or more writer, and presumably each writer can update
one or more texts. In a relational database this type of relationship is
modelled by a third table, Updates say, with two foreign key fields TextID
and WriterID which reference the primary keys of Texts and Writers. In this
Updates table these field should not be autonumbers however, but
straightforward long integer number data types. This table will probably
have other fields representing attributes of the Updates, e.g. UpdateDate.

Data entry for this type of relationship would normally be via a form based
on the Texts table with a subform within it based on the Updates table, the
two being linked on TextID. To select a writer who updates a text the
subform would have a combo box bound to the WriterID field, but showing the
writer names looked up from the Writers table (the combo box wizard can set
this up for you or I can walk you through setting it up manually). The
subform would also have controls bound to any other fields in the Updates
table such as UpdateDate. For each writer who updates a text its simply a
case of adding a new row to the Updates table via the subform.

For an example of this sort of scenario take a look at the Orders form in
the sample Northwind database. This has an Order Details subform in which
rows can be entered into the Order Details table, which models the
many-to-many relationship type between Orders and Products.

Ken Sheridan
Stafford, England

 

[Peter Stone]

Ken you may not come back to this after so long. But: I had several questions
running at this time and missed the notification. Just now, I found your
reply on Google groups. You answered my question perfectly and at length and
solved my problem.

Thanks very much

Peter

 

[Ken Sheridan]

Peter:

Thanks for the feedback. Glad to have helped.

Ken Sheridan,
Stafford England