Bob Good point but I do not have a Unique column so that is not an option. I did a select all from the Hist by date and it looks like all the rows are there so I am not sure why the total did not match.
What error would an Insert statement give if a row was not inserted to the Hist table? Thanks Marc From: [email protected] Sent: Thursday, February 11, 2010 11:27 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: compare rows in 2 tables I did not really study your statement below, but would have this input.... Normally a good structure setup has a column that is a unique record indentifier. Such as Order Number, PO Number or even a autonum type that might be called RecordNo. If you have that, then finding the "missing data" is easy... Edit ... from tran_Daily where OrderNo* not in + (Select OrderNo from tran_hist) *Orderno is your unique record identifier Not knowing what all your columns are (such as chknum), it would be very difficult to help decipher the issue. However, having many columns to compare, it would take only one for the data to be different and not return any values. If you have a unique column, simply use it. If not and it is not a major implementation, I would add one. I can even be a "behind the scenes column" that is not included in the forms and the user never see it. -Bob ----- Original Message ----- From: "MDRD" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Thursday, February 11, 2010 10:51:30 AM GMT -06:00 US/Canada Central Subject: [RBASE-L] - compare rows in 2 tables Hi I am trying to find missing rows of data in a Hist table that is not in our Daily table. We enter rows in the Daily table, then insert them into the Hist when we close the day. I count the # of rows in the Daily table and Hist table and after the Insert we look to see if the Hist table grows by that many rows. For this one user it appears that some of the rows are not making it to the Hist table but trying to search through 200+ rows to compare the 2 tables makes you head spin. This is what I am using but it finds no rows, yet my calculations show we are missing some rows. edit all from tran_Daily + WHERE ( CTXT(custnum) + CTXT(date_con) + + CTXT(tr_date) + ch_code + CTXT(ch_price) + + CTXT(ptest) + cknum + CTXT(date_frm) + + CTXT(date_to) + treat_dr + inshold ) NOT IN + (SELECT ( CTXT(custnum) + CTXT(date_con) + + CTXT(tr_date) + ch_code + CTXT(ch_price) + + CTXT(ptest) + cknum + CTXT(date_frm) + + CTXT(date_to) + treat_dr + inshold) FROM tran_hist) Thanks for any suggestions Marc
