>>FWIW, using a composite index like this is about the best way to ensure a loser decision and put an error in the table's PK by inadequate (and unnecessary) limitation. Make a separate primary key field. If there are in fact duplicated tuples, it's a clear indication that it is not a valid key. Using a factless, arbitrary or surroage primary key protects you - that way if someone ever pulls 101 samples from the custid+upsino+puldat, it won't break or truncate. St best, this is a bad candidate key that is risky because of the magnitude factor in STR(sampleno,2). And since the PK's job is not to sort the file but to uniquely identify a record (which probably can be done in a whole lot fewer cahacters. reducing bloar on child tables) you can make the arbitrary ordering a regular key...
>
>Ed
>
>Thank you for the info on the PK and how I should create a field for use as a PK. However, I am trying to relate some tables with existing data. Parent=Nameplate, Child Tables=Dga, Liquid, and a few others. The application is currently in FP 2.6 and I am converting it to VFP. In the parent table, the Custid+Upsino is what makes the record unique and is what I have to use to relate it to the child tables. In the child tables, the custid+upsino+DTOS(puldate)+STR(sampleno,2) is what makes those unique.
>
>I will be changing the method of the primary key to a "non user" field, however I know there are duplicates in the parent and child tables. I want to "clean those up" before I get started. And if I start using the new "non user" field as a PK before cleaning up and determing which parents are linked to which children, how in the world will I know how to link the parent to the child without using the existing scheme.
>
You're putting the cart before the horse. If we have a parent table NP, and we add a field as a unique code to it called PK_NP, and integer that is the unque id for the record. Each child record, instead of holding CustId and UPSIno, which point at the parent, can hold a pointer to their parent called FK_NP, and each then have their own DG_PK, or LQ_PK, which is an integer that is separate from and independent of the FK_NP value. How does this work?
If I'm on a given NP record, whose NP_PK is 12345, the Children of this record in DGA are the child with a value of FK_NP = 12345. Each one has its own PK, so that any of it's children carry their unique DG_PK. Lets assume that each DG record can have multiple XX records. Each XX record would hold (at least) a FK_DG value to point to their parent PG record. We can get the XX children of a DG record as the XX records with a FK_DG the same as their parent PK_DG. The XX grandchildren of an NP records are all the XX children of DG records such that the DG is a child of NP. The uniqueness of reference is guarenteed because each record can only point to one parent! Now, even accidental duplication of fact-based keys preserve the uniqueness of reference.
Jim explains this a whole lot better than I do. Erik usually does, too, if this seems overly abstract.
>I hope you can understand that rambling. And thanks for the suggestion on the Jim Booth book, I am planning on purchasing it after I finish his AppDev Video Course.