Append Query into lookup fields - HELP!!

[TobyAlcock]

I am trying to write an append query to convert data from an old database to a newly constructed one.
I have built the required table with underlying lookup tables for a few fields, and I am trying to import the data, using the fields' viewable column as set to the data from the lookup column to allow me to paste the text directly into the table.
The field I want is a text field, but the lookup field is obviously a number.
Is the easiest way to make the append query make a temporary table in the new database, then run another query from the new database to convert this and append to the required table?
Any help on this topic would be appreciated

 

[Amiel]

If you need to convert the text (String data type) to a numeric data type use the Cint() function.
You query will end up looking like this:


"Insert into DestinationTable (field1, field2, field3)
Select [red]CInt([/red]Fld1[red])[/red], Fld2, Fld3 from SourceTable"

the value of [fld1] in the source table will be converted to an integer and be stored in [field1] in the destination.

Hope this helps.


Amiel
amielzz@netscape.net

 

[TobyAlcock]

Amiel,
Thankyou for the reply but I am actually looking for a way to change the field to it's corresponding ID in the lookup table. Does that make sense? I have a new database with the text fields in a new table with ID's, and I need to link them in a new table by using these ID's rather than the text field. Now I am getting confused....................
Toby