..a few things - if the data in the string field is all numeric you can change the data type with impunity in Access and it won't even blink.
so I would change the data type to number first in the tmp table. then i would Do a left Join on the two tables. try this for adding new members to old db. 1. qryNewGuys SELECT DISTINCTROW tmpMembers.memNumber, tmpMembers.Region, tmpMembers.Phone FROM tmpMembers LEFT JOIN members ON tmpMembers.memNumber = members.memNumber WHERE ((members.User) Is Null); 2. qryAddNewGuys INSERT INTO members SELECT * FROM qryNewGuys; Then for deleting (adapted from Candaces suggestion) Delete from members Where members.memNumber Not IN (Select memNumber from tmpMembers) Hope this helps. Patrick > -----Original Message----- > From: Stephenie Hamilton [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 16, 2002 2:04 PM > To: CF-Community > Subject: RE: Db merge question > > > That is what I was thinking, however this query: (members > being the old > table, tmpMembers being the new table...members.memNumber is a number > field, tmpMembers,memNumber is a text field) > > <Cfquery name="deleteOld" datasource="#existingDSN#"> > SELECT * FROM members > WHERE NOT EXISTS (SELECT 1 FROM tmpMembers > WHERE members.memNumber = Cast(tmpMembers.memNumber as number)) > </CFQUERY> > > Gives me the following error....am I converting it wrong? > > ODBC Error Code = 37000 (Syntax error or access violation) > > > [Microsoft][ODBC Microsoft Access Driver] Syntax error. in query > expression 'NOT EXISTS (SELECT 1 FROM tmpMembers WHERE > members.memNumber > = Cast(tmpMembers.memNumber as int))'. > > > SQL = "SELECT * FROM members WHERE NOT EXISTS (SELECT 1 FROM > tmpMembers > WHERE members.memNumber = Cast(tmpMembers.memNumber as int))" > > > ~~ > Stephenie > > > > > > |-----Original Message----- > > |From: Toklat [mailto:[EMAIL PROTECTED]] > > |Sent: Wednesday, October 16, 2002 2:05 PM > > |To: CF-Community > > |Subject: Re: Db merge question > > | > > | > > |Then you could loop through the one table, convert the > > |number to a string, compare and then if its there ignore, if > > |its not add it. > > | > > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.401 / Virus Database: 226 - Release Date: 10/9/2002 > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=5 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_community This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
