Use a DECLARE CURSOR to step thru the ID table sorted on the LastName, BirthDate, FirstName. In the WHILE loop, WRITE the important comparative information current variables to the preveous variable row on the monitor, FETCH, compare the preveous to the current row variables. Are the birthdates the same? No, FETCH If yes, is the preveous lastname contained in the current lastname? No, FETCH If yes, then looking at the important compariative information that is written to the current row and preveous row CHOOSE : The preveous is correct; The current is correct; FETCH. INSERT into a table the correct PK into column 1 and the incorrect PK into column 2 . Now column 2 contains all the FK's that need to be changed to the FK's of column 1. Move the the incorrect PK rows from the ID table to the incorrect table. After a complete run thru, repete. UPDATE the incorrect FK's to the correct FK's. Birthdates help a lot to keep duplicates out of the system. RRR
----- Original Message ----- From: "David Ebert" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 05, 2002 12:40 PM Subject: RE: PK/FK cleanup > Yes. > > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:owner-rbase-l@;sonetmail.com] > On Behalf Of Ron Rose > Sent: Tuesday, November 05, 2002 3:26 PM > To: [EMAIL PROTECTED] > Subject: Re: PK/FK cleanup > > > Do you have their birthday in their master ID record? > RRR > > ----- Original Message ----- > From: "David Ebert" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, November 05, 2002 6:08 AM > Subject: PK/FK cleanup > > > > I had some temporary data entry operators inputing to a new database > > with some interesting results. If a client is readmitted they would > > enter a new client record and append either a '2' or a '?' to the last > > > name, i.e., Dave Ebert, Dave Ebert2, Dave Ebert? > > > > The client record is the top level of a pyramid structure. Below the > > client is the admission record (many-to-one), and all records > > pertaining to the admission refer to the admission record's PK. > > > > I want to assign the admission record's PK to the FK records, but > > transfer the admission record to the first client record. No problem > > as I'm only talking about 60 or so records. > > > > However, the client ID is used as a reference in most of the detail > > tables (assessments, services, outcomes, etc.) potentially 18 tables > > in all. Reassigning those client numbers involves finding out if a > > record by a given client number exists, and if so to update it to the > > correct ID. This may need to be done for up to two spurious ID > > numbers. > > > > I can to a series of updates to revise the ID number, but how would I > > do this aside from picking through the candidate tables one at a time? > > > > I'd appreciate any pointers. > > > > tia > > > > Dave Ebert > > Dave Ebert2 > > Dave Ebert? > > Huckleberry House > > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In > the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
