Enforcing Referential Integrety- Autonumber composite Key

  • Thread starter Thread starter MikeS
  • Start date Start date
M

MikeS

I am having a problem with a data entry form linked to a
query. I linked two tables by making a composite key
using auto number and three other fields and enforced
referential integrety. I queried fields from both tables
and created my data entry form. Since I don't want
people to have to enter data twice, the fields on the
form for my composite key are linked to only one of the
tables (call it table 1). I thought the data in the
fields of the composite key would be cascaded from table
1 to table 2, but I am finding this happens only after at
least one piece of data is entered in a field linked to
table 2. This concerns me because even though the
cascade works, if a situation arises where there is only
composite key info and no other data the second table
will be completely empty and my queries won't work. Any
suggestions. Mike
 
I am having a problem with a data entry form linked to a
query. I linked two tables by making a composite key
using auto number and three other fields and enforced
referential integrety.
Click to expand...

This seems VERY peculiar. An Autonumber is ALREADY UNIQUE. Adding
three more fields doesn't make it any more unique!

The whole *point* of autonumbers is to provide a guaranteed unique,
short, stable key to be used for linking to a Long Integer field in
related tables.
I queried fields from both tables
and created my data entry form. Since I don't want
people to have to enter data twice, the fields on the
form for my composite key are linked to only one of the
tables (call it table 1). I thought the data in the
fields of the composite key would be cascaded from table
1 to table 2, but I am finding this happens only after at
least one piece of data is entered in a field linked to
table 2. This concerns me because even though the
cascade works, if a situation arises where there is only
composite key info and no other data the second table
will be completely empty and my queries won't work. Any
suggestions. Mike
Click to expand...

Remove the three redundant fields from your Primary Key and from the
related tables; link the tables by the Autonumber only (using a Form
with a Subform, using the Autonumber as the Master Link Field and the
Long Integer foreign key as the Child Link Field). If you need to see
the other three fields in conjunction with the child records use a
Query linking the tables.
 
Thanks, How can I make all of the data cascade from just the auto number? Can you elaborate on the subform? My tables are very wide, is there a way to set up labels and windows in a subform?
 
Thanks, How can I make all of the data cascade from just the auto number? Can you elaborate on the subform? My tables are very wide, is there a way to set up labels and windows in a subform?
Click to expand...

STOP.

You're quite a ways into the swamp of non-normalized tables here.
Stop, turn around, and come back to solid ground!

"Very wide" tables is a red flag right there. I've needed as many as
60 fields in a table - twice in the past 20 years; 10 to 15 fields is
much more common. And if you're talking about cascading data from one
table into another table using an Autonumber, you're missing the point
of relational databases altogether! They use the "Grandmother's Pantry
Principle" - "a place - ONE place! - for everything, everything in its
place". You should store information ONLY in the appropriate table,
and then link to it.

If you have not done so, get a good book on relational database
design, or connect to one of the many good websites (starting with
http://support.microsoft.com) and study up on "normalization",
"relational design", and the like. You'll find that Access works very
well when you use it as it's designed; struggling against it will get
you nothing but trouble!
 
Back
Top