Adding new record problem

[Guest]

Scenario: When adding a donor to DonorMaster table the table is updated; also
during this function I want to load, to each of the sub tables, the donor
number and the reference number. I do this by having setup a relationship
between tables (See below). Control Source for the Add Form is an embedded
query and identifying each Recovery_ID Field. The ID’s are than loaded using
VB coding Subtable_recovery_ID = DonorMaser_ID.
Works until I hit the following 2 conditions:


Condition 1:
DonorMaster DonorNumber Primary Key
Recovery ID
Additional Fields

SubTable1 ST1_DonorNumber Primary Key
ST1_Recovery_ID
Additional Fields
Tables 2-29
SubTable30 ST30_DonorNumber Key
ST30_Recovery_ID
Additional Fields

Relationship between DonorMaster and SubTables
1. 1 to 1 relationship based on Donor Number
2. Enforce referential Integrity
3. Cascade Update
4. Cascade Delete

Problem 1: Creating relationship on any table over number 27, get message
“Too many indexes on DonorMaster.â€

Question: Is there a way around this?

Condition 2:
Using embedded query has the potential to bring in huge amounts of unwanted
data.
I only want to place an empty record, except for donornumber and
recovery_id, in these tables.

Question: Can this be done?

Last Question: Should I rethink the design?

 

[Guest]

Why 29 identical tables?
I suspect your database design needs some rethinking.

 

[Guest]

Tables not identical, different medical procedures and related information to
those procedures. The two fields mentioned are the keys that associate each
of the tables.
In all there are 765 fields in these tables.

 

[Guest]

Okay, but it should not be necessary to create a table for each procedure. I
believe if you reevalute you design, you can find a way to have only one
table. Not having had any experience in medical systems, I can't offer any
specific advice, but I doubt this is actually necessary.

 

[Guest]

Dave,

Spent yesterday afternoon convincing boss, and others, we needed achange in
the design. We came a a decision that there would be three tables, Base data,
Recovery Table and a Processing Table. This way we can still maintain
security as to who has access to which table.
Thank you for your advice, it was helpful in changing some people's minds.

 

[Guest]

Good. glad to hear it.