Access complains of duplicate key

[David H.]

Our Access 2000 database (a front end to MSSQL2000 data) has two main tables
with a 1-1 relationship. Call them Client and Assessment. (Why? Mostly
just too many fields to fit in a single table.)
The main data entry form has lots of tabs; the first two tabs look at data
in Client, other tabs use both tables.
Users create a new record using a form which automatically creates records
in both, with the same primary key value (ClientID) in each table. The "New
Client" form then closes and opens the main form, and selects the new client.
They then start entering and saving data happily on tabs 1 and 2. But if
they continue to tab #3 (without closing and reopening that record) and try
to enter more data, they often get this error:
"ODBC call failed. Violation of Primary Key Constraint (PK_Assessment)
Cannot insert duplicate key in Object 'Assessment'." Any ideas why SQL
thinks I'm trying to insert a duplicate key? Any ideas (other than merging
two tables into one) for how to fix it? Thanks!

 

[Jeff Boyce]

David

Having "too many fields to fit in a single table" is rarely a good reason
for just adding another table. Access is a relational database, so if your
data isn't well-normalized, you will not get the best (or easy) use of
Access' relationally-oriented functions and features.

If you have a table named "Assessment", I'll take a wild guess that there
are MULTIPLE assessments, stored in columns. This is how you would have to
organize your data ... if you were using a spreadsheet!

In a well-normalized relational database, you'd have Client-related
information, then you'd have a one-to-many relationship to
Assessment-related information.

Or maybe I'm reading way too much into your description. Since everything
you'll do in Access depends on your data, how 'bout posting back with a
brief description of some of the fields you are using in your current two
tables...


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[David H.]

Actually, you WOULD think there would be multiple assessments per client, and
I think perhaps long ago when we began designing this database there were
going to be... but no. There really is only one assessment per client.

And in fact we have over 200 fields in the Assessment table; if we combined
it with Client we would exceed the 255 fields/table limit that I see in the
Access help file.

I'm not sure if it will help, but the "Assessment" fields include date
fields (e.g. date of assessment), text fields of varying sizes, sume numberic
fields that we link to triple-state checkboxes (yes. no, we haven't found out
yet), and a few memo fields. Of course there are many other tables, like
"Contacts" or "Physicians" or "Dated Notes" which have a many-to-one
relationship to Client; these populate subforms within the main form.

I do understand that 1-1 relationships between tables are not "normal", but
they are not forbidden; there must be a reliable way to add a record to both
tables with the same index, without causing Access to have hiccups when we
edit them.

Thanks!

 

[KARL DEWEY]

Why must you have two primary keys?

Why not just one, relate them one-to-many referential integery, and set the
second table field indexed, no duplicates?

OR have two primary and have another field in second table that is related
one-to-many referential integery, and set the field indexed, no duplicates?