Jim, Thanks for the suggestion but that resulting view would compare each row in the colorformulas table. I need to compare a group of rows in that table. I think declaring a cursor that creates a loooong text value might be my only answer. (inkloading + (row 1 partnum + (ctxt(partratio)) + (ctxt(oilratio)) + (row 2 partnum + (ctxt(partratio)) ......etc.... And inserting that into a view, then checking the rows in the view for duplicates as you suggest. Dawn
-----Original Message----- From: Jim Limburg [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 4:06 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Checking for duplicate - multiple rows I have this code for checking duplicates. I believe it was from Bill Downall a good while back.. *(This code is made to check for duplicate values in a table. It will prompt you for the table and column name. The column name should be a column in the table you are refering to that is unique. (the one column that should no have duplicate values -- primary key or ???). ONE MAJOR NOTE:::: This will only reflect accurate results if all indexes have been removed. ) CLS LABEL checkanother SET VAR vm_tblname TEXT = NULL SET VAR vm_colname TEXT = NULL DIALOG 'Enter the Table name (F3 for a list) ' vm_tblname vm_endk 1 AT 13 15 DIALOG 'Enter the Column name (F3 for a list) ' vm_colname vm_endk 1 AT 13 15 SET VAR vm_pausemsg1 TEXT = ('Checking ' + .vm_tblname) PAUSE FOR 1 USING .vm_pausemsg1 AT CENTER CENTER BROWSE ALL FROM &vm_tblname WHERE &vm_colname IN (SELECT &vm_colname + FROM &vm_tblname GROUP BY &vm_colname HAVING COUNT (*)>1) SELECT COUNT(&vm_colname) INTO vbadcount IND vi1 FROM &vm_tblname + WHERE &vm_colname IN (SELECT &vm_colname FROM &vm_tblname + GROUP BY &vm_colname HAVING COUNT (*)>1) CLS WRITE ' ' WRITE ' ' SET VAR vmsg1 TEXT = ('Duplicates in ' + .vm_colname + ' = ') SET VAR vmsg2 TEXT = (CTXT(.vbadcount)) SET VAR vmsg TEXT = (.vmsg1 & .vmsg2) SHOW VAR vmsg CLEAR ALL VAR CLS SET VAR vmsg3 TEXT = 'Do you wish to check another table? ' DIALOG .vmsg3 vresp vkey YES AT 7 IF vresp = 'YES' THEN GOTO checkanother ENDIF CLS CLEAR ALL VAR RETURN Create a view in this manner... CREATE VIEW chkdupes (uniqformula) + AS SELECT ( (CTXT(ColorNum)) + (CTXT(Partnum)) + (CTXT(Partratio)) + + (CTXT(Oilratio)) ) FROM ColorFormulas Then check the view with the code above or a rendition of it. Mind you, none of this is tested. Just a thought line.. and this is where I usually get into trouble.. ha,ha.. Jim At 03:39 PM 8/10/2004, you wrote: >I have two tables which together, comprise formulas for making ink. I >would like to set up an eep to run when a new color gets assigned to be >sure the formula isn't already assigned another number. Here's an >example of the data. > >Table: ColorNumbers >ColorNum Inkloading >CY001CL 1.5 > >Table: ColorFormulas >ColorNum Partnum Partratio Oilratio >CY001CL 1212XY 10.5 NULL >CY001CL 775258 2.5 NULL >CY001CL 80888 NULL 80.00 >CY001CL 80253 NULL 20.00 > >The combination of all the rows in ColorFormulas along with the >InkLoading in ColorNumbers makes the formula unique. I can declare a >cursor, convert the numbers to text and do a concatenation to get a >text value representing the entire formula, but was wondering if there >was an easier way. >Thanks >Dawn
