Well I have converted the new db memNumber field to a number instead of text...they will just have to change it on their end. But now when I import the rows from the new db into a tmpMember field in the old db... The memNumber is coming out like this: 5102.0 5593.0 5594.0 5595.0
I can't figure out how to get rid of the decimal place as that is throwing off my insert statement with a data type mismatch...arg! Here is the query snippet for creating the tmpMembers table for that field MemNumber NUMBER NULL Is there any way in this create table statement to specify 0 for decimal places? <hating access> ~~ Stephenie > |-----Original Message----- > |From: Harkins,Patrick [mailto:[EMAIL PROTECTED]] > |Sent: Wednesday, October 16, 2002 3:59 PM > |To: CF-Community > |Subject: RE: Db merge question > | > | > |..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 Get the mailserver that powers this list at http://www.coolfusion.com
