One to One Relationships with autonumber

[Frank]

I have a database with three tables that are One to One relationship. I have
a Primary Field in each table named EventInfo_ID. I have set the first
EventInfo_ID Field to Identity Yes, Identity Increment 1 and Identity Speed
1. I then set a relationship with the three Tables by the EventInfo_ID
fields.

This works well when I run my form with an Access database but not when I
run the same form with a SQL database. The last two tables do not complete

Any Suggestions, what I need is for the first table to send the EventInfo_ID
number over to the other two tables when a record is completed?

Thank you

 

[John W. Vinson]

I have a database with three tables that are One to One relationship. I have
a Primary Field in each table named EventInfo_ID. I have set the first
EventInfo_ID Field to Identity Yes, Identity Increment 1 and Identity Speed
1. I then set a relationship with the three Tables by the EventInfo_ID
fields.

This works well when I run my form with an Access database but not when I
run the same form with a SQL database. The last two tables do not complete

Any Suggestions, what I need is for the first table to send the EventInfo_ID
number over to the other two tables when a record is completed?

Thank you

The "child" tables - even a one to one relationship has directionality - will
not automagically fill in when a record is created in the parent table, either
in SQL or in Access/JET. How are you populating the tables in Access? With a
Form and Subform, using the parent EventInfo_ID as the Master and Child? That
should also work if the tables are in SQL.

Just note that one to one relationships are VERY rarely necessary. If you're
not Subclassing, Superclassing or using Table-based field level security, your
table design may need reconsideration. If you're using one to one
relationships to get around the 255 field limit, you almost *certainly* need
to reconsider your table structure - even 50 fields is an enormously wide
table, and 500 plus is simply wrong.

John W. Vinson [MVP]

 

[Frank]

John,

All three tables do contain the same record rows with different fields,
should I just combine into one table with 65 fields? I thought that this may
be too many fields in one table.

Thank you

 

[John W. Vinson]

John,

All three tables do contain the same record rows with different fields,
should I just combine into one table with 65 fields? I thought that this may
be too many fields in one table.

255 fields is "too many" for Access; 65 is too many for lots of relational
purists, but it's legal and I'll confess to having a couple of tables that big
in my apps. It's certainly MUCH less work to manage the data if it's all in
one table.

If you have some one-to-many relationships embedded within these 65 fields, by
all means split them out; telltale signs are data embedded in fieldnames
("January", "February" etc. as part of the fieldname for example), or fields
with 1, 2, 3 suffixes.

John W. Vinson [MVP]

 

[Armen Stein]

255 fields is "too many" for Access; 65 is too many for lots of relational
purists, but it's legal and I'll confess to having a couple of tables that big
in my apps. It's certainly MUCH less work to manage the data if it's all in
one table.

If you have some one-to-many relationships embedded within these 65 fields, by
all means split them out; telltale signs are data embedded in fieldnames
("January", "February" etc. as part of the fieldname for example), or fields
with 1, 2, 3 suffixes.

John W. Vinson [MVP]

We've designed hundreds of databases over many years. I don't think
we've EVER had 65 fields in one table. As John mentioned, any
date-specific or numbered columns shouldn't be there. And you might
want to look for lists of "attributes", like a series of checkboxes or
values associated with each record. What happens when you want to
start storing a new attribute? These can often be more flexibly
stored in a child table of attribute values.

One quick test for database design is this: Can the structure remain
unchanged and still handle normal business operation over time? If
the answer is no, then it is not well normalized.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John W. Vinson]

All three tables do contain the same record rows with different fields,
should I just combine into one table with 65 fields? I thought that this may
be too many fields in one table.

Just to follow up... if you would like, you could post the names of ten or
twenty fields and see if someone can make a suggestion.

John W. Vinson [MVP]