Make Table Query - Need to Convert Data Type from Text to Number

[jmracura]

Hi Everyone,

I have table A with a column called ref_id which is a text field. I want to link this to table B that has something called a dep_id that is a number field. I am using a make table query to query Table A and make a temp table where ref_id will be a number.
I have tried using the Val ([ref_id]) expression in the "update to" row. The query runs, but the data type still shows as "text". Does anyone know what I may be doing wrong? Been struggling with this for about a week now.



Juan

[June7]

Why do you need to make a table? Why not just use the query and join to table B?

Why don't you change the data type of field in table A?

[jmracura]

Thanks for the quick reply. Table A is read only so I can't change the data type there. I can't link to Table B because i need to link the ref_id (text) to the dep_id (number). Table B is also read only, hence why I was trying to convert one of the data types in a temporary table using the make table query function.

Once I create the temp table, I can go in and manually change the data type. But I'll be setting this up to run as part of a macro and I don't know how to manually convert the data type in the temp table as part of my macro.

[June7]

Incorporating process that routinely modifies db design is usually bad idea.

Instead of make table, just manually create the 'temp' table with desired properties then run INSERT SELECT sql to append records. Then you can either routinely append and/or update records when necessary or purge the table and re-append all records.

[jmracura]

Incorporating process that routinely modifies db design is usually bad idea.

Instead of make table, just manually create the 'temp' table with desired properties then run INSERT SELECT sql to append records. Then you can either routinely append and/or update records when necessary or purge the table and re-append all records.

Thanks June7. That totally worked. I don't know why I keep thinking I had to do it within the initial make table query, since I'm going to regularly update this table with new data. So long as I just do the insert into function (using the update table feature) in Access, i should be ok! I wish I had checked this site sooner... Google wasn't much help this time around.