Is Referential Integrity the problem, if not then...?

[Mercadogs]

Hello:

I'm trying to add records to a table I'll call the "main" table. This table
has numberical foriegn keys of "sub" tables (which contain the data in "text
form"). I'm using a form to add the records to the "main" table, but I
continue to duplicate "text data" in the "sub" tables. In other words, if I
have a table for "Item Names", item name "Valve", then this "text data" would
have two primary keys if I try to add a new record for an item with this
name. If I change the Index of these "sub" table fields in design view to
"Yes (No duplicates)", then I am not able to add new records at all.

I have "Referential Integrity" and options "cascade delete" and "cascade
updates" checked. The join type is option 1 (Only include rows where the
joined fields from both tables are equal). Could any of this be my problem?
If not, then...?

For clarification, the relationships are one-to-many. The "main table" is
the one-sided table which links to all the other tables, about 20 tables in
total.

Thanks for your help,
Gabriel

 

[Pieter Wijnen]

Sounds to me you're doing it backwards
The idea is that the MainTable should hold the "Text Data"
With SubTables holding n records
For example

TABLE Order
Field OrderID PK
Field CustID FK
Field OrderDate
etc

TABLE OrderDetail
Field OrderDetailID PK
Field OrderID FK
Field ItemID FK
Field Qty
etc

HTH

Pieter