Duplicate records?

[jeffro]

Hi

I'm trying to allow duplicate records to be made within on of my tables. Well I have a primary key and a second primary key and I have set them to accept duplicates(ok). But when I go to save the table I get an error something about there being duplicates or I should define the indexes to accept duplicates. Does anyone have any idea how to stop this error?

aja

[monte96]

Sounds like you set up your primary keys to allow dupes but not your indices... take a look at the indexed fields.

[Gaffer]

A primary key cannot contain duplicates. You can have duplicates within one field of the key

Eg.

(F1 and f2 are the primary key fields, f3 isn’t)
F1 f2 f3
A b c
B b e

Duplicates in f2


But you cannot have duplicates in both fields

Eg

(F1 and f2 are the primary key fields, f3 isn’t)
F1 f2 f3
B b c
B b e


Hope this helps

[jeffro]

I do have the indexes as allowing duplicates. When I go into the indexes dialog box and try to take the unique off I get an error saying if I don't want the field to be unique I have to take the primary key off. But I don't want to do that.

aja

[paulw]

You do not seem to have caught on to Gaffer's drift.

The combination of values in a Primary Key must be unique - by definition.

Note the emphasis - you cannot do what you want - either sacrifice the Primary Key or abandon duplicates.

What are you trying to do? Maybe there is another way.

Paul.

[jeffro]

Ok I have got the primary keys to take duplicates. I just add an ID field and also made that a primary key.

Now when I try to make relationships between tables I get an error saying Data in TableMachineMaintenance violates referential integrity rules. Something about editing the data in the records so that records in the primary table exist for all related records.

any ideas?

aja

[paulw]

Jeffro,

Are you listening? Do you understand Referential Integrity - I think not. Read my last post and go back to your basic database design - It ain't right! Duplicate combinations and Primary Keys DO NOT MIX.

Paul.

[jeffro]

I'm sorry Pualw, but I did not see you previous post before I replied!! You don't need to be rude about it.

What I'm did is imported tables from an old database into the new one I have created. Well when I changed some of the names and primary keys to fit the new way the user would like to see the data, that's when I started getting the errors. See I didn't want to retype all the data into the new database.

aja

[Gaffer]

Jeffro,

I sympathise in having to adapt databases written by others into your own,
Especially if they are poorly set up.

But you must think of your strategy before you start re-assigning keys etc.
If the data you have had to import already had a primary key set up, it is likely that the designer
set the key up for a reason (e.g. because it works!) If that key does not fit into the structure of your database,
then I have always discarded the primary key.

If that database has got duplicate values in the fields you want to key, then find another field that makes the record unique.

Anyone else got any tips?

Good luck by the way

[jeffro]

Thank Gaffer,

Once I imported the tables from the old database, I did delete the primary key they had and put my own in it's place. And I did find a way to make the record unique and allow duplicate data.

The problem is when I go to make a relationship. I get an error saying Data in TableMachineMaintenance violates referential integrity rules. Something about editing the data in the records so that records in the primary table exist for all related records.

Thanks for your help. Maybe there's another way of doing this proccess.

aja

[paulw]

Jeffro,

I wasn't being rude really (you'd know if I was!) BUT you really do need to think about your database design I am serious about you not being clear on Referential Integrity and Primary Keys - you will spend a lot of time going round in circles if you do not get a good grounding in those topics.

Check out the tutorials in the Access help on RI and Keys. A good book would, I think, help you a lot.

Basically your RI problems are because tables are related so that fields in one table must take values from fields in another table.

Structured design (and Normalisation to third normal form if possible) is highly desirable. Do a search on here (and the rest of the Web) for Normalisation. Might be useful to you in explaining the concepts.

Cheers,

Paul.

Sorry, if you felt flamed...

[jeffro]

Ok I will look up RI and Keys. You are right I don't know to much about Access and all that it can offer. So I will look into getting a good book.

Thanks aja

[monte96]

The solution to your problem here, is to not use multiple fields for a primary key or, (gasp) to use a varchar based field for your primary key. The ID field itself is sufficient (especially in Access since you can make it Autonumber).

As for referential Integrity, you will need to modify the foriegn keys in your other tables to use this ID field.

Paul has hit the nail on the head though, just because something was done poorly, don't add to the mess when you have the opportunity to fix it. Poor database design will cause problems down the road in both maintenance and performance.